Dynamic range for Pivot-generating Macro
 

Hi

I've written a macro to save myself the effort of creating a pivot table of my employer's purchases and want to build it into a monthly template.

The range used in my test workbook is:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Amended Output Report!R1C1:R1440C14", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Pivot!R7C2", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14

However, I want to have the the final row of the selection as the penultimate used row on the 'Amended Output Report' sheet, so if there's 10 Rows, I want the last to be Row 9, and if 50 rows, it to be Row 49.

The columns will remain the same every month, and the destination for the Pivot won't change.

Please can anyone advise if it's possible to make the row selection dynamic - and, if so, how - or would I need to go in and amend the VB manually each month?

Any advice greatly appreciated!

Steve

Dynamic range for Pivot-generating Macro
 

Hi,

Am Fri, 22 May 2015 08:00:26 -0700 (PDT) schrieb
:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Amended Output Report!R1C1:R1440C14", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Pivot!R7C2", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14

give your range a dynamic name:
Name: Pivot_Data
refers to:
=OFFSET('Amended Output Report'!$A$1,,,COUNTA('Amended Output Report'!$A:$A),COUNTA('Amended Output Report'!$1:$1))

Then you can use that name as source for the Pivot table:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"=Pivot_Data",............


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dynamic range for Pivot-generating Macro
 

Hi Claus

That's excellent, thank you very much for that!

Steve