VBA Pivot Table - Change Data Source

Hi,

This code has been produced by the Excel Macro Recorder. I'd like to change the R1C4:R6C5 (which is fixed) by some variable expression as the data to be shown in the Pivot Table varies depending on some logic (counters) in my macro. How can I make this field "variable"?


Sheets("Résultats").Select
ActiveSheet.PivotTables("Résultats").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"[TEST 2022-09 Outil de vote.xlsm]Votes!R1C4:R6C5" _
, Version:=8)

Regards.

Icare.

One way you can do it is to use a named range for the pivot table source data. Then you only need to redefine the named range.

Hi ByteMarks,

Thank you very much for your prompt answer. I will certainly try and do that.
Regards

Yves Carrier

Hi ByteMarks,

I'm sorry to bother you again. Following your advice, I have been trying unsuccessfully to the make the pivot table data source variable. The first example below is working fine but it has a fixed number of rows and colums as R1C4:R76C5. In this case, the first 3 lines are useless; I kept them there just to keep my program identical in both cases.

TotalDeVotes = 76
Set RangeForPivot = Workbooks("TEST 2022-09 Outil de vote.xlsm").Sheets("Votes").Range("D1")
Set RangeForPivot = RangeForPivot.Resize(TotalDeVotes, 2)
Workbooks("TEST 2022-09 Outil de vote.xlsm").Worksheets("Résultats").Activate
ActiveSheet.PivotTables("Résultats").ChangePivotCache ActiveWorkbook.PivotCaches _
.Create(SourceType:=xlDatabase, SourceData:="[TEST 2022-09 Outil de vote.xlsm]Votes!R1C4:R76C5", Version:=8)

The second example stops on a run time error "5".

TotalDeVotes = 76
Set RangeForPivot = Workbooks("TEST 2022-09 Outil de vote.xlsm").Sheets("Votes").Range("D1")
Set RangeForPivot = RangeForPivot.Resize(TotalDeVotes, 2)
Workbooks("TEST 2022-09 Outil de vote.xlsm").Worksheets("Résultats").Activate
ActiveSheet.PivotTables("Résultats").ChangePivotCache ActiveWorkbook.PivotCaches _
.Create(SourceType:=xlDatabase, SourceData:="[TEST 2022-09 Outil de vote.xlsm]Votes!RangeForPivot", Version:=8)

The only difference is that I have changed the R1C4:R76C5 by a Named Range that I resized based on TotalDeVotes. The syntax or the logic of it does not work.

If you have any other advice, I'll be glad to try it.

Regards.

Yves.

Hi ByteMarks,

No need to worry anymore. I have found the solution. I just had to replace:

SourceData:="[TEST 2022-09 Outil de vote.xlsm]Votes!RangeForPivot"

by

SourceData:=RangeForPivot

Thanks for your support.

Yves.