Problem using GO TO function in a macro on a large workbook.

[BP]

Using Excel 97 on Windows XP. (This workbook was originally created in Excel
4.0.)
I use a large excel workbook to do construction estimating.
The WB consists of a main sheet that contains BUTTONS that were created and
assigned to macros on a master macro sheet in the WB. The buttons take you
to the specific section of another worksheet that contains the cells that
return the total on the row corresponding to the button. Those sections are
NAMED RANGES.

The macro consists simply of:
=FORMULA.GOTO(Sheet & Named Range)
=SELECT ("RC3")

The NAMED RANGES are created by going INSERT > NAME > DEFINE , and then
selecting a group of cells that fill the screen.
The SELECT command places you on the first cell that requires data entry in
the section.

Now, when the workbook is first set up all works great. Press the button and
you go to the correct section front and center and you're in the cell to
start work. But a problem arises because it is inherently necessary to add
rows to each new sheet due to the unique conditions found on varying
projects. When rows are added, pushing the original rows down, the relative
position of the named range does not move with it. Excel keeps the range in
the particular area on the worksheet, not on the group of cells selected, so
when you use the button after adding rows the section you are going to is
far down on the screen or off the screen in some instances.

I know there must be a way to do what I want, I'm just not smart enough to
figure it out. I've tried "Locking" the cells using the $ symbol in the
named range but that didn't work. Any help?

[Gord Dibben]

BP

Check out Debra Dalgleish's site for tips on how to create a "dynamic" range
that expands to cover added rows.

http://www.contextures.on.ca/xlNames01.html#Dynamic



Gord Dibben Excel MVP

On Fri, 18 Feb 2005 09:38:50 -0500, "BP" <[email protected]> wrote:

>Using Excel 97 on Windows XP. (This workbook was originally created in Excel
>4.0.)
>I use a large excel workbook to do construction estimating.
>The WB consists of a main sheet that contains BUTTONS that were created and
>assigned to macros on a master macro sheet in the WB. The buttons take you
>to the specific section of another worksheet that contains the cells that
>return the total on the row corresponding to the button. Those sections are
>NAMED RANGES.
>
>The macro consists simply of:
>=FORMULA.GOTO(Sheet & Named Range)
>=SELECT ("RC3")
>
>The NAMED RANGES are created by going INSERT > NAME > DEFINE , and then
>selecting a group of cells that fill the screen.
>The SELECT command places you on the first cell that requires data entry in
>the section.
>
>Now, when the workbook is first set up all works great. Press the button and
>you go to the correct section front and center and you're in the cell to
>start work. But a problem arises because it is inherently necessary to add
>rows to each new sheet due to the unique conditions found on varying
>projects. When rows are added, pushing the original rows down, the relative
>position of the named range does not move with it. Excel keeps the range in
>the particular area on the worksheet, not on the group of cells selected, so
>when you use the button after adding rows the section you are going to is
>far down on the screen or off the screen in some instances.
>
>I know there must be a way to do what I want, I'm just not smart enough to
>figure it out. I've tried "Locking" the cells using the $ symbol in the
>named range but that didn't work. Any help?
>

[BP]

That looks promising. I'll give it a go and post back if it works. Thanks.

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> BP
>
> Check out Debra Dalgleish's site for tips on how to create a "dynamic"
> range
> that expands to cover added rows.
>
> http://www.contextures.on.ca/xlNames01.html#Dynamic
>
>
>
> Gord Dibben Excel MVP
>
> On Fri, 18 Feb 2005 09:38:50 -0500, "BP" <[email protected]> wrote:
>
>>Using Excel 97 on Windows XP. (This workbook was originally created in
>>Excel
>>4.0.)
>>I use a large excel workbook to do construction estimating.
>>The WB consists of a main sheet that contains BUTTONS that were created
>>and
>>assigned to macros on a master macro sheet in the WB. The buttons take you
>>to the specific section of another worksheet that contains the cells that
>>return the total on the row corresponding to the button. Those sections
>>are
>>NAMED RANGES.
>>
>>The macro consists simply of:
>>=FORMULA.GOTO(Sheet & Named Range)
>>=SELECT ("RC3")
>>
>>The NAMED RANGES are created by going INSERT > NAME > DEFINE , and then
>>selecting a group of cells that fill the screen.
>>The SELECT command places you on the first cell that requires data entry
>>in
>>the section.
>>
>>Now, when the workbook is first set up all works great. Press the button
>>and
>>you go to the correct section front and center and you're in the cell to
>>start work. But a problem arises because it is inherently necessary to add
>>rows to each new sheet due to the unique conditions found on varying
>>projects. When rows are added, pushing the original rows down, the
>>relative
>>position of the named range does not move with it. Excel keeps the range
>>in
>>the particular area on the worksheet, not on the group of cells selected,
>>so
>>when you use the button after adding rows the section you are going to is
>>far down on the screen or off the screen in some instances.
>>
>>I know there must be a way to do what I want, I'm just not smart enough to
>>figure it out. I've tried "Locking" the cells using the $ symbol in the
>>named range but that didn't work. Any help?
>>
>

[BP]

No, unfortunately that was not the fix I needed. That allows me to expand
the number of rows within the range, but does not fix the range in the
sheet.
Maybe I need to try another approach?

"BP" <[email protected]> wrote in message
news:[email protected]...
> That looks promising. I'll give it a go and post back if it works. Thanks.
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:[email protected]...
>> BP
>>
>> Check out Debra Dalgleish's site for tips on how to create a "dynamic"
>> range
>> that expands to cover added rows.
>>
>> http://www.contextures.on.ca/xlNames01.html#Dynamic
>>
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Fri, 18 Feb 2005 09:38:50 -0500, "BP" <[email protected]> wrote:
>>
>>>Using Excel 97 on Windows XP. (This workbook was originally created in
>>>Excel
>>>4.0.)
>>>I use a large excel workbook to do construction estimating.
>>>The WB consists of a main sheet that contains BUTTONS that were created
>>>and
>>>assigned to macros on a master macro sheet in the WB. The buttons take
>>>you
>>>to the specific section of another worksheet that contains the cells that
>>>return the total on the row corresponding to the button. Those sections
>>>are
>>>NAMED RANGES.
>>>
>>>The macro consists simply of:
>>>=FORMULA.GOTO(Sheet & Named Range)
>>>=SELECT ("RC3")
>>>
>>>The NAMED RANGES are created by going INSERT > NAME > DEFINE , and then
>>>selecting a group of cells that fill the screen.
>>>The SELECT command places you on the first cell that requires data entry
>>>in
>>>the section.
>>>
>>>Now, when the workbook is first set up all works great. Press the button
>>>and
>>>you go to the correct section front and center and you're in the cell to
>>>start work. But a problem arises because it is inherently necessary to
>>>add
>>>rows to each new sheet due to the unique conditions found on varying
>>>projects. When rows are added, pushing the original rows down, the
>>>relative
>>>position of the named range does not move with it. Excel keeps the range
>>>in
>>>the particular area on the worksheet, not on the group of cells selected,
>>>so
>>>when you use the button after adding rows the section you are going to is
>>>far down on the screen or off the screen in some instances.
>>>
>>>I know there must be a way to do what I want, I'm just not smart enough
>>>to
>>>figure it out. I've tried "Locking" the cells using the $ symbol in the
>>>named range but that didn't work. Any help?
>>>
>>
>
>