修改VBA语句 关于合并工作表的列数的变动

孙灯亮

请高手指点：
附件中，文件夹中存有1000个以上的工作簿，文件格式相同，需要汇总到一个工作表中（BOM整理中），我在VBA语句的书中，复制了一个语句，比较有效果，但是不能复制到10列，仅仅是7列，个人修改过（红色标注的地方），仍不行，拜托了！！
Sub HzWb()
   Dim bt As Range, r As Long, c As Long
   r = 1
   c = 7  （这个地方修改为10，但是结果仍然是7列）
   Dim wt As Worksheet
   Set wt = ThisWorkbook.Worksheets(1)
   wt.Rows(r + 1 & ":1048576").ClearContents
   Application.ScreenUpdating = False
   Dim FileName As String, sht As Worksheet, wb As Workbook
   Dim Erow As Long, fn As String, arr As Variant
   FileName = Dir(ThisWorkbook.Path & "\*.xlsx")
   Do While FileName <> ""
       If FileName <> ThisWorkbook.Name Then
           Erow = wt.Range("A1").CurrentRegion.Rows.Count + 1
           fn = ThisWorkbook.Path & "\" & FileName
           Set wb = GetObject(fn)
           Set sht = wb.Worksheets(1)
           arr = sht.Range(sht.Cells(r + 1, "A"), sht.Cells(1048576, "B").End(xlUp).Offset(0, 5))
           wt.Cells(Erow, "A").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
           wb.Close False
       End If
       FileName = Dir
   Loop
   Application.ScreenUpdating = True
End Sub

microyip

这个代码跟C没有关系的呀，跟数据文件才有关系

lsc900707

Sub HzWb()
   Dim bt As Range, r As Long
   r = 1
   Dim wt As Worksheet
   Set wt = ThisWorkbook.Worksheets(1)
   wt.Rows(r + 1 & ":1048576").ClearContents
   Application.ScreenUpdating = False
   Dim FileName As String, sht As Worksheet, wb As Workbook
   Dim Erow As Long, fn As String, arr As Variant
   FileName = Dir(ThisWorkbook.Path & "\*.xlsx")
   Do While FileName <> ""
       If FileName <> ThisWorkbook.Name Then
           Erow = wt.Range("A1").CurrentRegion.Rows.Count + 1
           fn = ThisWorkbook.Path & "\" & FileName
           Set wb = GetObject(fn)
           Set sht = wb.Worksheets(1)
           arr = sht.Range(sht.Cells(r + 1, "A"), sht.Cells(1048576, "J").End(xlUp).Offset(0, 1))
           wt.Cells(Erow, "A").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
           wb.Close False
       End If
       FileName = Dir
   Loop
   Application.ScreenUpdating = True
End Sub

孙灯亮

多谢！这个效果已经达到预期