How can one Excel-sheet show the filtered values from another sheet?-CodePudding

I have an Excel file with many columns in Sheet A, and where the top row has filters for each column. How can I show the filtered values from SheetA in SheetB, where SheetB has no filter in the top row?

Lets say SheetA has 100 rows, and after I have used a filter in one or more of its columns, I can see 15 filtered rows under the sheets header row. I want these 15 rows to show up as the uppermost 15 rows in SheetB; without SheetB having any filter row at the top. How can I do this?

I want SheetB to show the result of the filtering in SheetA..

CodePudding user response：

You cannot do what you're asking without VBA as there is no formula to determine if a row is hidden/filtered. If you could identify such a rule (i.e. over a certain amount or something), then you could use the Filter Function.

If VBA is acceptable, you could put this procedure into sheet B module (see below where this is).

Private Sub Worksheet_Activate()
    Me.UsedRange.ClearContents

    Sheets("Sheet A").UsedRange.SpecialCells(xlCellTypeVisible).Copy
    
    Me.Range("A1").PasteSpecial (xlPasteValues)
End Sub