|
描述:一个文件中有N个表,我想把每个表格中的内容合并到一个表格中,但每个表格遇到空格就不采集,从第二个表开始采集,同样遇到空格就采集一个表的内容。详情见附件
就跟你改了下,其他的没动。 - Sub 合并()
- Dim x As Integer
- Dim y As Integer '明细表的最后一行
- Dim z As Integer
- Dim zz As Integer '第几行开始复制
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- zz = Application.InputBox("不含标题行,从明细表第几行取数", "复制数据", 4)
- z = zz
- '复制标题
- If zz > 1 Then
- If Sheets(1).Name <> "合并" Then
- Sheets(1).Rows(1 & ":" & zz - 1).Copy Range("A1") '
- Else
- Sheets(2).Rows(1 & ":" & zz - 1).Copy Range("A1") '
- End If
- End If
- Range(z & ":65536").Clear
- For x = 1 To Worksheets.Count
- If Sheets(x).Name <> "合并" Then
- z = [b65536].End(xlUp).Row
- y = Sheets(x).[a1].End(xlDown).Row
- If y < zz Then GoTo 100 '为空表就不复制
- Sheets(x).Rows(zz & ":" & y).Copy Range("A" & z + 1) 'zz明细表第几行取数
- 100:
- End If
- Next x
- '加明细表名称
- Dim ss As Integer
- ss = Cells(zz - 1, 256).End(xlToLeft).Column + 1 '增加一列,显示明细表名称
- Cells(zz - 1, ss) = "明细表"
- For x = 1 To Worksheets.Count
- If Sheets(x).Name <> "合并" Then
- z = Cells(65536, ss).End(xlUp).Row
- y = Sheets(x).[a1].End(xlDown).Row
- If y < zz Then GoTo 200 '为空表就不复制
- Range(Cells(z + 1, ss), Cells(z + 1 + y - zz, ss)) = Sheets(x).Name '加明细表名称
- 200:
- End If
- Next x
- With Sheets("合并")
- ROW1 = .Range("B65536").End(xlUp).Row
- For K = ROW1 To zz + 1 Step -1
- If .Cells(K, 2) = "" Then Cells(K, 2).EntireRow.Delete
- Next K
- .Cells(1, 2).Select
- End With
- MsgBox "合并成功!!", , "提示您"
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|