You don't need to delete the name Database. When you redefine it as below,
the new definition replaces the old.

Worksheets("sheet1").Range("A1").CurrentRegion.Nam e = "Database"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Database").CreatePivotTable TableDestination:="",
TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10


--
Regards,
Tom Ogilvy


"Don" wrote in message
...
I am generating a dynamic pivot table that is based on an Excel Worksheet.

I can do what I want manually, but what I need to do is to set a Named
Range field to the last poplulated cell on the worksheet, so that I can pass
a named range field to the pivot table wizard.


-- my progress so far ...
Of course, the following will position me to the last cell on the sheet

Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select

Next, I create a range called "Database" that defines the area of the
worksheet that I want to define...

ActiveWorkbook.Names("Database").Delete
ActiveWorkbook.Names.Add Name:="database", RefersToR1C1:= _
"=Sheet1!R1C1:last"

Then I pass this named range ("Database") to the pivot table wizard ...

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Database").CreatePivotTable TableDestination:="",
TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10

The problem I have is that "last" named range is static, and has been
created manually. If I try to record the macro, it creates a static range.
I need to dynamically assign the current cell (ie: the last cell on the
worksheet) and put it in the named range "last"

Alternately, I would like to dynamically define the name "Database" as the
area of the worksheet that is populated.


Thanks in advance,

Don