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]

This is the macro InsertRow:

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

Column A in the range is hidden and doesn't fill down. Any ideas how to fix? My google searches are giving me nothin' on this one.

 

[Ishna]

Column A is also my filter column... a macro when the sheet is opened configures the filter criteria to only display records with 'Yes' in column A. When InsertRow inserts a row and fills down, missing the hidden column A (which is empty), the row is still visible until the sheet is re-opened, and then it disappears. So I don't think it's the filter.

Worst comes to the worst I'll just make the column really, really skinny, haha.

 

[Dryver14]

It probably is the filter as you said yourself it operates when you open the workbook, you could try making this a change event macro on the worksheet or including it in part of your code for inserting the new line.

I have to admit though I dont fully understand what is going on as you keep teasing with snippets of code here and there, but I do like your process of finding bits of code here and there and doing for yourself.

 

[Ishna]

Hi Dryver14

Thanks for taking the time to get back to a total noob. I'm sorry I've been "teasing" -- I haven't used a technical forum like this before and I don't know too much about Excel and VB so if I could be doing things differently please tell me! I know how annoying it is when someone asks a question without having tried fixing it for themselves so I try searching and learning as much as I can to fix on my own. On the bright side, I've made a nice little record for myself here (much to the board regulars' disgust I'm sure).

The filter works when I open the worksheet, not the workbook. I turned on the filter then recorded a macro configuring the filter on column A to show 'Yes' rows only. The macro is launched from the worksheet's main code page when activated (Private Worksheet or something -- sorry, don't know the right term, don't have access to Excel right now).

This is so I can complete an entry on Sheet1 and only relevant cells for entries flagged 'Yes' are displayed on Sheet2, and I can keep changing between the two sheets and will always have the most current list of 'Yes' records on Sheet2 (without having to do anything) because whenever I go to that worksheet the macro runs automatically.

I've tested InsertRow with column A hidden, column A visible, filter on, filter off, protection on and off (yes I removed it from the macro too!) and it works fine except when the column is hidden. When I look at it again tomorrow the solution will probably jump out at me.

 

[Dryver14]

I Too am a noob, if it works when unhidden can you not make that part of the macro ie: first line column unhide : Macro : column hide

 

[Ishna]

*facepalm* See?!?! Simple things!!

Thanks Dryver14

 

[Ishna]

So in summary:

Here is the macro InsertRow which unhides the filter column A, inserts a row below active row, copies the contents of cells in the active row from column A thru G and pastes them into the newly inserted row below, then hides the filter column A:

Sub InsertRow()
Sheet3.Unprotect Password:=""
Columns("A").Hidden = False
Dim i As Long
Selection.Offset(1, 0).EntireRow.Insert
i = Selection.Row
Range("A" & i & ":G" & i + 1).FillDown
Columns("A").Hidden = True
Sheet3.Protect Password:=""
End Sub

And here is the macro which calls the InsertRow macro when the the contents of cell Mx is "Rejected":

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

Thanks Dryver14 for the help!

Ish

 

[Dryver14]

Your very welcome maybe 2 noobs make nearly a whole one!