help - How do you program many pivots to sink to same page field
 

I am building massive pivot reports - many pivots on many sheets of a work
book.

Is there a way to select a page field value in a pivot ( or other fields as
well) and have all other pivots on the sheet, other sheets, and the entire
workbook have there page filed set to this same value.

This would allow me to deploy some fantastic tools to people.

I am speaking of slect the customer(one fo my fields) in a single pivot -
designated as the master pivot if thats easier - then have automated switch
to that cusotmer in all other page fields.

Thanks for the time

help - How do you program many pivots to sink to same page field
 

Hi Todd

This should get you started :

Sub Macro1()
Dim x As String, y As Integer, scnt As Integer
scnt = ActiveWorkbook.Sheets.Count
x = ActiveSheet.PivotTables(1).PivotFields("n1").Curre ntPage.Value
For j = 1 To scnt
Sheets(j).Activate
y = ActiveSheet.PivotTables.Count
For i = 1 To y
ActiveSheet.PivotTables(i).PivotFields("n1").Curre ntPage = x
Next i
Next j
End Sub

This example assumes one pivot table per sheet. Choose a page field on any
table then run the macro and the page field on all the other sheets will be
set to the same value.

You can run this code from a button placed on each sheet. Or fancier, use
the calculate event to call the macro.
Private Sub Worksheet_Calculate()
Call Macro1
End Sub
This could be very slow on a massive number of large pivot though.

Regards,
Mike


"Todd F." wrote:

I am building massive pivot reports - many pivots on many sheets of a work
book.

Is there a way to select a page field value in a pivot ( or other fields as
well) and have all other pivots on the sheet, other sheets, and the entire
workbook have there page filed set to this same value.

This would allow me to deploy some fantastic tools to people.

I am speaking of slect the customer(one fo my fields) in a single pivot -
designated as the master pivot if thats easier - then have automated switch
to that cusotmer in all other page fields.

Thanks for the time

can we do multiple pivots on a single sheet
 

Thanks allot - this is awsome - not totally understanding YET! ut very cool

One question - can it be done when there are multiple pivots on some sheets
and other sheets have only a single pivot.

If we need the single pivot per single sheet ok - I will redo and just have
allot of sheets - not a problem

Two or 3 pivots per page would be a great thing however.

Hey thanks allot for your time on this

"MIKE215" wrote:

Hi Todd

This should get you started :

Sub Macro1()
Dim x As String, y As Integer, scnt As Integer
scnt = ActiveWorkbook.Sheets.Count
x = ActiveSheet.PivotTables(1).PivotFields("n1").Curre ntPage.Value
For j = 1 To scnt
Sheets(j).Activate
y = ActiveSheet.PivotTables.Count
For i = 1 To y
ActiveSheet.PivotTables(i).PivotFields("n1").Curre ntPage = x
Next i
Next j
End Sub

This example assumes one pivot table per sheet. Choose a page field on any
table then run the macro and the page field on all the other sheets will be
set to the same value.

You can run this code from a button placed on each sheet. Or fancier, use
the calculate event to call the macro.
Private Sub Worksheet_Calculate()
Call Macro1
End Sub
This could be very slow on a massive number of large pivot though.

Regards,
Mike


"Todd F." wrote:

I am building massive pivot reports - many pivots on many sheets of a work
book.

Is there a way to select a page field value in a pivot ( or other fields as
well) and have all other pivots on the sheet, other sheets, and the entire
workbook have there page filed set to this same value.

This would allow me to deploy some fantastic tools to people.

I am speaking of slect the customer(one fo my fields) in a single pivot -
designated as the master pivot if thats easier - then have automated switch
to that cusotmer in all other page fields.

Thanks for the time

can we do multiple pivots on a single sheet
 

Todd,

As the example shows now, it will change the page on any number of pivot
tables on a page. The variable y gets a count of the number of pivot tables
and the FOR statement loops through them all and changes the page for each
one.

However, x gets its page value only from the first pivot table on a sheet.
So if the user changes the page in the 5th table and the macro runs nothing
happens. The pages don't change for other tables. If the user changes the
page in table 1 and the macro runs then all the other pages change.

