control many pivot tables

T

tzcarmy

i have many pivot tables in a workbook, and i want to find way t
control all of them toghter. i mean for example: if i have many pivo
tables and i want to change the month (date) in all of them togther.
help!!
 
H

Hans

Hi

I was working on the same problem yesterday. Maybe this
macro helps:

(The bPeriod referes to a cell on a normal sheet; you
could change it to an Input box if you want)


Sub PivotChangePeriod()
'this macro changes the period in the pivottables
'in sheets LI(cc) and LP(cc)to the current
'period (mentioned on sheet OVERVIEW, range is
'called period)

Dim bPeriod As Byte
Dim PT As PivotTable
Dim PTS As PivotTables
Dim Sh1 As Sheets
Dim wk As Worksheet

bPeriod = Range("period").Value
Set Sh1 = Worksheets(Array("LI(cc)", "LP(cc)")

For Each wk In Sh1
For Each PT In wk.PivotTables
With PT
.PivotFields("period").CurrentPage = bPeriod
End With
Next PT
Next wk
End Sub


regards, Hans
 
T

tzcarmy

thank you for the macro
i trid to use it, but the line

Set Sh1 = Worksheets(Array("LI(cc)", "LP(cc)")

get a red color. what i need th change to use it rigth, the name of th
sheets instad of the cc ???

thank's agai
 
T

tzcarmy

ok, i understand what i asked before, and i put the sheets names, bu
now i get a compile error massage:

"worng number of aruments or invalid proprty assigment"

what sould i do???
 
T

tzcarmy

hi
sorry for all the previous massages, the macro work great after i
"play" with it....

thank you very much

Tzu
 
G

Guest

Hi

glad it works ok now...it took me some time too how to
handle this problem!

regards,
Hans
 
T

tzcarmy

you must have a list structure to create pivot table.
that's mean:
title row, and empty row on the bottom and and top of the list, an
empty column on each side of the list.
:rolleyes
 
Top