Trouble creating pivot table in VBA

[jasonfish11]

All,
I've searched for the answer to my question and haven't found much help. I was hoping someone could point out where I'm missing something as the below coding fails at the last line and never creates a pivot table.

'Create pivot tables
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String


Worksheets("Combined").Select
SrcData = ActiveSheet.Name & "!" & Range("A1:E" & comLR2).Address(ReferenceStyle:=xlR1C1)


Worksheets("Recon Pivot").Select
StartPvt = ActiveSheet.Name & "!" & Range("A2").Address(ReferenceStyle:=xlR1C1)
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTable1")

The variables of SrcData and StartPvt both appear correctly when I hover over them in VBA.
Any help would be much appreciated.

 

[jasonfish11]

So I did some more digging on the internet and found that I had to add apostrophe's in the StartPvt coding. I'm not sure why yet but it is now working.

StartPvt = "'" & ActiveSheet.Name & "'!" & Range("A2").Address(ReferenceStyle:=xlR1C1)

RoryA your coding might work as well, but I hadn't tried it since finding the above solution.

Thank you everyone for the help.

 

[RoryA]

If the sheet name contains spaces, or looks like a date, or any of the other reasons that it would be enclosed in quotes in a formula string applies, you need to use them there. No real need for the Range("A2").Address(ReferenceStyle:=xlR1C1) when you could just use:

StartPvt = "'" & ActiveSheet.Name & "'!R2C1!