Insert row below conditional cell and copy/paste some original fields into new row

[Ishna]

Hello

I've got a workbook containing Sheet1 and Sheet2.

Staff enter a project idea in Sheet1. When the idea is marked 'approved' it appears on Sheet2 automatically by way of autofilter.

Sheet2 tracks the progress of the project as the staff search for someone to support it. They apply to the person for support, and if they get it, they enter 'Successful' in the 'Successful/Rejected?' field. No worries.

If they are not successful, ie. the support person rejects their application, they enter 'Rejected' in the 'Successful/Rejected?' field.

When something is 'Rejected' I need to automatically insert a row beneath the active row and copy/paste the info in cells A-G of the active row into the new row (so to copy/paste their original project details into a new row so they can look for someone else to support it). And so on until it is 'Successful'.

Lots of projects will be recorded on Sheet2 so the macro/VB will need to execute on any row within Sheet2.

I hope this makes sense - it's my first time posting an Excel question.

I've researched but can only find very specific examples and I can't quite tailor them to my needs.

If you need more info, please ask!

Thanks
Ishna

 

[Ishna]

Whoops! Forgot to say I'm using Excel 2003 and Windows XP. Can pinch a PC with Excel 2007 if needed.

 

[Ishna]

Bumpity-bumb

 

[Ishna]

I pinched some code from this thread and made this:

Sub InsertRow()
Dim i As Long
Selection.Offset(1, 0).EntireRow.Insert
i = Selection.Row
Range("A" & i & ":T" & i + 1).FillDown
End Sub

This works to insert a row beneath the active row when I run the macro.

As cells in columns A thru G reference cells on Sheet1 (example ='Sheet1'!A5) I found my new row would reference ='Sheet1'A6 instead of A5, so I've made all my references absolute by using ='Sheet1'$A$5 and so on. There's probably a better way of doing it but I'll protect Sheet1 so no rows can be added to protect integrity.

Now, how can I execute my macro when the contents of M5 (where 5 is whichever row is active) is equal to the word 'Rejected'? I have a validation list where options are 'Accepted' 'Rejected' and 'Abandoned'. 'Rejected' needs to run the macro to give a new row beneath the active row.

Thanks in advance.
Ish

 

[Ishna]

I got this code from here: http://www.cpearson.com/excel/differen.htm

Private Sub Worksheet_Change (ByVal Target As Excel.Range)
If Target = [A1] Then
If Target.Value > 10 Then
MyMacro
End If
End If
End Sub

I don't know how to specify the target range as the cells M6 thru M237, or how to make Target.Value = 'Rejected' (that might work but can't test it).

 

[Ishna]

Any takers? Anyone at all?

 

[Ishna]

Okay, I've done some research and done this:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("M6:M999")) Is Nothing Then
If Target.Value = "Rejected" Then
Run 'InsertRow'
End If
End If
End Sub

When I put "Rejected" in a cell in target range I get

Run-time error '5': Invalid procedure call or argument

and the debugger highlights the code Run 'InsertRow' (InsertRow is the name of my macro).

 

[Dryver14]

try call InsertRow

and do not put ' around it as it then comments out what you want to do

 

[Ishna]

Thanks Dryver14. Such simple things!

It appears I've really broken things now though.

When I change a cell in range to "Rejected", it runs part of my InsertRow macro (it inserts a row but doesn't fill down) and gives me:

Run-time error '13': Type mismatch

and the debugger highlights this line in VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("M6:M999")) Is Nothing Then
If Target.Value = "Rejected" Then
Call InsertRow
End If
End If
End Sub

I get the same error if I run the InsertRow macro without any input in the "Rejected" range. InsertRow macro works fine if I remove the above Worksheet_Change thingy... whatever it's called non-macro piece of event code or whatever.

I will research and post back with my results.

 

[Ishna]

Solution found here: http://excel.bigresource.com/Track/excel-xFTNLe00/

Solution code highlighed:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
If Target.Count = 1 Then
If Not Intersect(Target, Range("M6:M999")) Is Nothing Then
If Target.Value = "Rejected" Then
Call InsertRow
End If
End If
End If
End Sub