Excel Pivot Table Address, Location, Move & Copy using VBA

Excel Pivot Table Address, Location, Move & Copy using VBA

This section explains how to get the Address of a PivotTable in a worksheet; Move PivotTable to a new location; Copy a PivotTable and paste as values or paste as a PivotTable.


Related Links:

1. Create an Excel Pivot Table report using VBA

2. Referencing an Excel Pivot Table Range using VBA

3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA

4. Excel Pivot Table Address, Location, Move & Copy using VBA

5. Excel Pivot Table Layout and Design, using VBA

6. Excel Pivot Table Properties & Settings, using VBA

7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA

8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA

9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA

10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA

11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA

12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA

13. Create & Customize Excel Pivot Table Charts, using VBA

Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):

Create and Customize a Pivot Table report


——————————————————————————————————

Contents:

Address of Pivot Table

Move PivotTable to a new location

Copy a PivotTable: (i) and paste as data or values; (ii) and paste as a PivotTable

——————————————————————————————————

Address of Pivot Table

Code to get address of the top-left cell of a PivotTable (without the page fields):

MsgBox Worksheets(“Sheet1”).PivotTables(“PivotTable1”).TableRange1.Cells(1).Address

Code to get address of the top-left cell of a PivotTable (including the page fields):

MsgBox Worksheets(“Sheet1”).PivotTables(“PivotTable1”).TableRange2.Cells(1).Address

Move PivotTable to a new location

Move PivotTable to a new location, using the PivotTable.Location Property. The specified location is in respect of the top-left cell (without the page fields) of the PivotTable.

Code to move PivotTable to a different worksheet:

Worksheets(“Sheet1”).PivotTables(“PivotTable1”).Location = “Sheet2!E4”

Copy a PivotTable: (i) and paste as data or values; (ii) and paste as a PivotTable.

1. Copy PivotTable cells to a new range, and paste as data or values and not as a PivotTable

Example 1: Refer Image 1 which shows the original PivotTable at the top, and at the bottom are the values copy-pasted after running the below code.

Image 1

Sub PivotTableCopy1()

Dim PvtTbl As PivotTable
Dim rng As Range
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate

‘determine last used row:
lastRow = Worksheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row

‘omit copying the top row of the PivotTable:
PvtTbl.TableRange2.Offset(1, 0).Copy
Worksheets(“Sheet1”).Cells(lastRow + 2, 1).PasteSpecial xlPasteValues

‘Delete the PivotTable:
PvtTbl.TableRange2.Clear

‘delete all rows at the top:
Set rng = Worksheets(“Sheet1”).Range(“A1:A” & lastRow + 1)
rng.Select
Selection.EntireRow.Delete

End Sub

Example 2: Refer Image 2 which shows the original PivotTable at the top, and at the bottom are the values copy-pasted after running the below code.

Image 2

Sub PivotTableCopy2()

Dim PvtTbl As PivotTable
Dim rng As Range
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

PvtTbl.Location = “Sheet1!A1”
PvtTbl.TableRange2.Copy
Worksheets(“Sheet1”).Cells(PvtTbl.TableRange2.Rows.Count + 2, 1).PasteSpecial xlPasteValues

Set rng = Worksheets(“Sheet1”).Range(“A1:A” & PvtTbl.TableRange2.Rows.Count + 1)
rng.Select
Selection.EntireRow.Delete

End Sub

2. Copy a PivotTable to a new worksheet, and paste as a PivotTable

Example 3: see below code.

Sub PivotTableCopy3()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

‘select the PivotTable which you want to copy, using the PivotTable.PivotSelect Method
Worksheets(“Sheet1”).Select
PvtTbl.PivotSelect “”, xlDataAndLabel, True
Selection.Copy

‘add a new worksheet and name it, where you want the PivotTable to be pasted:
Worksheets.Add
ActiveSheet.Name = “PT_Copy1”

‘paste the selected PivotTable to the new worksheet:
Worksheets(“PT_Copy1”).Range(“A3”).PasteSpecial
‘Note, using this line will paste as values and not as a PivotTable:- Worksheets(“PT_Copy1”).Range(“A1”).PasteSpecial xlPasteValues

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top