Error in Creating a Pivot table

Question

I've been debugging this code for hours in creating a pivot table, but unfortunately, I'm not able to figure out the problem. It keeps on displaying, Object doesn't support this property or method.

Sub CreatePivotTable()

Dim sht, srcSheet As Worksheet, ccsheet As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim lrow As Long
Set srcSheet = ThisWorkbook.Sheets("Document Raw")
lrow = srcSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set ccsheet = ThisWorkbook.Sheets("Character Count")

SrcData = srcSheet & "!" & Range("A1:V"& lrow).Address(ReferenceStyle:=xlR1C1)' this is the line that errors

StartPvt = ccsheet & "!" & ccsheet.Range("A79").Address(ReferenceStyle:=xlR1C1)

Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

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

End Sub

Any help? Thanks

Answer 1

Try the modified code below (i prefer to setup up the Range, later the PivotCache and PivotTable and this method):

Sub CreatePivotTable()

Dim srcSheet                As Worksheet
Dim ccSheet                 As Worksheet
Dim pvtCache                As PivotCache
Dim pvtTbl                  As PivotTable
Dim StartPvt                As Range
Dim SrcData                 As Variant
Dim lrow                    As Long

Set srcSheet = ThisWorkbook.Sheets("Document Raw")
lrow = srcSheet.Cells(srcSheet.Rows.Count, 1).End(xlUp).Row
Set ccSheet = ThisWorkbook.Sheets("Character Count")

' make sure you have valid data from Column A to Column V
Set SrcData = srcSheet.Range("A1:V" & lrow)
Set StartPvt = ccSheet.Range("A79")

'Create Pivot table from Pivot Cache
Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, SrcData)


' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set pvtTbl = ccSheet.PivotTables("PivotTable1") ' check if "PivotTable1" Pivot Table already created (in past runs of this Macro)

On Error GoTo 0
If pvtTbl Is Nothing Then
    Set pvtTbl = ccSheet.PivotTables.Add(PivotCache:=pvtCache, TableDestination:=StartPvt, TableName:="PivotTable1")
Else
    pvtTbl.ChangePivotCache pvtCache
    pvtTbl.RefreshTable
End If

End Sub