control many pivot tables
 

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!!

--
Message posted from http://www.ExcelForum.com

control many pivot tables
 

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
-----Original Message-----
i have many pivot tables in a workbook, and i want to
find way to
control all of them toghter. i mean for example: if i
have many pivot
tables and i want to change the month (date) in all of
them togther.
help!!!


---
Message posted from http://www.ExcelForum.com/

.

control many pivot tables
 

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

--
Message posted from http://www.ExcelForum.com

control many pivot tables
 

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???

--
Message posted from http://www.ExcelForum.com

control many pivot tables
 

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

thank you very much

Tzu

--
Message posted from http://www.ExcelForum.com

control many pivot tables
 

Hi

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

regards,
Hans
-----Original Message-----
hi
sorry for all the previous massages, the macro work great
after i
"play" with it....

thank you very much

Tzur


---
Message posted from http://www.ExcelForum.com/

.

control many pivot tables
 

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

--
Message posted from http://www.ExcelForum.com