PDA

View Full Version : Solved: Need macro for this pivot functionality?



SilverSN95
01-21-2010, 12:11 PM
I am working with a large table of data that I need to pivot over to get certain counts/totals etc. Basically I need the pivot table to be dynamic... I want to have one pivot table on an isolated WS that would feed other reports in different WS's.
The problem I am having is that I don't see a way to change what the pivot table is pivoting over in the "page region" without actually changing it on the pivot table, I can't insert formulas into these regions. Ideally I would like this to change based either on a value somewhere else in the WB, or based on the name of the active WS so the various reports are showing relevant data pulled from the pivot table.
Is this something that can be done programatically with VBA? I admit I am inexperieced with using pivot tables so there might be a better way to do this.

Thanks for the help.

Bob Phillips
01-21-2010, 02:29 PM
Try



With ActiveSheet

.PivotTables("myPivot").PageFields("Region").CurrentPage = .Range("D1").Value
End With

SilverSN95
01-26-2010, 01:02 PM
Thanks xld, this is what I needed. Sorry I didn't get back to you until just now, but this will help me out a lot.
Marking as solved.