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