Copy row and Insert above

rob-strong

New Member
Joined
Apr 3, 2004
Messages
14
I know this subject has been beaten to death. My code works for inserting rows however it's not reliable and inserts where ever an active cell is. This is what I need.

I have a list of names in Col A. These names are seperated by their respective department headers also in Col A. Rows with (Add Member) contain all the formulas and formating necessary for that perticular department. I need to copy the (Add Member) row and place it above the the old (Add Member) row, then clear the contents of the A"#" cell to allow the user to "Add" the new member to the list. I would like this to occur only when Add Member cell is clicked in some way.

The (Add Member) cell is locked to prevent deletion in a protected sheet. This new row will have to be unlocked for the new entry.

A1
Name
Department A
Joe Smith
Ann Doe
John Snuffy
(Add Member)

Department B
Jon Doe
Frank Lee
(Add Member)

Department C
Etc.
(Add Member)


ActiveCell.EntireRow.Select
Selection.Copy
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
Right click on sheet table go "view code" then paste:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    
    If Target.Column = 1 Then
        If Target.Value = "(Add Member)" Then
            Range("A" & Target.Row).Insert Shift:=xlDown
        End If
    End If
End Sub
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
I noticed that this will keep inserting rows so i added another test that will check if the row above is already blank then not insert another row so you dont end up with lots of blank rows..

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    If Target.Column = 1 Then
        If Target.Value = "(Add Member)" Then
            If Range("A" & Target.Row - 1) <> "" Then
                Range("A" & Target.Row).Insert Shift:=xlDown
            End If
        End If
    End If
End Sub
 

rob-strong

New Member
Joined
Apr 3, 2004
Messages
14
Will this work on any cell that has A# (New Member) in it? And how did you get the code to execute?

Thanks for you quick reply! I appreciate it!
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
All you need to do is right click on the sheet tab at the bottom of the sheet (i.e. Sheet1) go "view code" then paste the stuff above.

it will work for any cell in column A with "(Add Member)" in it

to make it work in column B change the line

Code:
If Target.column = 1 then
to
Code:
If Target.column = 2 then

etc

to change the text that it is looking for change
Code:
If Target.value= "(Add Member)" then
to
Code:
If Target.value= "(New Member)" then

etc
 

rob-strong

New Member
Joined
Apr 3, 2004
Messages
14
No problem getting the code in the sheet. It's the "getting it to execute". When I click the cell with the (New Member) in it, nothing happens. Maybe I am missing something.
 

rob-strong

New Member
Joined
Apr 3, 2004
Messages
14
Hey all the sudden it started working. However it needs to do the whole row. Thanks I am excited that were almost there. Big thanks so far!
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
your original post said the text was "Add Member" not "New Member" which is it?

all you need to do is change the following line to whatever you need

Code:
If Target.value= "(Add Member)" then
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
Hey all the sudden it started working. However it needs to do the whole row. Thanks I am excited that were almost there. Big thanks so far!

Oh didn't realise, no problemo

change
Code:
Range("A" & Target.Row).Insert Shift:=xlDown

to

Code:
rows(Target.Row).Insert Shift:=xlDown
 

Forum statistics

Threads
1,181,102
Messages
5,928,067
Members
436,586
Latest member
latintxn

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
Top