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

Question

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..

copy paste the data? Not sure exactly what you are asking here. Please [edit] the post to include a [mcve] — Scott Craner, Oct 31 '22 at 17:46
You cannot tie the two filters together. They are two different objects. Even if the columns are the same and the data is the same. — Scott Craner, Oct 31 '22 at 17:51
There are not two filters, only filters in SheetA, not in SheetB — myotis, Oct 31 '22 at 19:18

score 0 · Accepted Answer · answered Oct 31 '22 at 19:49

0

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

answered Oct 31 '22 at 19:49

pgSystemTester

8,979
2
23
49

Thanks a lot, it works. How can I copy only selected colums from SheetA, and rename their headings before pasted into SheetB? Thank in advance! – myotis Oct 31 '22 at 21:56
@myotis if this is your first VBA subroutine, that's great! However, you'll probably have dozens of questions if you keep working on it and I'd be doing you a disservice to just give you answers in chat. A good starter tool is using the macro recorder. This allows you execute the steps you want and see the appropriate vba code. Play with that a bit, google your questions and if you get stuck again, post another question. Thanks for accepting answer. – pgSystemTester Nov 01 '22 at 16:46
The one problem with the macro recorder is that it uses `select` and `activecell` frequently which you almost never want in your code. See [this famous question on how to avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Good luck. – pgSystemTester Nov 01 '22 at 16:47

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

1 Answers1

Linked