Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to change the macros as per active cell and range | Excel Discussion (Misc queries) | |||
use active cell to determine range | Excel Discussion (Misc queries) | |||
Clearly seeing active cell in a range | Excel Discussion (Misc queries) | |||
need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
Setting bkcolor on active cell when left mouse doubleclikked | Excel Programming |