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