Forum Discussion

WIllowED's avatar
WIllowED
Occasional Reader
Dec 05, 2025

Trying to insert Pivot Table

I recorded a new macro, using the tab View Macros-Record Macro, which resulted in the following VBA code:

   Sub CreatePivotTable()
       Sheets("Year Data").Select
       Range("A3:F24").Select
       ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
          "Year Data!R3C1:R24C6", Version:=xlPivotTableVersion14).CreatePivotTable _
          TableDestination:="Yearly Summary!R3C1", TableName:="PivotTable3", _
          DefaultVersion:=xlPivotTableVersion14
         
       Sheets("Yearly Summary").Select
       Cells(3, 1).Select 
   End Sub

The recording did exactly as I wanted, taking the data from the "Year Data" WS and adding the new pivot table to the "Yearly Summary" WS. 

As a test of the macro, I deleted the initial pivot table that was created on the "Yearly Summary" WS.  The problem I have now, when I attempt to run this macro again it constantly fails on the 4th line of the macro with Run-time error '5': Invalid procedure call or argument.

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
          "Year Data!R3C1:R24C6", Version:=xlPivotTableVersion14).CreatePivotTable _
          TableDestination:="Yearly Summary!R3C1", TableName:="PivotTable3", _
          DefaultVersion:=xlPivotTableVersion14

I cannot figure out why it works when I am recording the macro but fails when I try to run it alone. 

Help is greatly appreciated.

 

excel
Macros and VBA

1 Reply

  • Leina's avatar
    Leina
    MCT
    Dec 06, 2025

    Sub CreatePivotTable()

        Dim wb As Workbook
        Dim wsData As Worksheet
        Dim wsSummary As Worksheet
        Dim rngSource As Range
        Dim pc As PivotCache
        Dim pt As PivotTable

        ' Always point to the workbook that holds this code
        Set wb = ThisWorkbook          ' Or Workbooks("YourFileName.xlsx")

        ' Source and destination sheets
        Set wsData = wb.Worksheets("Year Data")
        Set wsSummary = wb.Worksheets("Yearly Summary")

        ' Source range (you can keep it fixed or use CurrentRegion)
        Set rngSource = wsData.Range("A3:F24")
        ' Or:
        ' Set rngSource = wsData.Range("A3").CurrentRegion

        ' Optional: remove existing pivot with the same name
        On Error Resume Next
        wsSummary.PivotTables("PivotTable3").TableRange2.Clear
        On Error GoTo 0

        ' 1) Create the PivotCache
        Set pc = wb.PivotCaches.Create( _
                SourceType:=xlDatabase, _
                SourceData:=rngSource, _
                Version:=xlPivotTableVersion14)

        ' 2) Create the PivotTable from that cache
        Set pt = pc.CreatePivotTable( _
                TableDestination:=wsSummary.Range("A3"), _
                TableName:="PivotTable3", _
                DefaultVersion:=xlPivotTableVersion14)

        ' (Optional) select the top-left cell of the new pivot
        wsSummary.Activate
        wsSummary.Range("A3").Select

    End Sub

Resources