仅合并excel中的特定选项卡/工作表

Question

我目前有一个宏，可以合并所有的选项卡，除了一个名为"组合"的选项卡。合并后的工作表将成为合并所有选项卡/工作表的目标。但是，我想从组合中排除一些制表符。

例如，我有5个标签:组合，tab1, tabA, tab2, tabB。所有选项卡都有相似数量的列/列标头。当我运行宏时，它将合并tab1, tabA, tab2和tabB到Combined。我想修改我当前的宏是只组合tab1和tabA。我需要如何更改当前的宏?

这是我当前的宏:

Option Explicit
Public Sub CombineDataFromAllSheets()
Sheets("Combined").Rows("2:" & Sheets("Combined").Rows.Count).ClearContents
Dim wksSrc As Worksheet, wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long

'Notes: "Src" is short for "Source", "Dst" is short for "Destination"

'Set references up-front
Set wksDst = ThisWorkbook.Worksheets("Combined")
lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below (and in Toolbelt)!
lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below (and in Toolbelt)!

'Set the initial destination range
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

'Loop through all sheets
For Each wksSrc In ThisWorkbook.Worksheets

'Make sure we skip the "Import" destination sheet!
If wksSrc.Name <> "Combined" Then

'Identify the last occupied row on this sheet
lngSrcLastRow = LastOccupiedRowNum(wksSrc)

'Store the source data then copy it to the destination range
With wksSrc
Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
rngSrc.Copy Destination:=rngDst
End With

'Redefine the destination range now that new data has been added
lngDstLastRow = LastOccupiedRowNum(wksDst)
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

End If

Next wksSrc
End Sub

Answer 1

用表单名定义一个数组，然后迭代这个数组

Dim arrSheets(1) as String
arrSheets(0) = "tab1"
arrSheets(1) = "tabA"
Dim i as long
For i = 0 to Ubound(arrSheets)
Set wksSrc = Thisworkbook.worksheets(arrSheets(i))
....
next

BTW 1:避免隐式引用表和范围-始终使用显式引用，如thisworkbook.worksheets或wksSrc.range(...)

BTW 2:您不需要复制/粘贴-只需将值写入目标:

With rngSrc 
rngDst.Resize(.Rows.Count, .Columns.Count).Value = .Value 'this is much faster and you don't need the clipboard
End With

Answer 2

@Ike给出了一个很好的答案。他们的和我的是同一思想的两种变体:创建要处理的工作表列表，然后循环遍历该列表，而不是遍历工作簿中的所有工作表。

我还清理了一些其他部分，以更好地实现这个新想法。下面是修改后的代码:

Option Explicit
Public Sub CombineDataFromAllSheets()
Dim wksSrc As Variant, wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
Dim SourceSheets As Variant
'Notes: "Src" is short for "Source", "Dst" is short for "Destination"

'Set references up-front
Set wksDst = ThisWorkbook.Worksheets("Combined")
lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below (and in Toolbelt)!
lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below (and in Toolbelt)!

'List of sheets to operate on
SourceSheets = Array( _
ThisWorkbook.Worksheets("tab1"), _
ThisWorkbook.Worksheets("tabA") _
)

wksDst.Rows("2:" & Sheets("Combined").Rows.Count).ClearContents

'Set the initial destination range
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

'Loop through all sheets
For Each wksSrc In SourceSheets

'Identify the last occupied row on this sheet
lngSrcLastRow = LastOccupiedRowNum(wksSrc)

'Store the source data then copy it to the destination range
With wksSrc
Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
rngSrc.Copy Destination:=rngDst
End With

'Redefine the destination range now that new data has been added
lngDstLastRow = LastOccupiedRowNum(wksDst)
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)


Next wksSrc
End Sub