Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table dynamic range code needed
Hi All......
If someone would be so kind, I need help with the following Pivot Table code, written in Excel 2000. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "All12Working!R11C1:R5362C19").CreatePivotTabl e TableDestination:="", _ TableName:="PivotTable1" It works fine, as it is written, but the next time I run it on a new "All12Working" sheet, that sheet will be of a different amount of rows, (the R5362 figure). If I make that number smaller than the number of row, it runs fine but cuts off at that row limit......if I make the number larger than the number of rows, crash city. It only runs correctly if that number exactly corresponds with the actual number of rows in the sheet. I would appreciate if anyone could give me code to replace the above, that will automatically size the range from All12Working!R11C1:R5362C19. to All12Working!R11C1:RwhateverC19. TIA for any assistance. Vaya con Dios, Chuck, CABGx3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table dynamic range code needed
Dim myRng as range
with worksheets("All12Working") 'I used column A to find the last row with data set myrng = .range("A11:S" & .cells(.rows.count,"A").end(xlup).row) end with then in the pivotcaches.add line: ..., sourcedata:=myrng, ... An alternative: I think it would be easier to create a dynamic name that adjusted when the data changed--then you could just refresh the pivottable(s). Debra Dalgleish explains dynamic range names he http://contextures.com/xlNames01.html#Dynamic CLR wrote: Hi All...... If someone would be so kind, I need help with the following Pivot Table code, written in Excel 2000. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "All12Working!R11C1:R5362C19").CreatePivotTabl e TableDestination:="", _ TableName:="PivotTable1" It works fine, as it is written, but the next time I run it on a new "All12Working" sheet, that sheet will be of a different amount of rows, (the R5362 figure). If I make that number smaller than the number of row, it runs fine but cuts off at that row limit......if I make the number larger than the number of rows, crash city. It only runs correctly if that number exactly corresponds with the actual number of rows in the sheet. I would appreciate if anyone could give me code to replace the above, that will automatically size the range from All12Working!R11C1:R5362C19. to All12Working!R11C1:RwhateverC19. TIA for any assistance. Vaya con Dios, Chuck, CABGx3 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table dynamic range code needed
there are sevaral ways to do this.
Here's one: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Worksheets("All12Working").Range("D7").CurrentRegi on ).CreatePivotTable TableDestination:="", TableName:="PivotTable1" "CLR" wrote: Hi All...... If someone would be so kind, I need help with the following Pivot Table code, written in Excel 2000. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "All12Working!R11C1:R5362C19").CreatePivotTabl e TableDestination:="", _ TableName:="PivotTable1" It works fine, as it is written, but the next time I run it on a new "All12Working" sheet, that sheet will be of a different amount of rows, (the R5362 figure). If I make that number smaller than the number of row, it runs fine but cuts off at that row limit......if I make the number larger than the number of rows, crash city. It only runs correctly if that number exactly corresponds with the actual number of rows in the sheet. I would appreciate if anyone could give me code to replace the above, that will automatically size the range from All12Working!R11C1:R5362C19. to All12Working!R11C1:RwhateverC19. TIA for any assistance. Vaya con Dios, Chuck, CABGx3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table dynamic range code needed
Thanks Patrick.........
I did manage to get it working a different way, but your way seems easier......so will give it a try when time permits........ Many thanks again, Vaya con Dios, Chuck, CABGx3 "Patrick Molloy" wrote in message ... there are sevaral ways to do this. Here's one: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Worksheets("All12Working").Range("D7").CurrentRegi on ).CreatePivotTable TableDestination:="", TableName:="PivotTable1" "CLR" wrote: Hi All...... If someone would be so kind, I need help with the following Pivot Table code, written in Excel 2000. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "All12Working!R11C1:R5362C19").CreatePivotTabl e TableDestination:="", _ TableName:="PivotTable1" It works fine, as it is written, but the next time I run it on a new "All12Working" sheet, that sheet will be of a different amount of rows, (the R5362 figure). If I make that number smaller than the number of row, it runs fine but cuts off at that row limit......if I make the number larger than the number of rows, crash city. It only runs correctly if that number exactly corresponds with the actual number of rows in the sheet. I would appreciate if anyone could give me code to replace the above, that will automatically size the range from All12Working!R11C1:R5362C19. to All12Working!R11C1:RwhateverC19. TIA for any assistance. Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for a Macro that allows a Pivot table to be repeatedly run (fora dynamic rows in a table, but static field names) | Excel Programming | |||
Dynamic Pivot table range | Excel Programming | |||
VBA code needed to apply formula to each value in a dynamic range! | Excel Programming | |||
Dynamic Range in a Pivot Table | Excel Discussion (Misc queries) | |||
Dynamic Range for Pivot Table | Excel Programming |