Pivot Table dynamic range code needed

[CLR]

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:=xlDatabase, SourceData:= _
"All12Working!R11C1:R5362C19").CreatePivotTable
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]

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 here:
http://contextures.com/xlNames01.html#Dynamic

 

[Patrick Molloy]

there are sevaral ways to do this.
Here's one:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Worksheets("All12Working").Range("D7").CurrentRegion
).CreatePivotTable TableDestination:="", TableName:="PivotTable1"

 

[clr]

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