vba 使用宏将 3 个以上的 Excel 工作表合并到一个工作簿中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19182360/
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
Merge more than 3 Excel worksheets into one workbook with macro
提问by user2842844
I'm trying to merge worksheets together into one workbook. I have a work book that has over 20 sheets. I was looking to create a macro that would merge sheets together (depending on their name) into a separate workbook.
我正在尝试将工作表合并到一个工作簿中。我有一本超过 20 页的工作簿。我正在寻找创建一个宏，将工作表合并在一起（取决于它们的名称）到一个单独的工作簿中。
So far this is the code that I have got: it mergers all the sheets in the workbook together but I would like to merge them by name.
到目前为止，这是我得到的代码：它将工作簿中的所有工作表合并在一起，但我想按名称合并它们。
Sub mergedata()
Sheets(1).Activate
lastrow = ActiveSheet.UsedRange.Rows.Count
For Each Sheet In Sheets
If Sheet.Index <> 1 Then
RowCount = Sheet.UsedRange.Rows.Count
Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)
lastrow = lastrow + RowCount
Sheet.UsedRange.Clear
End If
Next Sheet
End Sub
采纳答案by Kazimierz Jawor
You need to add one additional if statements in your loop. This could be like this:
您需要在循环中添加一个额外的 if 语句。这可能是这样的：
'.....
For Each Sheet In Sheets
If Sheet.Index <> 1 Then
If Sheet.Name = "NameOfSheet" or Sheet.Name = "NameIsCaseSensitive" then
RowCount = Sheet.UsedRange.Rows.Count
Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)
lastrow = lastrow + RowCount
Sheet.UsedRange.Clear
end if
End If
Next Sheet
'....
回答by Mr Gray
I case you need the same but horizontaly (like I did) (w/o Names upgrade)
我的情况你需要相同但水平的（就像我一样）（没有名称升级）
Sub mergedata_horizontal()
Sheets(1).Activate
lastcol = ActiveSheet.UsedRange.Columns.Count
For Each Sheet In Sheets
If Sheet.Index <> 1 Then
ColCount = Sheet.UsedRange.Columns.Count
Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(1, lastcol + 1)
lastcol = lastcol + ColCount
Sheet.UsedRange.Clear
End If
Next Sheet
End Sub
