|
题目
打开本路径下的A.Xls文件,并把文件中的所有工作表的明细数据合并到本表中,上下排列
注: A.xls文件中工作表数量和明细表行数和列数均不定.但各个工作表中的行列数量相同
这是我自己写写的程序,运行时提示“下标越界”请大家帮忙(B是目标文件)
Sub second()
Dim num As Integer, x As Integer, y As Integer
Dim sh_u As Worksheet, sh As Worksheet
Dim rg As Range, rg1 As Range, rg2 As Range
num = Workbooks("A.xls").Sheets.Count
For x = 1 To num
Set sh_u = Workbooks("A.xls").Sheets(x)
Set rg2 = Workbooks("A.xlsx").Sheets(x).Range("A65536").End(xlUp) //运行时提示“下标越界”
If x = 1 Then
Set rg = Workbooks("B.xlsx").Sheets(1).Range("A1")
sh_u.Range("A1").CurrentRegion.Copy rg
Else
Set rg1 = Workbooks("B.xlsx").Sheets(1).Range("A65536").End(xlUp).Offset(1, 0)
Intersect(sh_u.Columns("A:D"), Range("B1:rg2").EntireRow).Copy rg1
End If
Next x
End Sub
附件:
A文件的表1
销售顾问 | 销售数量 | 销售单价 | 销售金额 | A | 2 | 6 | 12 | B | 3 | 8 | 24 | C | 4 | 7 | 28 | D | 56 | 4 | 224 | E | 3 | 8 | 24 | R | 3 | 5 | 15 | A文件的表2
销售顾问 | 销售数量 | 销售单价 | 销售金额 | A | 1 | 6 | 6 | B | 3 | 2 | 6 | C | 4 | 1 | 4 | D | 5 | 2 | 10 |
|
|