Select entire Pivot Table using VBA


November 9th, 2009 by admin admin

We can select the entire Pivot Table by using the special command called TableRange2. The following codes is to select the entire Pivot Table and paste it to another location (like a blank worksheet).

Sub copy_Pivot_Table()

‘Used range is a command readily avaiable in Excel VBA.
ThisWorkbook.Worksheets(“Sheet1″).UsedRange.Clear

‘The code below is to identify and perform a copy of Pivot Table.
‘TableRange2 is a special command that refers to the entire Pivot Table
With Worksheets(“Report”)
.PivotTables(“PivotTable2″).TableRange2.Copy
End With

With ThisWorkbook.Worksheets(“Sheet1″)
.Activate
.Range(“A4″).PasteSpecial Paste:=xlPasteAll
.Range(“A4″).PasteSpecial Paste:=xlValues
.UsedRange.Columns.AutoFit
End With

End Sub

One Response to “Select entire Pivot Table using VBA”

  1. odιoѕιn™ Says:

    thanks a lot very useful :D

Leave a Reply

You must be logged in to post a comment.

s2Member®