CODEWITHSUNDEEP
vbaexcel
msim
msim

Reputation: 373

Unable to get the PivotTables Property of the Worksheet Class

I am trying to use some VBA that another person made and it is giving me an error that I can't seem to fix. Here's the code:

Sub Format_Homeward_V3()
' Format_Homeward_V3 Macro
    ChDir "J:\Templates"
    Workbooks.Open Filename:= _
        "J:\Templates\Homeward_Exception_Report_Template_Final.xlsx"
    Workbooks.Open Filename:= _
        "J:\Templates\Homeward_Inventory_Report_Template_Final.xlsx"
    Range("A4").Select
    Sheets("Homeward_Summary").PivotTables("MyPivot").PivotCache.Refresh
    Sheets("Detail").Select
    Range("A1").Select

it errors out at: 

Sheets("Homeward_Summary").PivotTables("MyPivot").PivotCache.Refresh

and gives the error:

"unable to get the pivottables property of the worksheet class"

But as far as I can tell it is looking at the right sheet and the right name of the PivotTable. I also tried doing .RefreshTable instead of cache refresh but got the same error. I don't really know why it would be giving me this error and and I don't know what else to try. It's been working without this error for a while.

Upvotes: 1

Views: 9174

Answers (1)

neuralgroove
neuralgroove

Reputation: 580

Make sure everything is referenced, then there is no guesswork about which workbook or sheet you are trying to deal with (pardon the shorthand, alter as required)

Sub Format_Homeward_V3()
Dim wbe As Workbook
Dim wbi As Workbook
Dim ws As Worksheet
Set wbe = Workbooks.Open("J:\Templates\Homeward_Exception_Report_Template_Final.xlsx")
Set wbi = Workbooks.Open("J:\Templates\Homeward_Inventory_Report_Template_Final.xlsx")
Set ws = wbi.Sheets("PIVOT")
ws.Range("A4").Select
ws.PivotTables("MyPivot").PivotCache.Refresh

Upvotes: 1

Related Questions