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