Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic range for Pivot-generating Macro
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic range for Pivot-generating Macro
Hi Claus
That's excellent, thank you very much for that! Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Dynamic range in Pivot table | Excel Programming | |||
DYNAMIC RANGE FOR PIVOT TABLE | Excel Programming | |||
need help in my code on generating chart with dynamic range | Excel Programming | |||
Dynamic Pivot table range | Excel Programming | |||
Dynamic Range for Pivot Table | Excel Programming |