Pivot Table & Dynamic data

[Guest]

I am importing data of variable length (rows) and never know the exact size.

How can I write a programme that in setting up the Pivot table identifies
the size of the data table in the same way that "currentregion" does for say
copy and paste.

I am told that dynamic range is the answer but dont know hoe to write syntax
to incorporate into the pivot element of the VBA programme.

Ta in advance for any help offerred

 

[Dave Peterson]

You could use .current region in your code, too.

dim myPTRng as range
with worksheets("Sheet1")
set myptrng = .range("a1").currentregion
end with

Or if you know the last column (say W) and can pick out a column that always has
an entry (I used column A in my example):

dim myPTRng as range
with worksheets("Sheet1")
set myptrng = .range("a1:W" & .cells(.rows.count,"A").end(xlup).row)
end with

then later...

,sourcedata:=myptrng.address(external:=true), ...

If the data can change after you create the pivottable, you may want to look at
using a dynamic range. You can see some instructions at Debra Dalgleish's site:

http://www.contextures.com/xlNames01.html#Dynamic