vba 将多行从多张纸复制到一张纸
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10887325/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy multiple rows from many sheets to one sheet
提问by user1435900
I receive a workbook daily that lists 50 rows of information per page on a variable number of pages depending on how many rows there are total.
我每天都会收到一本工作簿，其中每页列出 50 行信息，页数可变，具体取决于总共有多少行。
How can I copy the 50 rows from each page onto one master list?
如何将每页的 50 行复制到一个主列表中？
From recording a macro I get
从录制宏我得到
Sub Macro2()
Sheets("Page1_2").Select
Rows("5:54").Select
Selection.Copy
Sheets("Page1_1").Select
Range("A56").Select
ActiveSheet.Paste
End Sub
But I need it to loop through the entire workbook. I can't find a way to increment the sheet selection by 1 for each iteration and the paste range by 50.
但我需要它来遍历整个工作簿。我找不到将每次迭代的工作表选择增加 1 并将粘贴范围增加 50 的方法。
Any help?
有什么帮助吗？
回答by Mark M
How about:
怎么样：
Sub test()
Dim curRow As Integer
Dim activeWorksheet As Worksheet
Set activeWorksheet = ActiveSheet
curRow = 1
For Each ws In ActiveWorkbook.Worksheets
If Not ws.Name = activeWorksheet.Name Then
ws.Range("5:54").Copy Destination:=activeWorksheet.Range(CStr(curRow) & ":" & CStr(curRow + 49))
curRow = curRow + 50
End If
Next ws
End Sub
It loops over all worksheets in the workbook and copies the contents to the current active sheet. The looping excludes the current active worksheet. It assumes that the contents that you are trying to aggregate are always in rows 5 through 54.
它遍历工作簿中的所有工作表并将内容复制到当前活动工作表。循环排除当前活动的工作表。它假定您尝试聚合的内容始终位于第 5 行到第 54 行中。
