Defining variable Source range for pivot chart code in VBA

1
votes

I have recorded a macro for pivot table and chart. Now I what to change the range as per the changes in the rows and columns of the source sheet.

The code from the recorded macro is:

SourceData:= _
        "DATA INPUT SHEET!R2C1:R23C12", Version:=xlPivotTableVersion15)

Any way out to change the Range R2C1:R23C12 to Range(Cells(2,1),Cells(FinalRow,8+NoBids)), where Finalrow is the last row of the source sheet and NoBids is to the no. of columns to be added after the 8th column of the source sheet.

1
vbaexcelpivot

1 Answers

1
votes

There is a way, try the code below:

SourceData:= "DATA INPUT SHEET!" & Range(Cells(2, 1), Cells(FinalRow, 8 + NoBids)).Address(True, True, xlR1C1)

If you want to learn more about the Range.Address Property, read HERE