Automatically change month for all pivot tables

[Guest]

I'm from the Dominican Republic and I work as a Media Analyst at an
Advertising Agencies. I need some help regarding the use of pivot tables.
The thing is I have a workbook with several worksheets and pivot tables
which I refresh at least monthly. There are some graphs that are linked
to the results of these pivot tables. As I told you, I update this
databases monthly, in order to elaborate monthly Competitive Advertising
Reports. Each time I do this i have to manually change the field month
for the current month. For instance it's January now and last month was
december, so I have to change the month in the drop-down menu for this
field for all pivot tables that apply. I was wondering if you could help
me out creating a macro to do this automatically.

One problem might be that not all pivot tables have the field month,
so
if I try to apply this setting for all pivot tables VBA will return me
an error, right?

Please help... It will be greatly appreciated,

Marcos Rodríguez

 

[Tom Ogilvy]

Debra Dalgleish has extensive examples on working with Pivot Tables, both
manually and programmaticlly.

This is a likely place to start

http://www.contextures.com/xlPivot03.html

You can also go to here index page and look under P
http://www.contextures.com/tiptech.html

 

[Guest]

Tom:

Thanks for your soon response and your desire to help me. But sadly your
advice hasn't been helpful, given that it was Debra who told me to seek help
in this forum. I don't know if she told me so because she's too busy right
now to analyze my request or because here I could find more help.

I wander if you understand my need. I hope it's not misleading.

Thank you and I look forward for your reply

 

[Tom Ogilvy]

Assuming it is a pagefield in the pivot table

Dim sh as Worksheet, pvtTbl as PivotTable
Dim pgefld as PageField
for each sh in Worksheets
for each pvtTbl in sh.PivotTables
On error Resume Next
set pgefld = pvtTbl.PageFields("Month")
On Error goto 0
if not pgefld is Nothing then
pgefld.Value = "January"
end if
Next
Next

would be where I would start.

There are a lot of inherent assumptions which may not match you situation.

 

[Guest]

Hey Tom!

Thanks once again for your willingness to help and sorry for my late
response. I tried this macro you're suggesting me, but it returns an error
window stating: "Compile Error: User-defined type not defined".

Hope to hear from you soon...

P.D.: How do you pronounce Ogilvy?

 

[Tom Ogilvy]

PageField should be PivotField
pgeFld.Value = should be pgefld.CurrentPage =

below is tested and worked for me. Make sure you make a copy of your
workbook and test the code on a copy.

Sub EFGH()
Dim sh As Worksheet, pvtTbl As PivotTable
Dim pgefld As PivotField
For Each sh In Worksheets
For Each pvtTbl In sh.PivotTables
On Error Resume Next
Set pgefld = pvtTbl.PageFields("Month")
On Error GoTo 0
If Not pgefld Is Nothing Then
pgefld.CurrentPage = "January"
End If
Next
Next
End Sub

 

[Guest]

Hi Tom,

Looks like this macro is closer to working now, but it still does not. When
I test it, I receive the following error: Run-time error '1004': Unable to
set the _Default property of the PivotItem class.

And then when I press the Debug button of the error dialog box, this part is
highlithed: "pgefld.CurrentPage = "DIC""

As you'll see, it says "DIC", and not January because I have my months in
Spanish and abreviated, that one corresponds to December. I also replaced the
field "Month" with "Mes".

Thanks in advanced...

 

[Guest]

Hey Tom,

I have to remember you that not all pivot tables have the same source data,
if it's something you have to take in consideration