Command Button run-time error

[Dominique Feteau]

I have a command button on a sheet that is supposed to look through 3 sheets
and refresh pivottables. for some reason it's giving me a runtime error.

Here's the code I'm using

Sub CommandButton1_Click()

Sheets("Cert Tracking YTD Totals").Select
ActiveSheet.PivotTables("CT_Totals").RefreshTable
ActiveSheet.PivotTables("CT_Losses").RefreshTable
ActiveSheet.PivotTables("CT_Product_Totals").RefreshTable
ActiveSheet.PivotTables("CT_Product_Losses").RefreshTable

Sheets("Cert Tracking Monthly Total").Select
ActiveSheet.PivotTables("CT_Monthly_Totals").RefreshTable
ActiveSheet.PivotTables("CT_Monthly_Losses").RefreshTable

Sheets("CertTracking Monthly Prod Tot").Select
ActiveSheet.PivotTables("CT_Monthly_Prod_Totals").RefreshTable
ActiveSheet.PivotTables("CT_Monthly_Prod_Losses").RefreshTable

Sheets("Cert Tracking YTD Totals").Select
Range("A1:C2").Select

End Sub

Can someone tell me whats wrong? If its any help, i originally created the
code in excel 2K and i'm trying to edit it in excel97.

thanks

 

[Dave Peterson]

There's a bug in xl97 (fixed in xl2k) that deals with code called from controls
from the control toolbox toolbar that are used on a worksheet.

One fix is to change the .takefocusonclick property to false.

Another fix is to add:
activecell.activate
at the top of your code.

===
And you may want to eliminate some of the selecting:

Option Explicit
Sub CommandButton1_Click()

With Sheets("Cert Tracking YTD Totals")
.PivotTables("CT_Totals").RefreshTable
.PivotTables("CT_Losses").RefreshTable
.PivotTables("CT_Product_Totals").RefreshTable
.PivotTables("CT_Product_Losses").RefreshTable
End With

With Sheets("Cert Tracking Monthly Total")
.PivotTables("CT_Monthly_Totals").RefreshTable
.PivotTables("CT_Monthly_Losses").RefreshTable
End With

With Sheets("CertTracking Monthly Prod Tot")
.PivotTables("CT_Monthly_Prod_Totals").RefreshTable
.PivotTables("CT_Monthly_Prod_Losses").RefreshTable
End With

End Sub

and if you're updating all the pivottables in all the worksheets:

Private Sub CommandButton1_Click()
With Me.Parent
.RefreshAll
End With
End Sub