I have created a macro to update a pivot table from an
excel list. Within the macro I have selected all the data
from the list using ctrl alt end which gives me

DATA!$A$1:$O$18498 within the pivot table wizard.

When I re-run the macro it does not bring the new data
past line 18498. Is there something I can put in place of
18498 so the pivot wizard always reads to the end of the
data. It is also doing the same when giving the range a
name and selecting the name.

Paul,

At the top of your code, put this:

Dim mySht As Worksheet
Set mySht = Worksheets("DATA")


Then, in the code, change the line with the SourceData:= to something along
the lines of

SourceData:= "'" & mysht.Name & "'!" &
mysht.Range("A1").CurrentRegion.Address(, , xlR1C1)

HTH,
Bernie
MS Excel MVP

"Paul" wrote in message
...
I have created a macro to update a pivot table from an
excel list. Within the macro I have selected all the data
from the list using ctrl alt end which gives me

DATA!$A$1:$O$18498 within the pivot table wizard.

When I re-run the macro it does not bring the new data
past line 18498. Is there something I can put in place of
18498 so the pivot wizard always reads to the end of the
data. It is also doing the same when giving the range a
name and selecting the name.

This'll work, too (if currentregion works):

...., sourcedata:=mysht.range("a1").currentregion.addres s(external:=true), ...

Bernie Deitrick wrote:

Paul,

At the top of your code, put this:

Dim mySht As Worksheet
Set mySht = Worksheets("DATA")

Then, in the code, change the line with the SourceData:= to something along
the lines of

SourceData:= "'" & mysht.Name & "'!" &
mysht.Range("A1").CurrentRegion.Address(, , xlR1C1)

HTH,
Bernie
MS Excel MVP

"Paul" wrote in message
...
I have created a macro to update a pivot table from an
excel list. Within the macro I have selected all the data
from the list using ctrl alt end which gives me

DATA!$A$1:$O$18498 within the pivot table wizard.

When I re-run the macro it does not bring the new data
past line 18498. Is there something I can put in place of
18498 so the pivot wizard always reads to the end of the
data. It is also doing the same when giving the range a
name and selecting the name.


--

Dave Peterson