Merge only specific tabs/sheets in excel-CodePudding

I currently have a macro that merges all the tabs except for a tab named "Combined". This combined sheet will be the destination of the merging of all tabs/sheets. However, I want to exclude some of the tabs from being combined.

For example, I have 5 tabs: Combined, tab1, tabA, tab2, tabB. All tabs have similar number of columns/column headers. When I run the macro it will merge tab1, tabA, tab2, and tabB into Combined. What I want to revise with my current macro is to only combine tab1 and tabA. How will I need to change my current macro?

Here is my current macro:

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

CodePudding user response：

Define an array with the sheet names and then iterate this array

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: Avoid implicit referencing of sheets and ranges - always use explicit referencing, like thisworkbook.worksheets or wksSrc.range(...)

BTW 2: You don't need copy/paste - just write the values to the target:

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

CodePudding user response：

@Ike offered a great answer. Theirs and mine are two variations on the same idea: Create a list of sheets to work on, and then loop through that instead of through all sheets in the workbook.

I also cleaned up some other parts to better implement this new idea. Here is the revised code:

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