Articles

Solved macro or formula to insert row based on input

July 30, 2010 at 03:55:53
Specs: Windows XP

Hi, I hope someone can help me with this. I have an excel sheet where I need to insert a row based on the input value (by the user) in a specific cell. If the user type Y in cell C8, then a row needs to be inserted right below it and the data in cells F8 through M8 need to be copied onto the inserted row. Now, the Y is not only for cell C8 but for the entire column C. So the macro or formula or whatever needs to be valid for the entire column C and whatever line it might be on. If somebody types N in column C then nothing should happen. I sure hope someone can help me with this.

See More: macro or formula to insert row based on input

Report •


#1
July 30, 2010 at 04:40:38
✔ Best Answer

You can't use a formula to insert a Row, so you have to use macro.

Right-Click the sheet tab for the sheet you want this to happen in, select View Code, and paste this into the pane that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHnd
'Don't do anything if more than one cell has been changed
 If Target.Cells.Count > 1 Then Exit Sub
'Determine if the changed cell is in Column C and is a Y
  If Target.Column = 3 Then
   If Target = "Y" Then
'Disable events so code doesn't fire again when row is inserted
     Application.EnableEvents = False
'Copy & Insert changed Row, Clear dotted lines
      Target.EntireRow.Copy
      Range("A" & Target.Row + 1).Insert Shift:=xlDown
      Application.CutCopyMode = False
   End If
  End If
errHnd:
'Re-enable event
 Application.EnableEvents = True
End Sub


Report •

#2
July 30, 2010 at 06:42:48

Wow, great, it worked!!!!!

I just realised I forgot to mention something in my original question. Would it also be possible for the macro to put the value 2201 in the cell of column B on the line it just created?

If that could be possible then all my problems are solved. Thanks again for your help though!!!!!


Report •

#3
July 30, 2010 at 07:16:25

Add these lines after the line that does the insert:

'Put 2201 in Column B of inserted Row
      Range("B" & Target.Row + 1) = 2201


Report •

Related Solutions

#4
July 31, 2010 at 01:44:17

Thank you so much, you're the best!!!

Report •

#5
July 31, 2010 at 05:10:42

Glad I could help.

Report •

#6
October 26, 2010 at 14:19:00

DerbyDad03, I'm trying to adapt the macro you provided to SRC but I'm struggling with two issues:

1) How to add 11 rows if the cell value is "Monthly"; how to add 51 rows if the cell value is "Weekly"? I tried incorporating this suggestion but to no avail:

http://www.asap-utilities.com/excel...

2) Whenever I use your original macro, before it pastes the new row, I get the msg:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape....

The paste only succeeds if I select a single cell and then hit ENTER. Is there a way to automatically add these rows without hitting the ENTER key?

Thanks


Report •

#7
October 26, 2010 at 14:47:55

Actually, this addresses my first question about adding extra rows:

Range("A" & Target.Row + 1).Resize(11).Insert Shift:=xlDown

I'm still figuring out how to insert an elseif for weekly value...

Thanks


Report •

#8
October 26, 2010 at 15:55:30

I've resolved #1...but I still need help with #2 if anyone has suggestions, I'd appreciate it.

1) How to add 11 rows if the cell value is "Monthly"; how to add 51 rows if the cell value is "Weekly"? I tried incorporating this suggestion but to no avail:

http://www.asap-utilities.com/excel...

2) Whenever I use your original macro, before it pastes the new row, I get the msg:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape....

The paste only succeeds if I select a single cell and then hit ENTER. Is there a way to automatically add these rows without hitting the ENTER key?

Thanks


Report •

#9
October 26, 2010 at 16:48:29

Figured it out...not graceful but it works...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHnd

'Don't do anything if more than one cell has been changed
If Target.Cells.Count > 1 Then Exit Sub

'Determine if the changed cell is in Column C and is a Y
If Target.Column = 29 Then
If Target = "Weekly" Then
'Disable events so code doesn't fire again when row is inserted
Application.EnableEvents = False
'Copy & Insert changed Row, Clear dotted lines
Range(Cells(Target.Row, "A"), Cells(Target.Row, "AO")).Copy
Range("A" & Target.Row + 1).Resize(51).Insert Shift:=xlDown
Application.CutCopyMode = False

'Determine if the changed cell is in Column C and is a Y
ElseIf Target.Column = 29 Then
If Target = "Monthly" Then
'Disable events so code doesn't fire again when row is inserted
Application.EnableEvents = False
'Copy & Insert changed Row, Clear dotted lines
Range(Cells(Target.Row, "A"), Cells(Target.Row, "AO")).Copy
Range("A" & Target.Row + 1).Resize(11).Insert Shift:=xlDown
Application.CutCopyMode = False

'Determine if the changed cell is in Column C and is a Y
ElseIf Target.Column = 29 Then
If Target = "Quarterly" Then
'Disable events so code doesn't fire again when row is inserted
Application.EnableEvents = False
'Copy & Insert changed Row, Clear dotted lines
Range(Cells(Target.Row, "A"), Cells(Target.Row, "AO")).Copy
Range("A" & Target.Row + 1).Resize(3).Insert Shift:=xlDown
Application.CutCopyMode = False

End If
End If
End If
End If

errHnd:
'Re-enable event
Application.EnableEvents = True
End Sub


Report •


Ask Question