I am having an Error in creating Pivot using Excel VBA

Question

I am trying to create a pivot using below code, however I am receiving error,

"Invalid Procedure call or argument"

Can someone please help.

Here is my code:

Sub Creatingquerypivot()
    
        Dim sht As Worksheet
        Dim pvtCache As PivotCache
        Dim pvt As PivotTable
        Dim StartPvt As String
        Dim SrcData As String
        Dim Pf As PivotField

'Create Pivot Cache from Source Data
        Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=Sheets("Queries").Name & "!" & Sheets("Queries").Range("A3").CurrentRegion.Address)
                 
'Create a new worksheet
        Set sht = Sheets.Add
        sht.Name = "Queries Summary"

'Where do you want Pivot Table to start?
        StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlA1)
        
'Create Pivot table from Pivot Cache

        

 - Set pvt = pvtCache.CreatePivotTable( _
           TableDestination:=StartPvt, _
           TableName:="QueriesSummary")

    
    'To add fields to the pivot table
    
        Set Pf = pvt.PivotFields("Folder")
        Pf.Orientation = xlRowField
           
        Set Pf = pvt.PivotFields("Category")
        Pf.Orientation = xlRowField
            
        Set Pf = pvt.PivotFields("Received")
        Pf.Orientation = xlDataField

Answer 1

As BigBen mentioned, TableDestination expects a Range object.

Dim StartPvt As Range

But you'll also need to Set it like so:

From:

StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlA1)

To:

Set StartPvt = sht.Range("A3")

Answer 2

Worksheet names with spaces need to be single-quoted. It's good practice to always add quotes, even if the name has no spaces: it doesn't harm, and you don't have to worry about fixing your code if you change the sheet name later.

Edit: on testing, seems like it only works with R1C1 format range addresses?

Set sht = Sheets.Add
sht.Name = "Queries Summary"

StartPvt = "'" & sht.Name & "'!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

Having said that, you already have Range objects you can use directly without first converting them to string range addresses:

    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=Sheets("Queries").Range("A1").CurrentRegion)
             
    Set sht = Sheets.Add
    sht.Name = "Queries Summary"
    Set pvt = pvtCache.CreatePivotTable(TableDestination:=sht.Range("A3"), _
                                        TableName:="QueriesSummary")