I haven't tested this but declaring all variables, including using a sheet variable and then using them may help.
Sub Test()
Dim wS1 As Worksheet
Dim LastRow As Integer, AddRow As Integer
Set wS1 = Worksheets("Sheet1")
LastRow = wS1.Range("C" & wS1.Rows.Count).End(xlUp).Row
AddRow = LastRow + 3
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R2C2:R65000C34", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R30C3", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion12
End Sub
Notice the
which explicitly states that the cell is in Sheet 1. Again, I don't know exactly what you want to do but there are no more syntax errors with what I did.
Cheers!
Bookmarks