4 lines of recorded code can't be modified to dynamic code, why?

[VBAProIWish]

Hello All,

I recorded the code below and after literally 3 hours of trying to turn the recorded code into dynamic code, I finally gave up!
Can anyone get this recorded code to work dynamically by selecting the current region?
Note that below shows both recorded code and my best attempt at dynamic code...

'RECORDED CODE
ActiveSheet.ChartObjects("Chart 1").Activate

    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="MSDB!R1C1:R100C18", Version:=6)
'END RECORDED CODE

    
'MY BEST ATTEMPT AT DYNAMIC CODE
ActiveSheet.ChartObjects("Chart 1").Activate

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=Worksheets("MSDB").Range("A1").CurrentRegion.Address)
'END MY BEST ATTEMPT AT DYNAMIC CODE

 

[Misca]

Remove the ".Address" from your dynamic code and try again!

 

[VBAProIWish]

Hello Misca and thank you for the suggestion...
I removed the ".Address" and got the error below...

Run-time error '-2147024809 (80070057)
"The PivotTable field name is not valid. To create a PivotTable report, you must use..."

I then tried to change the name of "PivotTable2" to "PivotTable3" and got this error...

Run-time error '1004'
Unable to get the PivotTables property of the Worksheet class

Any suggestions?

 

[Misca]

If there's only one pivot table on your sheet you can always use the index instead of the pivot table name:

Activesheet.Pivottables(1)...

The index works with several pivot tables on the same sheet but it might take a couple of tries before you find the right number.

Also, if you had your data in an Excel table your data sources would be automatically dynamic. It would be enough to refresh them one by one or using the Refresh All -button found on the Data tab. Or - if you want to do it with VBA - with a single row of code using:

ThisWorkbook.RefreshAll

 

[VBAProIWish]

Hi,
I think that "PivotTable1" is correct because I tried "PivotTable2" and it doesn't work as I got the error below..

Run-time error '-2147024809 (80070057)
"The PivotTable field name is not valid. To create a PivotTable report, you must use..."

When I use "PivotTable1", I instead, get this error...

Run-time error '1004'
Unable to get the PivotTables property of the Worksheet class

Here is my entire routine...

Sub Change_Source_Current_Region()

Sheets("PGP 2020").Select

    
ActiveSheet.ChartObjects("Chart 1").Activate

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=Worksheets("MSDB").Range("A1").CurrentRegion)
    
End Sub

I am baffled as to why this doesn't work. Note that I did confirm that there is data in cell A1.

Any suggestions?

 

[VBAProIWish]

My goodness, I think I know why. I have data in column S with no header row!

What is the code for current region minus last column?

Uggg, sorry about that

 

[VBAProIWish]

Actually, I removed all the data from the last column with no header row and it STILL doesn't work....uggggg

I am really baffled by this

 

[VBAProIWish]

I'm not sure how, but I finally got it.

 

[smozgur]

@VBAProIWish - Glad to hear you got the solution.

Do you mind posting the final code? Then it is perfectly fine to mark your post as the solution to help future readers.

 

[Misca]

Make sure the sheet names & ranges match your data and try this:

Sub Change_Source_Current_Region()

Dim PT As PivotTable
Dim Rng As Range

Set Rng = Sheets("Sheet2").Range("B10").CurrentRegion    'Adjust to match your data

With Sheets("Sheet1")   'Adjust to match your data

    Set PT = .PivotTables(1) 'The first pivot table on the sheet

    With PT
        .ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=Rng)
    End With
    
End With

End Sub