I’m trying to create a pivot table based on filtered data.

This code work but selects all data:

Set dSheet = Worksheets("data")
Set pSheet = Worksheets("pivot)

'Find the last row used and the last column used
LR = dSheet.Cells(Rows.Count, 1).End(xlUp).Row
LC = dSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Set the pivot table data range
Set pRange = dSheet.Cells(1, 1).Resize(LR, LC)

'Create pivot cache
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=pRange)

'Create pivot table
Set pTable = pCache.CreatePivotTable(TableDestination:=pSheet.Cells(2, 1), TableName:="PivotTotals")

...

The code below fails:

Set dSheet = Worksheets("data")
Set pSheet = Worksheets("pivot)

'Set the pivot table data range
Set pRange = dSheet.UsedRange.SpecialCells(xlCellTypeVisible)

'Create pivot cache
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=pRange)

'Create pivot table
Set pTable = pCache.CreatePivotTable(TableDestination:=pSheet.Cells(2, 1), TableName:="PivotTotals")

...

Error on the Set pTable statement: “The reference isn’t valid”

Any advice?

I’m now thinking of creating a seperate tab where I first paste the filtered data and then create a pivot on that tab.