Multiple Macros to insert new rows

_pipo_

New Member
Joined
Jun 10, 2010
Messages
7
Hi everyone,

Wondered if anyone could be of assistance. I am using the code below to insert a new row below the one in which the button is located (i.e. A16).

Code:
Private Sub CommandButton1_Click()

Sheets("Sheet1").Range("A17").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("Sheet1").Range("A17:J17").Select
Selection.Borders.Weight = xlThin

VerticalAlignment = xlCenter

End Sub

So far, so good. The kicker is that what was previously in A17 that has now been pushed down also has the same macro but when this is clicked it is still inserting rows at A17 (but now I want it to insert rows underneath it, say at A20). How can I make it so that the range selected is not fixed, but moving relative to where the macro is located/relocated?

Hope that made sense. Thanking all in advance.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi everyone,

Wondered if anyone could be of assistance. I am using the code below to insert a new row below the one in which the button is located (i.e. A16).

Code:
Private Sub CommandButton1_Click()

Sheets("Sheet1").Range("A17").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("Sheet1").Range("A17:J17").Select
Selection.Borders.Weight = xlThin

VerticalAlignment = xlCenter

End Sub

So far, so good. The kicker is that what was previously in A17 that has now been pushed down also has the same macro but when this is clicked it is still inserting rows at A17 (but now I want it to insert rows underneath it, say at A20). How can I make it so that the range selected is not fixed, but moving relative to where the macro is located/relocated?

Hope that made sense. Thanking all in advance.
Hi _pipo_,

I think the easiest way to handle this issue would be to create a couple of named ranges directly underneath where each of the buttons are. For the sake of example lets call cell A16 "Button1". To do this, select A16, then in the "Name" box to the left of the formula bar just type in Button1 and press enter. Next go down to A19 where I believe the other button is, then in the "Name" box type in Button2 and press enter.

When using named ranges you can insert or delete rows and even though the named cells are moving, they retain their names. This means your first macro to insert a row could be this:

Code:
Sub TEST1()
' Selects the row directly below Button1
Sheets("Sheet1").Range("Button1").Offset(1, 0).Select
' Inserts a new row
ActiveCell.EntireRow.Insert Shift:=xlDown
End Sub

The second macro only needs the number after the word button to be changed.

Code:
Sub TEST2()
' Selects the row directly below Button2
Sheets("Sheet1").Range("Button2").Offset(1, 0).Select
' Inserts a new row
ActiveCell.EntireRow.Insert Shift:=xlDown
End Sub

That should prevent the issue you were having about buttons moving and the inserted rows not being where you wanted them.
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,628
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top