vba 将所有工作表复制到一张纸中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18854432/
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 all worksheets into one sheet
提问by Peck3277
I am trying to write an excel macro that will copy all my worksheets into one single worksheet.
我正在尝试编写一个 excel 宏，它将我的所有工作表复制到一个工作表中。
All worksheets are layed out the same, 4 columns with data in every cell of every row. Each sheet has a header. I am trying to copy the prefiltered data from each sheet to a results sheet, the data from each sheet will be stacked on top of each other.
所有工作表的布局相同，4 列，每行的每个单元格中都有数据。每张纸都有一个标题。我试图将预过滤的数据从每张表复制到结果表，每张表中的数据将堆叠在一起。
So far this is what I have and it's almost working.
到目前为止，这就是我所拥有的，并且几乎可以正常工作。
Dim sh As Worksheet
Dim iRows As Long
iRows = 0
For Each sh In ActiveWorkbook.Worksheets
sh.Select
Range("A1").Select
Selection.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets("Results").Select
Range("A1").Select
Selection.Offset(iRows, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
iRows = Worksheets("Results").UsedRange.Rows.Count
Next sh
My offset is incorrect, when I copy over the next sheet I copy over the data it copies over the previous row.
我的偏移量不正确，当我复制下一张纸时，我复制了它复制到前一行的数据。
If anyone can help out that would be great, if you could also explain what I am doing wrong here as well that would be great as I'm new to excel and VBA. I'm guess that I don't understand how the paste works correctly?
如果任何人都可以提供帮助，那就太好了，如果您也可以解释我在这里做错了什么，那就太好了，因为我是 excel 和 VBA 的新手。我想我不明白粘贴是如何正确工作的？
回答by tigeravatar
Sub tgr()
Dim ws As Worksheet
Dim wsDest As Worksheet
Set wsDest = Sheets("Results")
For Each ws In ActiveWorkbook.Sheets
If ws.Name <> wsDest.Name Then
ws.Range("A2", ws.Range("A2").End(xlToRight).End(xlDown)).Copy
wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next ws
End Sub
回答by Peck3277
I managed to figure it out. I'm not sure if my code is ideal but what I needed now works.
我设法弄明白了。我不确定我的代码是否理想，但我现在需要的东西可以工作。
I had two blank worksheets in my work book. One was called template and one results. What I have done is added and if loop to ignore those two pages. It seems that because those two blank sheets existed I was adding in extra spaces.
我的工作簿中有两张空白工作表。一种称为模板，一种称为结果。我所做的是添加和 if 循环忽略这两个页面。似乎因为存在那两张空白纸，所以我添加了额外的空格。
