Pivot table Macro causes Excel to crash

Konuko

New Member
Joined
Mar 21, 2009
Messages
3
Can someone tell me why the following causes Excel to crash.
I am running the SstnPvt Macro using ComboBox controls on a sheet called "Sustainable"

Private Sub ComboBox1_Change()
SstnPvt
End Sub

Private Sub ComboBox2_Change()
SstnPvt
End Sub

Sub SstnPvt()
Dim mob As String
Dim loc As String
Dim yymm As String

On Error Resume Next
Application.ScreenUpdating = False
yymm = Range("SstnYrMo").Value
loc = Range("SstnMca").Value

Select Case loc
Case "SCAL"
mob = "(All)"
Case Else
mob = loc
End Select

'PivotTable1 is Hospital
'PivotTable2 is Clinic
'PivotTable3 is Clinic Work Comp
'PivotTable4 is Clinic TPL

Worksheets("PvtSstn").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields("MCA").CurrentPage = mob
ActiveSheet.PivotTables("PivotTable2").PivotFields("MCA").CurrentPage = mob

If loc = "MORENO VALLEY" Then
ActiveSheet.PivotTables("PivotTable3").PivotFields("MCA").CurrentPage = "(All)"
Else
ActiveSheet.PivotTables("PivotTable3").PivotFields("MCA").CurrentPage = mob
End If

ActiveSheet.PivotTables("PivotTable4").PivotFields("MCA").CurrentPage = mob

ActiveSheet.PivotTables("PivotTable1").PivotFields("YrMo").CurrentPage = yymm
ActiveSheet.PivotTables("PivotTable2").PivotFields("YrMo").CurrentPage = yymm
ActiveSheet.PivotTables("PivotTable3").PivotFields("YrMo").CurrentPage = yymm
ActiveSheet.PivotTables("PivotTable4").PivotFields("YrMo").CurrentPage = yymm

Worksheets("Sustainable").Select
Range("A5").Select
Application.ScreenUpdating = True
End Sub

Thanks for any help you can give. I am stumped.
Konuko
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
Not sure - but the first thing I would do is comment out (or remove) the following lines.

On Error Resume Next
Application.ScreenUpdating = False

That way at least you get to see errors when they happen....

(Application.screenupdating=False is good after you have finished debugging

On Error Resume Next is almost always a bad idea imho)
 
Upvote 0

Konuko

New Member
Joined
Mar 21, 2009
Messages
3
Thanks. I am a newbie at macros. I commented out "On Error Resume Next" but now macro stops at ActiveSheet.PivotTables("PivotTable1").PivotFields("MCA").CurrentPage = mob.
Any idea why? The error says "unable to get the pivot tables property of the worksheet class".
 
Upvote 0

Forum statistics

Threads
1,191,321
Messages
5,985,972
Members
439,993
Latest member
vishalnair70

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top