Pivot Error showing but on a different macro Sheet. Not on the sheet the where I refreshed

[Devonair]

Hi,

This is my first time. I have been looking everywhere for a solution for this
I have made a pivot table on Sheet4 and named the Pivot table to Overtime.
But when I refresh manually by right click the newly made pivot table stops and shows an error

------------------
Run-time Error '1004':

Unable to get the PivotTables property of the Worksheet Class
------------------

When I click Debug the error goes to my sheet3 Not in sheet4 and points to this different Pivot table I made previously

Sub RefreshCustomPivotTable2()
Application.DisplayAlerts = False
With ActiveSheet
.PivotTables("WBS_Pivot").RefreshTable <- this one has the error pointed at
End With
End Sub

Its not the pivot table I refreshed, and not even at the same sheet.

Can anybody help me on this issue. I am using Excel 2013 by the way.

Lots of thanks guys

 

[Michael M]

Maybe change this

With ActiveSheet

to

With Sheets("Sheet4")

 

[Devonair]

Additional info.

I noticed when I go to my Sheet3 after the error the pivot table there WBS_Pivot is selected and highlighted like its being copied.

 

[Devonair]

OK I will try

Lots of thanks

 

[Michael M]

OK, maybe this way then....

Sub RefreshCustomPivotTable2()
Application.DisplayAlerts = False
Sheets("Sheet4").Activate
ActiveSheet.PivotTables("WBS_Pivot").RefreshTable
End Sub

 

[Devonair]

OK, maybe this way then....

Sub RefreshCustomPivotTable2()
Application.DisplayAlerts = False
Sheets("Sheet4").Activate
ActiveSheet.PivotTables("WBS_Pivot").RefreshTable
End Sub

After doing your code + all my range function I put Sheets("Report 02") it didn't show any error. but the odd thing happened
Is that my active sheet went from sheet4(Overtime) to sheet3(Report 02) its like it auto selected it self to another sheet.

 

[Michael M]

Sorry, I don't understand......

After doing your code + all my range function

Do you have any Worksheet_Change events running...OR...any other code ??

 

[Devonair]

Sorry, I don't understand......

Do you have any Worksheet_Change events running...OR...any other code ??

Sorry for my bad explaination.

Yes I do have Worksheet_Change in every sheet in my workbook except to the new one Sheet4(Overtime) .


after putting all the update edits, When I right mouse click

for a Refresh​

Pivot named PivotTable1 from my Sheet4(Overtime). There is no error instead now it will leave Sheet4(Overtime) and go to Sheet3(Report 02) and my pivot table there I named WBS_Pivot ​(before having errors) is somehow selected.

Its a mystery for me.

 

[Michael M]

Hmm makes no sense to me either !
If no steps in with further assistance, I'd suggest uploading to dropbox so we can take a look at it.

 

[Devonair]

OK I uploaded it to drop box where to give the link for the file