"R1C1:last row of the document" (no matter if the rows change, the macro should still work?)

[Fra88]

Hi All

I have a macro creating a Pivot from a database (Hello)
The macro is working.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Hello!R1C1:R278C52", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15

My problem is that the database "Hello" is created each week, and it has different data inside.

This macro was created by recording, and was set with these data : R1C1:R278C52
Therefore, the macro doesn't work if the workbook has different rows..

Instead of manually going and changing data, is there a way to substitute "R1C1:R278C52" with "R1C1:whatever the last row of this document is"?

Thank you !!

Fra

 

[Scott T]

You can use a variableto hold the last row

LR = ThisWorkbook.Sheets(WS).Cells(Rows.Count, 1).End(xlUp).Row

You can then use the variable when referring to ranges

 

[RoryA]

I would use:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Hello!" & sheets("Hello").Range("A1").Currentregion.address(referencestyle:=xlR1C1), Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15

 

[Fra88]

Thank you both !

I tried your solution RoryA, but it doesn't work
May Excel 2013 affect it?

About the first one, Scott could you help me implementing it?

I inserted

Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Hello!R1C1:R" & LR & "C23", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15


'Worksheets(Worksheets.Count).Select
'Worksheets(Worksheets.Count).Activate
Sheets("Sheet2").Select
Cells(3, 1).Select

Sheets("DataToComment").Activate

ActiveSheet.PivotTables("PivotTable1").PivotFields("COUNTRY").Orientation = xlRowField
ActiveSheet.PivotTables("PivotTable1").PivotFields("COUNTRY").Position = 1



And it stucks at this line: ActiveSheet.PivotTables("PivotTable1").PivotFields("COUNTRY").Orientation = xlRowField
But probably I am doing it wrong

Thanks

 

[Fra88]

Sorry Rory, I had put an extra space

It works :D!

 

[RoryA]

No problem - glad to help. That version will also work if you happen to add new columns.

 

[Scott T]

I have not used R1C1 notation so I am not sure but maybe?

[COLOR=#008000]"Hello!R1C1:R"& LR &"C52",[/COLOR]