here is a strange one, hopefully someone know why this is happening or has seen something like this before.
i have a query update that is running from either a calendar or combobox on another sheet.
when i step through the macro, it starts out fine, but when it gets to the end of the macro for the query refresh, it skips the end sub part and jumps to a different macro that runs off a seperate sheet all together. i end up with a run time error telling me the pivot refresh method failed (even though when i normally run that pivot refresh its fine.)
here is some of the code:
this is off the combobox which starts it off. the refresh roster allocation is run from here.
here is the end of the roster allocation query
it runs to "wsBMIS.Cells(1, 1).ListObject.QueryTable.Refresh BackgroundQuery:=False", and instead of running end sub and returning to the initiating macro, it jumps to a private sub on a different sheet module.
and here is where i end up with the runtime error
any help would be appreciated. the rest of the workbook is running fine except for this error. cheers
joe
i have a query update that is running from either a calendar or combobox on another sheet.
when i step through the macro, it starts out fine, but when it gets to the end of the macro for the query refresh, it skips the end sub part and jumps to a different macro that runs off a seperate sheet all together. i end up with a run time error telling me the pivot refresh method failed (even though when i normally run that pivot refresh its fine.)
here is some of the code:
this is off the combobox which starts it off. the refresh roster allocation is run from here.
Code:
Private Sub ServZoneBox1_Change()
Worksheets("Service Zone Viewer").Activate
ActiveSheet.Unprotect
Cells(6, 5).Value = ServZoneBox1.Value
RefreshRosterAllocation 'in main module
RefreshResourceCapacity 'in main module
Worksheets("Service Zone Viewer").Activate
ActiveSheet.Protect
End Sub
Code:
wsBMIS.Cells(1, 1).ListObject.QueryTable.CommandText = query
'refresh BMIS Data
On Error GoTo BMISFail2
wsBMIS.Cells(1, 1).ListObject.QueryTable.Refresh BackgroundQuery:=False
BMISFail2:
End Sub
and here is where i end up with the runtime error
Code:
Private Sub Worksheet_Activate()
PivotTables("PivotTable1").RefreshTable
End Sub
joe