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,