Unable to get pivottable worksheet property

[rshanka2]

Hello All

I'm trying to merge a pivot table macro file from one worksheet to another workbook. When I merge and run macro, it gives the above error and also user defined error. I also tried pivottable 1 instead of 5. No luck. Any help is appreciated. Thanks Everyone

Sub Rollup()
'
' Rollup Macro
'

'
    Dim LR As Long
    LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Enterprise Lane Roll Up").Select
    ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
    Columns("A:I").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("J2:J" & LR), Type:=xlFillDefault
    Range("J2:J" & LR).Select
    Columns("J:J").Select
    Range("J397").Activate
    Selection.Copy

 

[Luke M]

I would check first that when you are running the code, the active sheet is the one with the PivotTable. Next, double-check the name of your PT. YOu can do this by selecting a cell within the PT, and then go to Design ribbon, and look at far left. Often when you copy sheets/PTs, the name gets changed.

Along that line, if the PivotTable is the only one on the sheet, you can simplify that line of code by using an Index number rather than name, like so:

 ActiveSheet.PivotTables(1).PivotCache.Refresh

 

[rshanka2]

Thanks for your help. Active sheet is on a different workbook. I copied the pivot table to merge to above workbook shown and it gives an error even now. I want to make sure the pivot table runs on any worksheet. I don't come from a VBA background.
How do I write the code so that macro pivot table runs on any worksheet.

 

[Luke M]

If you want to be able to run the macro on any workbook, on the sheet that is currently active, try this:

Sub Rollup()
'
' Rollup Macro
'

   Dim LR As Long
    LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.PivotTables(1).PivotCache.Refresh
    With Columns("A:I")
         .Copy
         .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    Range("J2").AutoFill Destination:=Range("J2:J" & LR), Type:=xlFillDefault
    Range("J397").Copy

' This is as much as I can see of your code...

 

[rshanka2]

Thank You.