VBA to Update Pivot Table

[Louise]

Hi,

Can someone help me with my query - I really do not understand why this
doesn't work?!

I have a simple workbook with 2 sheets in in (sheet1 and sheet4).

In sheet1 I have a table of data with 4 fields (Business, PNR, Online,
Tickets).

In sheet4 I have a pivot table running from sheet1 which shows the page
field as business and then a summary of the others below.

I want a list box (validation list) in cell G1 to update the pivot table
page field (business) when I run my macro and I have been using the following
code in a macro:

Sub Alter()
Sheets("Sheet4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Business Unit
Name").CurrentPage = ActiveSheet.Range("g1").Value
End Sub

....When I run this macro I get the following error and i'm not sure why:

Run-time error '1004':
Unable to get th PivotTables property of the Worksheet class

Any help much appreciated..

Many thanks in advance for your time,

 

[Smallweed]

Hi Louise - your code looks good. All I can think is that your pivot table
has a different name now. Try the following to see the name(s) of pivot
tables in the active sheet:

Dim pvt As PivotTable
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
Next

 

[Roger Govier]

Hi Louise

It sounds as though your Pivot table is not "PivotTable1"

Right click on PT>Table Options>look at PT name
Adjust your code to suit.

 

[Debra Dalgleish]

Another option is to refer to the pivot table by index number, instead
of name, e.g.:

ActiveSheet.PivotTables(1)

instead of:

ActiveSheet.PivotTables("PivotTable1")

 

[Louise]

Hi All,

Thanks for your responses - I thought the code was OK but couldn't work out
why it wasn't working! The pivot table name was correct too so I was VERY
confused!

I have rebuilt the source table and pivot table in a new workbook with the
same code etc and it works! Who knows, must be a little Excel gremlin!!!

Much appreciated,

 

[Jitesh Kumar]

Louise, I believe the error is due to the Pivot field name.
It seems you are using the wrong field name. I think here it should be
'Business'.

Just check it.

 

[Paul Martin]

I too have been battling with this dreaded issue, which others clearly
have, but I have not yet found a decent response. I have found a fix,
though I don't know how or why it works.

When I try to pass a seemingly valid value to a CurrentPage, eg:

Set pf = ActiveSheet.PivotTables(1).PivotFields("FiscalYear")

pf.CurrentPage = Range("FiscalYear").Value

I get the 1004 error. I've tried forcing the value as a string using
Cstr, but nothing changes. When I create the Pivot Table, the default
value of the Pivot Field is "(All)". If I manually change it, the
above code works. The problem seems to be to find a way to
legitimately change the value once progammatically, and then the code
has no hassles. My solution is to pass a PivotItem - any one, it
doesn't matter - then it works.

Set pf = ActiveSheet.PivotTables(1).PivotFields("FiscalYear")

pf.CurrentPage = pf.CurrentPage.PivotItems(1).Value ' Force any
value into the PivotField
pf.CurrentPage = Range("FiscalYear").Value ' Now push
desired value into the PivotField

I don't know why this works, but it does. I liken it to push-starting
a car that won't go. Pushing the PivotItem.Value is something the
PivotTable recognises and gets it going. Once it's going, the value
from the worksheet is fine. Voila!

I spent a lot of time on this, and I hope this helps others with the
same problem.


Paul Martin
Melbourne, Australia

 

[Paul Martin]

I've since ascertained that the issue I had was due to the formatting
of cells, which was putting a space at the end of numeric characters.
Changing the cell format to General or a number format resolved the
issue.

 

[Pepe Tapia]

Hello all,

I experienced similar problems with one of my spreadsheets. After hours
of pulling my hair I discovered this MS posting that solved my problem:

http://support.microsoft.com/kb/213955

In short, I had a faulty line in my code, that was removing the labels
in the original data, causing this problem.

I hope this helps!

 

[singh]

Hi Louise

I am using a simple trick for pivots.
1.Create all your pivots manully once before writing the code
2. Write the below code in Your Macro
Sheets("Specify Name").select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
If you have more than 1 Pivots
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

Let me know if it is useful?

 

[kapiszon]

I have a similar issue to your, but still even after reading those posts I
cannot figure out how to sort out my problem.

What I'm trying to do is linking the pivot table filter with a cell that I
placed in a different worksheet. And then, when the value in this cell
changes then in consequence the pivot table changes its filter criteria
automatically. It seems very simple, but I can't resolve it...


Worksheets("Chart_tables").PivotTables("PivotTable4").PivotFields("Destination") _ = Worksheets("Chart_tables").Range("A1").Value

 

[Lolob2b]

Usually missing/no relevant data or not refreshed pivot.

LB