VBA to Create Pivot Table on a new worksheet

[Arise]

Hello,

To give some background of what I am doing, I have a workbook where there is a tab labeled Raw Data which is a data extract from a SQL query. The extract piece is working properly, and I'm now trying to code to create a new sheet called Power Outage Support Report in the workbook, and populate it with a Pivot Table for easy manipulation and viewing of the data. I've attempted to use the following thread, specifically the first reply, as a guide.

In looking at the example they provided, I created my own code to follow suit, and updated the default version number for the version of Excel that I'm using which is Excel 2010. I feel as though I'm inching closer to my goald, but I'm getting an Invalid Call or Procedure error when I attempt to assign my Pivot Table object the value to create the new Pivot Table. I'm still rather new to VBA, but not programming in general, so I often have to look up various syntax rules. However this one has me stumped as it is a mirror of what was posted in the thread that I linked.

Also, I have tried recording the macro and then using that code, however I ran into a number of issues with the code that was generated, and I felt that for my own learning purposes, it would be better if I understood how to write some simple custom pivot table VBA, so that if/when I inevitably have to create another report similar to this one, I have something to reference. I've included the code for the subroutine in its entirety below with a comment on the line that the debugger points to when it encounters the run time error. I'm not even sure if the With loop will work since I pretty much took that idea from the linked thread, and then used my own modifications for how I wanted the data to be displayed in the pivot table. If anyone has any suggestions on how to fix this code, or to a better example than the one I'm using, I'm all for it. This has been quite the learning experience thus far

Note: I delete the Power Outage Support Report sheet at the beginning as this is a macro that will be run on a regular basis, and thus I want to re-use the same sheet name.

Sub Create_Pivot()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim objTable As PivotTable, objField As PivotField
    Dim pivCache As pivotCache
    Dim lastRow As Long
    
    Set wb = ThisWorkbook
    wb.Sheets("Raw Data").Select
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row    'get last row of data (in column 1)
       
    wb.Names.Add Name:="Database", RefersTo:="=Raw Data!$A$1:$F$" & lastRow   'add a named range set to data range to assist in Pivot Table Creation
        
    Application.DisplayAlerts = False
    wb.Worksheets("Power Outage Support Report").Delete
    Worksheets.Add.Name = "Power Outage Support Report"
    Application.DisplayAlerts = True
    Set ws = wb.Worksheets("Power Outage Support Report")
    wb.Sheets("Power Outage Support Report").Select
    
    'create pivot cache & table
    Set pivCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Database")
    Set objTable = pivCache.CreatePivotTable(TableDestination:=ActiveSheet.Cells(3, 1), TableName:="PT1", DefaultVersion:=xlPivotTableVersion14) 'THIS IS THE LINE THAT I RECEIVE RUN TIME ERROR 5
    
    With objTable
        Set objField = objTable.PivotFields("Project")
        objField.Orientation = xlRowField
        objField.Position = 1
    
        Set objField = objTable.PivotFields("Fiscal Month")
        objField.Orientation = xlRowField
        objField.Position = 2
    
        Set objField = objTable.PivotFields("Costs")
        objTable.AddDataField objField, "Sum of Costs", xlSum
    
        Set objField = objTable.PivotFields("Year")
        objField.Orientation = xlRowField
        objField.Position = 1
    
        Set objField = objTable.PivotFields("Hours")
        objTable.AddDataField objField, "Sum of Hours", xlSum
    
        Set objField = objTable.PivotFields("Project")
        objField.Orientation = xlColumnField
        objField.Position = 1
    
        objTable.CompactLayoutColumnHeader = "Project"
    
        objTable.ShowTableStyleRowStripes = True
    
    End With
    
    Set objTable = Nothing
    Set pivotCache = Nothing
    Set wb = Nothing

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Try with these modifications..

'--single quotes are needed when sheet names include spaces
wb.Names.Add Name:="Database", RefersTo:="='Raw Data'!$A$1:$F$" & lastRow

'--for xl2010 use .Create Method instead of .Add
'--reference to Name Database needs leading "=" symbol
 Set pivCache = wb.PivotCaches.Create(SourceType:=xlDatabase, _
      SourceData:="=Database", Version:=xlPivotTableVersion14)
 Set objTable = pivCache.CreatePivotTable _
     (TableDestination:=ActiveSheet.Cells(3, 1), TableName:="PT1", _
     DefaultVersion:=xlPivotTableVersion14)

 

[Arise]

Hi Jerry,

Thanks for welcome and the help! That did the trick and now the pivot table is generating exactly as I want it to. I figured it was something syntax related, and I would've never figured that one out on my own, so thanks again!