Trouble using macros to insert rows

[thegetch1]

Hi,

I have 3 buttons that insert new rows in 3 different sections of the
spreadsheet. Whenever I insert a row, the subsequent rows referenced in
the other macros are now off. For example, if I add 2 new rows to
sections 1 with button 1, buttons 2 and 3 will now add rows 2 rows
above where I want them. I tried coding a variable to track how many
times each button is pressed, but I don't know how to add that number
to a row. They are different types. I'm a novice with VB so that could
be problem. Any help you can give would be much appreciated.

THANKS!!

 

[Dave O]

Please post the code for each button, and tell us what range is section
1, 2, and 3. Also, you say "I tried coding a variable to track how
many times each button is pressed, but I don't know how to add that
number to a row." Did you try this to troubleshoot the Button 1-2-3
problem, or is this a different requirement?

 

[thegetch1]

Please post the code for each button, and tell us what range is section
1, 2, and 3. Also, you say "I tried coding a variable to track how
many times each button is pressed, but I don't know how to add that
number to a row." Did you try this to troubleshoot the Button 1-2-3
problem, or is this a different requirement?

Thanks for the response.

The code is very simple, just recorded it:

Sub addMilestone1()

Rows("20:20").Select
Selection.Insert Shift:=xlDown
Range("A20").Select
End Sub

Sub addMilestone2()

Rows("21:21").Select
Selection.Insert Shift:=xlDown
Range("A21").Select
End Sub

Sub addMilestone3()

Rows("22:22").Select
Selection.Insert Shift:=xlDown
Range("A22").Select
End Sub

As you can see the rows to select are hardcoded. Hence, when I use
addMilestone1, the other 2 milestone macros won't change the row they
should be inserted at.

For example: if I run addMilestone1 twice, it will add 2 rows at row
20. It will shift all other rows below it down by 2. So, when I go to
run the other macros, they insert 2 rows above where they should.

I tried to track the number of times a button was pressed so that I
could add that number to the row in each macro to offset for any rows
that have been inserted. Problem here is that I don't know how to add
anumber to Rows("20:20").Select. If I put the variable in place of the
20's, it says that it is a type mismatch which makes sense because it
is looking for a row and I am handing it a number.

Hope that answered your questions, I really appreciate any help you can
offer.

Getch

 

[thegetch1]

Please post the code for each button, and tell us what range is section
1, 2, and 3. Also, you say "I tried coding a variable to track how
many times each button is pressed, but I don't know how to add that
number to a row." Did you try this to troubleshoot the Button 1-2-3
problem, or is this a different requirement?

Here is a pic of exactly what it looks like:

http://dodownload.filefront.com/611...ee9a8258b8159a3cd3980de9b9748d15cfac07958d16d

 

[Dave O]

Here's one way to do it: create a named range for each of the three
sections: I've called them Section1, Section2, and Section3 for this
example. Each named range should be 1 row above the row each section
starts on. Then change your code to the code that follows. The named
ranges for sections 2 and 3 will move, and thereby they are dynamic,
when rows are inserted above them, which will relieve the need to keep
track of how many rows have been inserted. Note: if you use a
different named range naming format, you'll need to change the code to
match.

Let me know what you think- will this do it for you?


Sub addMilestone1()
Application.Goto Reference:="Section1"
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Range("a" & Selection.Row).Select
End Sub

Sub addMilestone2()
Application.Goto Reference:="Section2"
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Range("a" & Selection.Row).Select
End Sub

Sub addMilestone3()
Application.Goto Reference:="Section3"
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Range("a" & Selection.Row).Select
End Sub

 

[thegetch1]

THANK YOU!!!!!!!!!!!!!!!

I've worked with named ranges before. Genious to use them in this
manner, never would have thought of that. Works PERFECTLY! I don't know
how to thank you, but I appreciate this so much.

Take care!
Getch

 

[Dave O]

No worries, glad to help. The feedback is plenty of thanks!