Copying Pivot Tables on to one sheet using VBA

[WildfireS3]

Hi All

I'm a beginner to VBA, so please be gentle.

I have realsied that I can speed up a lot of my reports by automating them using VBA. However I'm a complete novice.

I have managed to get Excel to kick out 13 pivot tables to indivdual sheets, but now I want to consolidate these into one sheet.

I would like to copy the pivot table from each sheet, place it on to a new sheet, count 6 cells down then paste the next one in. So I end up with a master sheet with all 13 pivot tables on it.


Any takers?

Thanks

 

[Andrew Poulsom]

Why don't you put all the pivot tables on that sheet in the first place?

 

[WildfireS3]

Hi Adam

That would be good, but I'm not sure how to get Excel to find the last cell, in the last pivot table on the sheet, count 6 cells down then create the new pivot table.

Thanks

 

[Andrew Poulsom]

You can use the PivotTable's TableRange2 property like this:

Sub Test()
    With ActiveSheet.PivotTables(1).TableRange2
        MsgBox .Cells(.Rows.Count, 1).Address
    End With
End Sub

 

[WildfireS3]

Thanks Andrew!