Pivot table based on named (non-contiguous) range

Hello All,

have Sheet("List") with an autofilter and sheet("Table") with pivot table. How do I base the pivottable on the filtered (visible) items from "List"?

I use this code so far but:

Please Login or Register  to view this content.

Code breaks on the pivot table is refreshed with "Reference not Valid"

so I check the name, and the Refers to field is filled up with this

Please Login or Register  to view this content.

How do I construct a proper range reference based on the visual cells?

You need a contiguous range as source.

Can you not replicate the visibility via a Page Field ?
eg store a SUBTOTAL function on each row on source sheet designed to generate 1/0 pending row visibility - use this new column as a Page Field on your Pivot set to 1 thereby excluding all hidden rows from subsequent analysis.

If not you will need to take the visible range and move to a contiguous range elsewhere before setting as source for PT.