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

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
980
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...


VBA 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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,714
Remove the ".Address" from your dynamic code and try again!
 

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
980
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

Well-known Member
Joined
Aug 12, 2009
Messages
1,714
If there's only one pivot table on your sheet you can always use the index instead of the pivot table name:

VBA Code:
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:
VBA Code:
ThisWorkbook.RefreshAll
 

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
980

ADVERTISEMENT

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...
VBA Code:
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

Well-known Member
Joined
Jul 6, 2009
Messages
980
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

Well-known Member
Joined
Jul 6, 2009
Messages
980

ADVERTISEMENT

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 :(
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,538
@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

Well-known Member
Joined
Aug 12, 2009
Messages
1,714
Make sure the sheet names & ranges match your data and try this:
VBA Code:
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
 

Forum statistics

Threads
1,141,132
Messages
5,704,458
Members
421,351
Latest member
Sheogorath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top