Automatically change month for all pivot tables
 

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

Automatically change month for all pivot tables
 

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

--
Regards
Tom Ogilvy

"Marcos Rodríguez" <Marcos wrote in
message ...
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

Automatically change month for all pivot tables
 

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" wrote:

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

--
Regards
Tom Ogilvy

"Marcos RodrÃ*guez" <Marcos wrote in
message ...
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

Automatically change month for all pivot tables
 

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.

--
Regards,
Tom Ogilvy

"Marcos Rodríguez" wrote in
message ...
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" wrote:

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

--
Regards
Tom Ogilvy

"Marcos Rodríguez" <Marcos wrote in
message ...
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

Automatically change month for all pivot tables
 

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" wrote:

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.

--
Regards,
Tom Ogilvy

"Marcos RodrÃ*guez" wrote in
message ...
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" wrote:

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

--
Regards
Tom Ogilvy

"Marcos RodrÃ*guez" <Marcos wrote in
message ...
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

Automatically change month for all pivot tables
 

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

--
Regards,
Tom Ogilvy

"Marcos Rodríguez" wrote in
message ...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Marcos Rodríguez" wrote in
message ...
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" wrote:

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

--
Regards
Tom Ogilvy

"Marcos Rodríguez" <Marcos wrote
in
message ...
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

Automatically change month for all pivot tables
 

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Marcos RodrÃ*guez" wrote in
message ...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Marcos RodrÃ*guez" wrote in
message ...
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" wrote:

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

--
Regards
Tom Ogilvy

"Marcos RodrÃ*guez" <Marcos wrote
in
message ...
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

Automatically change month for all pivot tables
 

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Marcos RodrÃ*guez" wrote in
message ...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Marcos RodrÃ*guez" wrote in
message ...
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" wrote:

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

--
Regards
Tom Ogilvy

"Marcos RodrÃ*guez" <Marcos wrote
in
message ...
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