Macro copy that includes hidden/filtered data

Question

I have a workbook with 20+ worksheets all of the same format with the same header in row 1 and 2. I have a macro I cobbled together to pull all the data (except for a few worksheets called out) into one master in the same workbook. Works great, however, various users "own" each tab and when they decide to filter or hide columns in their worksheet and leave them the hidden/filtered data doesn't pull to the master properly.

Any suggestions for how to get all of the data from row 3 down regardless of filters/hidden from each worksheet to the combined worksheet? I'd even survive if it just copied every cell in the worksheet to the master because the next sheet in the loop would past at the last occupied row and I could filter the repeated headers easily enough.

Worst case I could add code to unfilter and unhide everything before running but they want to come back to their tabs with their filters in place for convenience sigh. I never know what they are filtering/hiding for so I'm not sure how I could do that then put it back smartly either.

Many thanks in advance

LastOccupiedRowNum/LastOccupiedColNum functions are defined elsewhere

Public Sub CombineDataFromAllSheets()

Application.ScreenUpdating = False

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"

'Clean old data first
Sheets("Data DO NOT EDIT").Select
Rows("3:2000").Select
Selection.Delete Shift:=xlUp

'Set references
Set wksDst = ThisWorkbook.Worksheets("Data DO NOT EDIT")
lngDstLastRow = LastOccupiedRowNum(wksDst)
lngLastCol = LastOccupiedColNum(wksDst)

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

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

    'Skip these
    If wksSrc.Name <> "Acronyms" And _
        wksSrc.Name <> "Template" And _
        wksSrc.Name <> "Permitter" And _
        wksSrc.Name <> "Plans" And _
        wksSrc.Name <> "Summary DO NOT EDIT" Then
        
        'Identify last occupied row
        lngSrcLastRow = LastOccupiedRowNum(wksSrc)
        
        'Store data then copy it to destination
        With wksSrc
            Set rngSrc = .Range(.Cells(3, 1), .Cells(lngSrcLastRow, lngLastCol))
            rngSrc.Copy Destination:=rngDst
        End With
        
        'Redefine destination range to next empty row
        lngDstLastRow = LastOccupiedRowNum(wksDst)
        Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
        
    End If

Next wksSrc

Application.ScreenUpdating = True

End Sub

Ranga Ranga · Accepted Answer · 2021-04-07T10:09:06

I am assuming that you data is not a table, try adding this in the For loop

wksSrc.Rows.EntireRow.Hidden = False
wksSrc.Columns.EntireColumn.Hidden = False
On Error Resume Next
    wksSrc.ShowAllData
  On Error GoTo 0

Macro copy that includes hidden/filtered data

1 Answers