Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a named range for a dynamic result set
In a workbook template that will be run for each months production numbers,
several hidden worksheets are updated with data from MS query from an Access db. The first column of each contains a value used to discriminate between new or renewal business, while the second column is used by worksheets the user can see to automatically display data using VLOOKUP functions. I have manually defined the range of data for new and renewal, but would like to create VBA code that will do it automatically when the template is opened each month. I am having trouble redefining the range once I know its dimensions. When I record the range define steps in a macro, the following code is generated: Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select ActiveWorkbook.Names.Add Name:="rngPrgmYTD", RefersToR1C1:= _ "='LU-PRGMYTD'!R1C1:R14C8" How would I replace the 14 and 8 in the R14C8 reference with variables? Also, assuming the name already exists, what would the statement syntax be to resize the already-defined named range? Thanks |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking up a returned value defining a named range | Excel Discussion (Misc queries) | |||
Defining a custom-named Range across several worksheets | Excel Worksheet Functions | |||
help defining dynamic range | Charts and Charting in Excel | |||
Defining a Dynamic Range using a variable | Excel Programming | |||
Defining Dynamic Range | Excel Programming |