Macro Help - Indefinite Rows

I have a macro set up on the following file:
www.oksana-design.com/Spreadsheet.xls

If you scroll to W6, you will see the list of dates, and their respective numbers underneath.
I have set up a macro so that when you push "Week Update," the dates move over to the left along with their respective numbers underneath.
The macro works fine on this file. However, if I add any more rows (right now the last row is 175) the macro stops working. It stops working because of the fact that it only knows to change up to row 175.
How can I set up the macro so that it always conforms to the number of rows in my table, and I don't have to manually change it everytime I add a row?

Thank you.

Create a named range for your selected area X9:AF174. To do this, highlight
X9:AF174. Then go to Insert-Name-Define. Give it a name. For my example,
I used "Test". Once this is created, the named range should automatically
adjust if rows are inserted before the last row of the range (row 174).
Once it's added, the named range is adjusted to row 175 and so on. To
reference the named range in your macro, see below.

Range("Test").Select
Selection.Copy
Range("W9").Select
ActiveSheet.Paste
Range("Test").Select
Selection.ClearContents
Range("W4").Select
With Worksheets("Asphalt").Range("w7")
.Value = .Value + 7
End With


HTH,
Paul


Range("Test").Select
"piano.lisa" <[email protected]> wrote
in message news:[email protected]...
>
> I have a macro set up on the following file:
> www.oksana-design.com/Spreadsheet.xls
>
> If you scroll to W6, you will see the list of dates, and their
> respective numbers underneath.
> I have set up a macro so that when you push "Week Update," the dates
> move over to the left along with their respective numbers underneath.
> The macro works fine on this file. However, if I add any more rows
> (right now the last row is 175) the macro stops working. It stops
> working because of the fact that it only knows to change up to row 175.
>
> How can I set up the macro so that it always conforms to the number of
> rows in my table, and I don't have to manually change it everytime I
> add a row?
>
> Thank you.
>
>
> --
> piano.lisa
> ------------------------------------------------------------------------
> piano.lisa's Profile:
> http://www.excelforum.com/member.php...o&userid=37601
> View this thread: http://www.excelforum.com/showthread...hreadid=572222
>

Thank you. For anyone else using this method, please note that the second "Range("Test").Select" should not use "Test" but another named range.


Anyways, works great! Thank you.

Originally Posted by PCLIVE

Create a named range for your selected area X9:AF174. To do this, highlight
X9:AF174. Then go to Insert-Name-Define. Give it a name. For my example,
I used "Test". Once this is created, the named range should automatically
adjust if rows are inserted before the last row of the range (row 174).
Once it's added, the named range is adjusted to row 175 and so on. To
reference the named range in your macro, see below.

Range("Test").Select
Selection.Copy
Range("W9").Select
ActiveSheet.Paste
Range("Test").Select
Selection.ClearContents
Range("W4").Select
With Worksheets("Asphalt").Range("w7")
.Value = .Value + 7
End With


HTH,
Paul


Range("Test").Select
"piano.lisa" <[email protected]> wrote
in message news:[email protected]...
>
> I have a macro set up on the following file:
> www.oksana-design.com/Spreadsheet.xls
>
> If you scroll to W6, you will see the list of dates, and their
> respective numbers underneath.
> I have set up a macro so that when you push "Week Update," the dates
> move over to the left along with their respective numbers underneath.
> The macro works fine on this file. However, if I add any more rows
> (right now the last row is 175) the macro stops working. It stops
> working because of the fact that it only knows to change up to row 175.
>
> How can I set up the macro so that it always conforms to the number of
> rows in my table, and I don't have to manually change it everytime I
> add a row?
>
> Thank you.
>
>
> --
> piano.lisa
> ------------------------------------------------------------------------
> piano.lisa's Profile:
> http://www.excelforum.com/member.php...o&userid=37601
> View this thread: http://www.excelforum.com/showthread...hreadid=572222
>

Oops! You are correct. It should've been something like:

Range("Test").Select
Selection.Copy
Range("W9").Select
ActiveSheet.Paste
Range("AF9:AF" & Range("Test").Rows.Count + 8).Select
Selection.ClearContents
Range("W4").Select
With Worksheets("Asphalt").Range("w7").Value =
..Value + 7
End With


Range("Test").Rows.Count + 8

"aposatsk" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you. For anyone else using this method, please note that the
> second "Range("Test").Select" should not use "Test" but another named
> range.
>
>
> Anyways, works great! Thank you.
>
> PCLIVE Wrote:
>> Create a named range for your selected area X9:AF174. To do this,
>> highlight
>> X9:AF174. Then go to Insert-Name-Define. Give it a name. For my
>> example,
>> I used "Test". Once this is created, the named range should
>> automatically
>> adjust if rows are inserted before the last row of the range (row
>> 174).
>> Once it's added, the named range is adjusted to row 175 and so on. To
>> reference the named range in your macro, see below.
>>
>> Range("Test").Select
>> Selection.Copy
>> Range("W9").Select
>> ActiveSheet.Paste
>> Range("Test").Select
>> Selection.ClearContents
>> Range("W4").Select
>> With Worksheets("Asphalt").Range("w7")
>> .Value = .Value + 7
>> End With
>>
>>
>> HTH,
>> Paul
>>
>>
>> Range("Test").Select
>> "piano.lisa" <[email protected]>
>> wrote
>> in message
>> news:[email protected]...
>> >
>> > I have a macro set up on the following file:
>> > www.oksana-design.com/Spreadsheet.xls
>> >
>> > If you scroll to W6, you will see the list of dates, and their
>> > respective numbers underneath.
>> > I have set up a macro so that when you push "Week Update," the dates
>> > move over to the left along with their respective numbers
>> underneath.
>> > The macro works fine on this file. However, if I add any more rows
>> > (right now the last row is 175) the macro stops working. It stops
>> > working because of the fact that it only knows to change up to row
>> 175.
>> >
>> > How can I set up the macro so that it always conforms to the number
>> of
>> > rows in my table, and I don't have to manually change it everytime I
>> > add a row?
>> >
>> > Thank you.
>> >
>> >
>> > --
>> > piano.lisa
>> >
>> ------------------------------------------------------------------------
>> > piano.lisa's Profile:
>> > http://www.excelforum.com/member.php...o&userid=37601
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=572222
>> >
>
>
> --
> aposatsk
> ------------------------------------------------------------------------
> aposatsk's Profile:
> http://www.excelforum.com/member.php...o&userid=36709
> View this thread: http://www.excelforum.com/showthread...hreadid=572222
>