A question about how to use Excel Macro to create Pivot table

October 24, 2022

I am a newbie on Excel macro. I am learning using Macro to create a pivot table for a project. Eventually I will use Power Automate to make it fully automatically run every day.

First I tried to use Record Macro in Excel to figure out how to create a Pivot table and I got code below.

Range("A1:W37").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R37C23", Version:=8).CreatePivotTable TableDestination:= _
    "Sheet19!R3C1", TableName:="PivotTable2", DefaultVersion:=8
Sheets("Sheet19").Select
Cells(3, 1).Select

The problem I couldn’t figure out is how to make sure I can select all cells in the worksheet and store them into a variable. Then I can put that variable after SourceData:= _ (which means this variable will replace the code "Sheet1!R1C1:R37C23"). I search on the Internet that I can select all cells with data by following code:

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Tried to google it but no luck after few hours. Is there any suggestion?

>Solution :

You can do it like this:

Sub Tester()
    
    Dim ws As Worksheet, wsPT As Worksheet, rngData As Range, wb As Workbook
    Dim pc As PivotCache, pt As PivotTable
    
    Set wb = ActiveWorkbook   'or some other specific workbook
    Set ws = wb.Worksheets(1) 'or some other specific sheet
    
    'same as selecting A1 and pressing Ctrl+A
    Set rngData = ws.Range("A1").CurrentRegion
    
    Set wsPT = wb.Worksheets.Add() 'get a reference to the new sheet
    
    'create pivot cache
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
                           SourceData:=rngData, Version:=8)
    'create pivot table
    Set pt = pc.CreatePivotTable(TableDestination:=wsPT.Range("A3"), _
                      TableName:="PivotTable2", DefaultVersion:=8)
    
    'work with `pt`....
End Sub

You get more control if you split up the creation of the pivot cache and the pivot table into separate steps.