Mike

"Todd F." wrote:

Thanks allot - this is awsome - not totally understanding YET! ut very cool

One question - can it be done when there are multiple pivots on some sheets
and other sheets have only a single pivot.

If we need the single pivot per single sheet ok - I will redo and just have
allot of sheets - not a problem

Two or 3 pivots per page would be a great thing however.

Hey thanks allot for your time on this

"MIKE215" wrote:

Hi Todd

This should get you started :

Sub Macro1()
Dim x As String, y As Integer, scnt As Integer
scnt = ActiveWorkbook.Sheets.Count
x = ActiveSheet.PivotTables(1).PivotFields("n1").Curre ntPage.Value
For j = 1 To scnt
Sheets(j).Activate
y = ActiveSheet.PivotTables.Count
For i = 1 To y
ActiveSheet.PivotTables(i).PivotFields("n1").Curre ntPage = x
Next i
Next j
End Sub

This example assumes one pivot table per sheet. Choose a page field on any
table then run the macro and the page field on all the other sheets will be
set to the same value.

You can run this code from a button placed on each sheet. Or fancier, use
the calculate event to call the macro.
Private Sub Worksheet_Calculate()
Call Macro1
End Sub
This could be very slow on a massive number of large pivot though.

Regards,
Mike


"Todd F." wrote:

I am building massive pivot reports - many pivots on many sheets of a work
book.

Is there a way to select a page field value in a pivot ( or other fields as
well) and have all other pivots on the sheet, other sheets, and the entire
workbook have there page filed set to this same value.

This would allow me to deploy some fantastic tools to people.

I am speaking of slect the customer(one fo my fields) in a single pivot -
designated as the master pivot if thats easier - then have automated switch
to that cusotmer in all other page fields.

Thanks for the time

can we do multiple pivots on a single sheet
 

thanks i will experiment with it

"MIKE215" wrote:

Todd,

As the example shows now, it will change the page on any number of pivot
tables on a page. The variable y gets a count of the number of pivot tables
and the FOR statement loops through them all and changes the page for each
one.

However, x gets its page value only from the first pivot table on a sheet.
So if the user changes the page in the 5th table and the macro runs nothing
happens. The pages don't change for other tables. If the user changes the
page in table 1 and the macro runs then all the other pages change.

Mike

"Todd F." wrote:

Thanks allot - this is awsome - not totally understanding YET! ut very cool

One question - can it be done when there are multiple pivots on some sheets
and other sheets have only a single pivot.

If we need the single pivot per single sheet ok - I will redo and just have
allot of sheets - not a problem

Two or 3 pivots per page would be a great thing however.

Hey thanks allot for your time on this

"MIKE215" wrote:

Hi Todd

This should get you started :

Sub Macro1()
Dim x As String, y As Integer, scnt As Integer
scnt = ActiveWorkbook.Sheets.Count
x = ActiveSheet.PivotTables(1).PivotFields("n1").Curre ntPage.Value
For j = 1 To scnt
Sheets(j).Activate
y = ActiveSheet.PivotTables.Count
For i = 1 To y
ActiveSheet.PivotTables(i).PivotFields("n1").Curre ntPage = x
Next i
Next j
End Sub

This example assumes one pivot table per sheet. Choose a page field on any
table then run the macro and the page field on all the other sheets will be
set to the same value.

You can run this code from a button placed on each sheet. Or fancier, use
the calculate event to call the macro.
Private Sub Worksheet_Calculate()
Call Macro1
End Sub
This could be very slow on a massive number of large pivot though.

Regards,
Mike


"Todd F." wrote:

I am building massive pivot reports - many pivots on many sheets of a work
book.

Is there a way to select a page field value in a pivot ( or other fields as
well) and have all other pivots on the sheet, other sheets, and the entire
workbook have there page filed set to this same value.

This would allow me to deploy some fantastic tools to people.

I am speaking of slect the customer(one fo my fields) in a single pivot -
designated as the master pivot if thats easier - then have automated switch
to that cusotmer in all other page fields.

Thanks for the time