Setting a range value to the last cell in active worksheet.

[Guest]

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:=xlDatabase, 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

 

[Guest]

Try this after Selection.end(xldown).select

a=activecell.address
Names.Add Name:="Last", RefersTo:="=(yoursheetname)!" & a

Best of luck
DavidC

-----Original 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:=xlDatabase, 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.

 

[Tom Ogilvy]

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.Name = "Database"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Database").CreatePivotTable TableDestination:="",
TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10


--
Regards,
Tom Ogilvy

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:=xlDatabase, 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.

 

[Guest]

Thank you very much Tom, this did exactly what I was looking for ...

It's nice to know that in VB, someone has already done it before.

Take Care

Don