[已解决]简化代码

fghji

Sub 总数()
   Application.DisplayAlerts = False
   With Workbooks.Open(ThisWorkbook.Path & "\book1.xls")
   Workbooks("book1.xls").Sheets(6).Range("A3:A95").Copy Workbooks("book10.xls").Sheets(1).Range("I3")
   Workbooks("book1.xls").Sheets(6).Range("B3:B95").Copy Workbooks("book10.xls").Sheets(3).Range("I3")
    .Close True
    End With
   With Workbooks.Open(ThisWorkbook.Path & "\book2.xls")
   Workbooks("book2.xls").Sheets(6).Range("A3:A95").Copy Workbooks("book10.xls").Sheets(1).Range("J3")
   Workbooks("book2.xls").Sheets(6).Range("B3:B95").Copy Workbooks("book10.xls").Sheets(3).Range("J3")
    .Close True
    End With
   With Workbooks.Open(ThisWorkbook.Path & "\book3.xls")
   Workbooks("book3.xls").Sheets(6).Range("A3:A95").Copy Workbooks("book10.xls").Sheets(1).Range("K3")
   Workbooks("book3.xls").Sheets(6).Range("B3:B95").Copy Workbooks("book10.xls").Sheets(3).Range("K3")
    .Close True
    End With
   With Workbooks.Open(ThisWorkbook.Path & "\book4.xls")
   Workbooks("book4.xls").Sheets(6).Range("A3:A95").Copy Workbooks("book10.xls").Sheets(1).Range("L3")
   Workbooks("book4.xls").Sheets(6).Range("B3:B95").Copy Workbooks("book10.xls").Sheets(3).Range("L3")
    .Close True
    End With
   With Workbooks.Open(ThisWorkbook.Path & "\book5.xls")
   Workbooks("book5.xls").Sheets(6).Range("A3:A95").Copy Workbooks("book10.xls").Sheets(1).Range("M3")
   Workbooks("book5.xls").Sheets(6).Range("B3:B95").Copy Workbooks("book10.xls").Sheets(3).Range("M3")
    .Close True
    End With
    Application.DisplayAlerts = True
End Sub

最佳答案

月排行榜 / 总排行榜

hwc2ycy

2013-12-1 18:11

1. Sub 总数()
   
2.     Application.DisplayAlerts = False
   
3.     Dim wb10 As Workbook
   
4.     Set wb10 = Workbooks("book10.xls")
   
5.     Dim i As Byte
   
6.     Application.ScreenUpdating = False
   
7.     For i = 1 To 5
   
8.         With Workbooks.Open(ThisWorkbook.Path & "\book" & i & ".xls")
   
9.             With .Sheets(6)
   
10.                 .Range("A3:A95").Copy wb10.Sheets(1).Cells(3, 8 + i)
    
11.                 .Range("B3:B95").Copy wb10.Sheets(3).cells(3, 8 + i)
    
12.             End With
    
13.             .Close True
    
14. 
    
15.         End With
    
16.     Next
    
17.     Application.DisplayAlerts = True
    
18. End Sub
    

复制代码

跳转到最佳答案楼层

hwc2ycy

多用WITH吧，

上清宫主

没得好大优化空间，无非写成循环什么的

hwc2ycy

1. Sub 总数()
   
2.     Application.DisplayAlerts = False
   
3.     Dim wb10 As Workbook
   
4.     Set wb10 = Workbooks("book10.xls")
   
5.     Dim i As Byte
   
6.     Application.ScreenUpdating = False
   
7.     For i = 1 To 5
   
8.         With Workbooks.Open(ThisWorkbook.Path & "\book" & i & ".xls")
   
9.             With .Sheets(6)
   
10.                 .Range("A3:A95").Copy wb10.Sheets(1).Cells(3, 8 + i)
    
11.                 .Range("B3:B95").Copy wb10.Sheets(3).cells(3, 8 + i)
    
12.             End With
    
13.             .Close True
    
14. 
    
15.         End With
    
16.     Next
    
17.     Application.DisplayAlerts = True
    
18. End Sub
    

复制代码

hwc2ycy

.close true也可以改成.close false
毕竟文件不是写入到了book1-5.xls，不需要保存。