I am trying too create a macro that will add rows that have a sequential
number assigned to them.

The finish product has an "add row" button that copies weeks and adds them
to the bottom of the sheet with an assigned number (i.e. Week 1, Week 2,
ect.).

I was able to add the rows with a macro that, copies and inserts row, but
cannot get the numbering to remain relative. (Even with relative reference
picked).

If anyone can help me out it would be much appreciated.

what are the appropriate lines in the macro you use?

"MechEng" wrote:

I am trying too create a macro that will add rows that have a sequential
number assigned to them.

The finish product has an "add row" button that copies weeks and adds them
to the bottom of the sheet with an assigned number (i.e. Week 1, Week 2,
ect.).

I was able to add the rows with a macro that, copies and inserts row, but
cannot get the numbering to remain relative. (Even with relative reference
picked).

If anyone can help me out it would be much appreciated.

I just recorded the macro by copiying the lines and inserted copied cells,
the following code was created:

Sub Add_Week()
ActiveCell.Offset(-11, 0).Rows("1:2").EntireRow.Select
Selection.Copy
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub


"bj" wrote:

what are the appropriate lines in the macro you use?

"MechEng" wrote:

I am trying too create a macro that will add rows that have a sequential
number assigned to them.

The finish product has an "add row" button that copies weeks and adds them
to the bottom of the sheet with an assigned number (i.e. Week 1, Week 2,
ect.).

I was able to add the rows with a macro that, copies and inserts row, but
cannot get the numbering to remain relative. (Even with relative reference
picked).

If anyone can help me out it would be much appreciated.

hi,
Your second post confused me. from your first post i assumed that you just
want to know how to add week 3 to a line below week 2. i copied your code and
ran it on my PC. makes no sense. I think you need to refine it some more.
but to answer your first question...i think.....
If your are sitting in a blank cell just below the cell that has Week 2 in
it, this code sniplet will put Week 3 in the blank activecell.

ActiveCell.Value = Mid(ActiveCell.Offset(-1, 0), 1, 4) _
& " " & Mid(ActiveCell.Offset(-1, 0), 5, 6) + 1

regards
FSt1

"MechEng" wrote:

I just recorded the macro by copiying the lines and inserted copied cells,
the following code was created:

Sub Add_Week()
ActiveCell.Offset(-11, 0).Rows("1:2").EntireRow.Select
Selection.Copy
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub


"bj" wrote:

what are the appropriate lines in the macro you use?

"MechEng" wrote:

I am trying too create a macro that will add rows that have a sequential
number assigned to them.

The finish product has an "add row" button that copies weeks and adds them
to the bottom of the sheet with an assigned number (i.e. Week 1, Week 2,
ect.).

I was able to add the rows with a macro that, copies and inserts row, but
cannot get the numbering to remain relative. (Even with relative reference
picked).

If anyone can help me out it would be much appreciated.

Hey FSt1, let me try to explain what I'm trying to do.

We have 4 rows of data grouped under a heading as week 1, 2, ect..
We want to add the next week (same four rows, new heading) at the bottom of
the page (next blank row).
The new week added could be blank or a copy of another, it doesn't matter.
Also we would prefer the user does not have to be in the active cell the
rows will be pasted into.
Also we will want to be able to remove the last week as well, as a seperate
macro.
We will use "Add_WeeK" and "Remove_Week" bottons to assign the macros too.

Hope this explains what I'm trying to achieve and that you can provide some
direction. Thanks in advance.

"FSt1" wrote:

hi,
Your second post confused me. from your first post i assumed that you just
want to know how to add week 3 to a line below week 2. i copied your code and
ran it on my PC. makes no sense. I think you need to refine it some more.
but to answer your first question...i think.....
If your are sitting in a blank cell just below the cell that has Week 2 in
it, this code sniplet will put Week 3 in the blank activecell.

ActiveCell.Value = Mid(ActiveCell.Offset(-1, 0), 1, 4) _
& " " & Mid(ActiveCell.Offset(-1, 0), 5, 6) + 1

regards
FSt1

"MechEng" wrote:

I just recorded the macro by copiying the lines and inserted copied cells,
the following code was created:

Sub Add_Week()
ActiveCell.Offset(-11, 0).Rows("1:2").EntireRow.Select
Selection.Copy
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub


"bj" wrote:

what are the appropriate lines in the macro you use?

"MechEng" wrote:

I am trying too create a macro that will add rows that have a sequential
number assigned to them.

The finish product has an "add row" button that copies weeks and adds them
to the bottom of the sheet with an assigned number (i.e. Week 1, Week 2,
ect.).

I was able to add the rows with a macro that, copies and inserts row, but
cannot get the numbering to remain relative. (Even with relative reference
picked).

If anyone can help me out it would be much appreciated.