Hi all,
I am wondering if anyone can help with this following spreadsheet VBA – my knowledge to date, has been web searched, combined with a mixture of cut and pastes solutions posted on this forum ?. Unfortunately, my own knowledge doesn’t extend past that and I have been unable to get my sheet to fully work.
In my spreadsheet I note customer details on the ‘Initial Stage’ sheet and in Column ‘R’ there is drop down criteria list:
Booked,
In progress,
Quote sent out,
Not progressed,
Completed
What I am looking for is that if either ‘Completed’ or ‘Not progressed’ criteria are selected, then it will automatically move that row to the corresponding sheet titled, ‘Completed’ or ‘Not progressed’ and delete out the row from the ‘Initial Stage’ sheet.
At the moment I can only get rows to move to the ‘Completed’ sheet from the criteria and do not know how to add in the additional code for ‘Not progressed’ (completely stuck). The code I’ve got so far is:
The sheets are protected with password 1234.
The ‘Completed’ sheet contains additional information for columns ‘S-U’ so I initially tired only moving information contained within columns ‘A-R’, however I couldn’t get it to insert on a new row (it was only transferring to its corresponding row and not into the first available one), which is why I went for moving the full row with ‘unlock /lock functions’ to maintain formatting… (as I said, I’m a newbie ??).
Any help /solutions would be greatly appreciated!
Mike.
I am wondering if anyone can help with this following spreadsheet VBA – my knowledge to date, has been web searched, combined with a mixture of cut and pastes solutions posted on this forum ?. Unfortunately, my own knowledge doesn’t extend past that and I have been unable to get my sheet to fully work.
In my spreadsheet I note customer details on the ‘Initial Stage’ sheet and in Column ‘R’ there is drop down criteria list:
Booked,
In progress,
Quote sent out,
Not progressed,
Completed
What I am looking for is that if either ‘Completed’ or ‘Not progressed’ criteria are selected, then it will automatically move that row to the corresponding sheet titled, ‘Completed’ or ‘Not progressed’ and delete out the row from the ‘Initial Stage’ sheet.
At the moment I can only get rows to move to the ‘Completed’ sheet from the criteria and do not know how to add in the additional code for ‘Not progressed’ (completely stuck). The code I’ve got so far is:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect (1234)
Rows(ActiveCell.Row).Locked = False
If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target = "Completed" Then
Sheets("Completed").Unprotect (1234)
Target.EntireRow.Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
Application.EnableEvents = True
Sheets("Completed").Protect (1234)
ActiveSheet.Protect (1234)
End Sub
The sheets are protected with password 1234.
The ‘Completed’ sheet contains additional information for columns ‘S-U’ so I initially tired only moving information contained within columns ‘A-R’, however I couldn’t get it to insert on a new row (it was only transferring to its corresponding row and not into the first available one), which is why I went for moving the full row with ‘unlock /lock functions’ to maintain formatting… (as I said, I’m a newbie ??).
Any help /solutions would be greatly appreciated!
Mike.
Attachments
Last edited by a moderator: