Runtime error 1004 while compile vba with PivotTable

[Stephen Yip]

I am now encounter a "simple" problem but no clue on it.

I get the following code for creating Pivot table from a website(stated in the comment part)

Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: Squarespace - Claim This Domain

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

Range("A5").Select

'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & ActiveSheet.Range("A5:R100").Address(ReferenceStyle:=xlR1C1)

'Create a new worksheet
Set sht = Sheets.Add

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

'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")

End Sub





It runs everything fine however, I want to change the source data into a more dynamic way and make the following changes

Range(Range("A5"),Range("A5").SpecialCells(xlLastCell)) instaed of Range("A5:R100")

Runtime error 1004: Application-defined or object-defined error comes out at the code yellow in color

I googled the error but seems cant solve my problem.

What will be the possibility?

Looking for your advices.

Thank you