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,

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

"Louise" wrote:

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,

Hi Louise

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

Right click on PTTable Optionslook at PT name
Adjust your code to suit.

--

Regards
Roger Govier

"Louise" wrote in message
...
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,

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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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,

"Debra Dalgleish" wrote:

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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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.



*** Sent via Developersdex http://www.developersdex.com ***

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





On Mar 7, 7:24 am, Jitesh Kumar wrote:
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.

*** Sent via Developersdexhttp://www.developersdex.com***

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.

On Mar 7, 5:47 pm, Paul Martin wrote:
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

On Mar 7, 7:24 am, Jitesh Kumar wrote:

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.

*** Sent via Developersdexhttp://www.developersdex.com***

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!





*** Sent via Developersdex http://www.developersdex.com ***

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?


"Louise" wrote:

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,

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("PivotTable 4").PivotFields("Destination") _ = Worksheets("Chart_tables").Range("A1").Value




"Debra Dalgleish" wrote:

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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Usually missing/no relevant data or not refreshed pivot.

LB

*** Sent via Developersdex http://www.developersdex.com ***