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

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

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

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


.

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

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

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

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

"Tom Ogilvy" wrote:

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