Pivot source data error

[krishna334]

Hi All,

Below is a part of my macro:

Sheets("SUMMARY").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PM Manual Override!R5C1:R514C61", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="SUMMARY BY PM BY HPL!R4C1", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion12


I want to change it as something like below:

Sheets("SUMMARY").Select

K = Sheets("Planner Manual Override").UsedRange.Rows.Count
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("PM Manual Override").Range("A5:BI" & K), Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="SUMMARY BY PM BY HPL!A4", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion12


What i want is to change the R1C1 type referencing of souce data to something with Range("A5:BI" & K), where K is the last used row of a sheet "Planner Manual Override". But the above modifed one shows error

 

[pbornemeier]

In the first codeblock replace:
"PM Manual Override!R5C1:R514C61",
with:
"PM Manual Override!" & Range("A5:BI" & K).Address(ReferenceStyle:=xlR1C1),
and leave:
SUMMARY BY PM BY HPL!A4
as it is (an RC reference not an A1 reference)

also .UsedRange can yield some bad (usually too large) values which would put rows of blanks in your pivot table.

If the data starts in A1, then either of these should work:
Range("A1").CurrentRegion.Rows.Count or
Range(Cells(Rows.Count,1).End(xlUp).Row (where 1 is the column being counted)

 

[krishna334]

Hi pbornemeier,

Sorry for the late response.
Your code worked. Thanks a lot.

what is and RC reference and A1 reference?

 

[pbornemeier]

Excel can reference a cell 2 different ways. The newer version (A1) uses a letter for the column and a number for the row. The original version (pionerred by VisiCalc long ago) uses RxCy to identify Row X and Column Y. So B5 in the new (and most commonly used) notation is the same as R5C3 in the old style.

Look up "Address" in Excel help for more info