Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range setting for pivot table
Just want to know the way to set the range property for creating pivot table
provide that the number of row will change by each time. Eg. The following line R137, today may be 137 total number of row & may be 237 for tomorrow report. If I just amend the range from R137 to R10000 (aim to cover all data), error will appear when running the macro if the number of row changed by next time. Selection.CurrentRegion.Select ActiveWorkbook.Names.Add Name:="rangeT", RefersToR1C1:="=aging_data!R11C1:R137C16" -- aw |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range setting for pivot table
Can you pick out a column that always has data in it if that row is used?
If yes, you could do something in code, but I think it would be easier to create a dynamic name that adjusted when the data changed--then you could just refresh the pivottable instead of rebuilding it. Debra Dalgleish explains dynamic range names he http://contextures.com/xlNames01.html#Dynamic Insert|Name|define Names in Workbook: RangeT Refers to: =OFFSET('aging_data'!$A$1,0,0,COUNTA('aging_data'! $A:$A),16) This expects that column A always has something in it if that row is used. If you don't have a column that can be used to determine the last row, then this formula won't work. If you really wanted this in code, record one while you create the name and you'll be set. aw wrote: Just want to know the way to set the range property for creating pivot table provide that the number of row will change by each time. Eg. The following line R137, today may be 137 total number of row & may be 237 for tomorrow report. If I just amend the range from R137 to R10000 (aim to cover all data), error will appear when running the macro if the number of row changed by next time. Selection.CurrentRegion.Select ActiveWorkbook.Names.Add Name:="rangeT", RefersToR1C1:="=aging_data!R11C1:R137C16" -- aw -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting range criteria in pivot table | Excel Discussion (Misc queries) | |||
setting up a pivot table via macro? | Excel Discussion (Misc queries) | |||
Changing a field setting in a pivot table | Excel Discussion (Misc queries) | |||
Setting Range in Pivot Table | Charts and Charting in Excel | |||
Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) |