Filter pivot table using VBA

gsimons85

New Member
Joined
Apr 1, 2014
Messages
32
Hello everyone

I got some help on this site last week on this issue and it worked at the time, but now my macro keeps crashing.:confused:

I'm trying to filter a pivot table using a cell reference (cell CL1) but I keep getting an error message. The code I have is as follows:

Dim Week As String

Week = Range("cl1").Value


'Dim PvtTbl As PivotTable
'Set PvtTbl = Worksheets("Pivot").PivotTables("PivotTable2")

ActiveSheet.PivotTables("PivotTable2").PivotFields("Week").CurrentPage = Week


PvtTbl.PivotFields("Week").PivotFilters.Add Type:=xlCaptionContains, Value1:=Week


If someone could be so kind as to point me in the right direction, that'd be much appreciated.

Thanks!
Greg
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
What's in CL1 when you get the error? What error are you getting an on what line of code? I notice that you have commented out the line that assigns your pivot table to the variable PvtTbl.
 
Upvote 0

Forum statistics

Threads
1,223,282
Messages
6,171,174
Members
452,387
Latest member
Lorenzo_Barry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top