VB Pivot Table Refresh Excel 97

D

Darren DeCoste

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").refreshtable
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
 
B

Ben

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),COUNTA(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
 
D

Darren DeCoste

Ben,

Thank you for your help. This worked great.

Darren

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),COUNTA(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


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").refreshtable
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top