VB Pivot Table Refresh Excel 97
 

I have a simple pivot table on one sheet with the data on the second
sheet. I have added a command button and I would like the user to be
able to use it to refresh the data on the pivot table. I have
recorded the action and placed it in the code for the button but it
fails. Here is what I have recorded:

Private Sub rtable_Click()
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Part
Number'[All]", _
xllabelOnly
ActiveSheet.PivotTables("PivotTable1").refreshtabl e
End Sub

When I manually right click on the table and refresh it works with
noproblem.

When I use the button with code above I get the error:

Run Time error'1004'
RefreshTable methode of PivotTable class failed.

If I only use the second line of code I still get the same error.
What else can I do to get he Table to refresh using VB.

Any help is greatly appreciated.

Thank you,

Darren

VB Pivot Table Refresh Excel 97
 

Firstly if you define your range using a dynamic range. Go to Insert
- Name - Define and type a range name and put this code into your
range eg "Range1".

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

Remember to change the sheet name as appropriate. Reference your
pivot table not to a range but to but to "Range1".

Then go into VBA not an ordinary module but the worksheet that your
pivottable is on and paste this code.

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(1).RefreshTable
End Sub

You will not need an update button as the pivot table will update once
the pivot sheet is activated.

Regards

Ben


(Darren DeCoste) wrote in message . com...
I have a simple pivot table on one sheet with the data on the second
sheet. I have added a command button and I would like the user to be
able to use it to refresh the data on the pivot table. I have
recorded the action and placed it in the code for the button but it
fails. Here is what I have recorded:

Private Sub rtable_Click()
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Part
Number'[All]", _
xllabelOnly
ActiveSheet.PivotTables("PivotTable1").refreshtabl e
End Sub

When I manually right click on the table and refresh it works with
noproblem.

When I use the button with code above I get the error:

Run Time error'1004'
RefreshTable methode of PivotTable class failed.

If I only use the second line of code I still get the same error.
What else can I do to get he Table to refresh using VB.

Any help is greatly appreciated.

Thank you,

Darren

VB Pivot Table Refresh Excel 97
 

Ben,

Thank you for your help. This worked great.

Darren

(Ben) wrote in message . com...
Firstly if you define your range using a dynamic range. Go to Insert
- Name - Define and type a range name and put this code into your
range eg "Range1".

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

Remember to change the sheet name as appropriate. Reference your
pivot table not to a range but to but to "Range1".

Then go into VBA not an ordinary module but the worksheet that your
pivottable is on and paste this code.

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(1).RefreshTable
End Sub

You will not need an update button as the pivot table will update once
the pivot sheet is activated.

Regards

Ben


(Darren DeCoste) wrote in message . com...
I have a simple pivot table on one sheet with the data on the second
sheet. I have added a command button and I would like the user to be
able to use it to refresh the data on the pivot table. I have
recorded the action and placed it in the code for the button but it
fails. Here is what I have recorded:

Private Sub rtable_Click()
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Part
Number'[All]", _
xllabelOnly
ActiveSheet.PivotTables("PivotTable1").refreshtabl e
End Sub

When I manually right click on the table and refresh it works with
noproblem.

When I use the button with code above I get the error:

Run Time error'1004'
RefreshTable methode of PivotTable class failed.

If I only use the second line of code I still get the same error.
What else can I do to get he Table to refresh using VB.

Any help is greatly appreciated.

Thank you,

Darren