【VBA】多工作簿中某一个同名工作表汇总

hewan16

需求： 文件夹下有多个工作簿，每个工作簿中有8-10个工作表，只想汇总这么多工作簿中某一个相同名字的工作表（比如图中的7部）


方案&问题：在论坛中看到 多工作簿指定“sheet1” 汇总的代码（代码如下），尝试把标黄的部分改成7或者工作表名称后，代码报错下标越界，请帮忙看看是什么问题，代码改怎么修改或者编写能达到想要的结果呢？谢谢~~

1. Sub Summary()
   
2. Dim bt As Range, r As Long, c As Long
   
3. 
   
4. r = 2
   
5. c = 13
   
6. 
   
7. Dim wt As Worksheet
   
8. <font style="background-color: orange;">Set wt = ThisWorkbook.Worksheets(1) </font>
   
9. wt.Rows(r + 1 & ":1048576").ClearContents
   
10. Application.ScreenUpdating = False
    
11. Dim filename As String, sht As Worksheet, wb As Workbook
    
12. Dim erow As Long, fn As String, arr As Variant
    
13. filename = Dir(ThisWorkbook.Path & "\*.xlsx")
    
14. Do While filename <> ""
    
15.    If filename <> ThisWorkbook.Name Then
    
16.      erow = wt.Range("A1").CurrentRegion.Rows.Count + 1
    
17.      fn = ThisWorkbook.Path & "" & filename
    
18.      Set wb = GetObject(fn)
    
19. <font style="background-color: orange;">     Set sht = wb.Worksheet(1)</font>
    
20.      
    
21.      arr = sht.Range(sht.Cells(r + 1, "A"), sht.Cells(1048576, "B").End(xlUp).Offset(0, 5))
    
22.      wt.Cells(erow, "A").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    
23.      wb.Close False
    
24.     End If
    
25.      filename = Dir
    
26. Loop
    
27. Application.ScreenUpdating = True
    
28.    
    
29. 
    
30. End Sub
    

复制代码

蓝桥玄霜

Set sht = wb.Worksheet(1)
改为：
Set sht = wb.Worksheets("7部")

hewan16

谢谢~~明白了，之前陷入误区，以为两个1都要改。