请尝试下一个代码。它将在所有现有图纸之间迭代,并从一个名为“目的地”(从“A1”开始)的所有图纸中复制列“D:K”的所有行。如果需要从“D1”开始,则可以很容易地修改代码:
Sub copyAllSheetsInOne()
Dim ws As Worksheet, sh As Worksheet, lastRow As Long, lastEmptyCol As Long, i As Long
Set sh = Worksheets("Destination") 'a sheet named "Destination" must exist in the workbook to be processed
sh.cells.ClearContents 'clear its content (for cases when code run before)
'some optimization to make the code faster:
Application.DisplayAlerts = False: Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'iterate between all existing sheets:
For Each ws In ActiveWorkbook.Worksheets
If ws.name <> "Destination" Then
lastEmptyCol = sh.cells(1, sh.Columns.count).End(xlToLeft).Column + 1
lastRow = ws.Range("D" & ws.rows.count).End(xlUp).row
If lastEmptyCol = 2 Then lastEmptyCol = 1 'for the first sheet
ws.Range("D1", ws.Range("K" & lastRow)).Copy sh.cells(1, lastEmptyCol)
End If
Next ws
Application.DisplayAlerts = True: Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub