Change Data source of Pivot Table from VBA

[ItalianPlatinum]

I am looking to change the data source of my VBA. But getting syntax error when doing the below. What am I doing wrong? I can't even get it to work for a simple A2 - V100. Eventually I want the 100 to be set by my lr.

My data will always start on A2 to V but the amount of data could change that is why I am trying to use LR.

lr = Cells(rows.count, "B").End(xlUp).row

Sheets("Pivot Table").PivotTables("PivotTable1").ChangePivotCache Sheets("Pivot Table").
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Transaction!$A$2:$V$100", Version:=8)

 

[RoryA]

Pivotcaches is a workbook property, not a worksheet one:

Sheets("Pivot Table").PivotTables("PivotTable1").ChangePivotCache _ 
        activeworkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Transaction!$A$2:$V$100", Version:=8)

 

[ItalianPlatinum]

getting an error just not sure why. I copied what you had and it looks fine to me

 

[ItalianPlatinum]

now its a runtime 1004 if i change it to ActiveWorkbook from Sheets("Pivot Table")

Sheets("Pivot Table").Activate
ActiveWorkbook.PivotTables("PivotTable1").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Transaction!$A$2:$V$100", Version:=8)

 

[ItalianPlatinum]

I just wanted to say i got it to work. researched a bit online to help. took a lot of trial and error but got there see below if anyone needs it for any future dilemmas.

Dim lr As Long
Dim SrcData As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ActiveSheet.AutoFilterMode = False

If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("10:10").AutoFilter

With ActiveWindow
    .ScrollRow = 1 'the row you want to scroll to
    .ScrollColumn = 1 'the column you want to scroll to
    .SplitColumn = 9
    .SplitRow = 10
    .FreezePanes = True
End With

lr = Cells(rows.count, "B").End(xlUp).row

SrcData = "'" & ActiveSheet.Name & "'" & "!" & Range("$A$10:$V$" & lr).Address(ReferenceStyle:=xlA1)

Sheets("Pivot Table").Activate
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, Version:=8)