2 macros - Copy Row and Cut and paste row to another worksheet based on value

[Bloople]

Afternoon all

I've been working on a spreadsheet. I had no VBA knowledge at all, now I still have close to none! I've been searching various forums and websites and copied and pasted code in a trial and error way, sometimes successfully, sometimes not. I'm stuck now though. I've seen lots of similar questions and experimented with the answers provided, but can't get it to work.

I want two macros to run on my workbook. One is for when a city is chosen from a drop down menu, that row gets copied into the corresponding city worksheet. Secondly, when 'Completed' is chosen from a drop down menu on another column, I want that row to be moved to the Completed spreadsheet.


I've got the following code:

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Column = 6 Then 
tRow = Target.Row 
Range("F" & tRow).EntireRow.Copy Sheets("London").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
Range("F" & tRow).EntireRow.Copy Sheets("Manchester").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
Range("F" & tRow).EntireRow.Copy Sheets("Leeds").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
Range("F" & tRow).EntireRow.Copy Sheets("Birmingham").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
Range("F" & tRow).EntireRow.Copy Sheets("Cardiff").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
End

If Application.EnableEvents = False 

If Target.Column = 9 Then 
tRow = Target.Row 
Range("I" & tRow).EntireRow.Delete Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
End If 
Application.EnableEvents = True

End Sub

The city part works fine. The second part only deletes the row. I want it copied and pasted first, then delete, or just moved. I can't work out what code to add in though and how.

 

[Fluff]

Yes that's possible, as long as there is something unique to each row.
But as that's a totally different question, you will need to start a new thread.

 

[Bloople]

n

Yes that's possible, as long as there is something unique to each row.
But as that's a totally different question, you will need to start a new thread.

No problem, I will do shortly then. Thanks a lot for your help!

 

[Fluff]

 

[Bloople]

Oops, it should be

   ElseIf Target.Column = 9 Then
      Target.EntireRow.Copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      Target.EntireRow.Delete

Hi, sorry to bother you again, but I've noticed today when using this workbook that if I choose one of the other options in column 9, such as LIVE, or PAUSED, then it cuts and pastes it to the COMPLETED sheet, just like it does when I choose COMPLETED. If it says LIVE or PAUSED, I need it to stay in the Overview sheet.

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 6 Then
      Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
   ElseIf Target.Column = 9 And LCase(Target.Value) = "completed" Then
      Target.EntireRow.Copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      Target.EntireRow.Delete
   End If
End Sub

 

[Bloople]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 6 Then
      Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
   ElseIf Target.Column = 9 And LCase(Target.Value) = "completed" Then
      Target.EntireRow.Copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      Target.EntireRow.Delete
   End If
End Sub

Thank you, but unfortunately this didn't work. Nothing happened when I changed Column 9 to COMPLETED (or anything else)

 

[Fluff]

Are you changing cells one at a time?

 

[Bloople]

Are you changing cells one at a time?

Yes I click the drop down menu on an individual cell.

 

[Fluff]

In that case make sure that there are no leading/trailing spaces.
Also are you using the code exactly as I posted, or have you changed it?

 

[Bloople]

Ive got it. I was looking at your code and saw Lcase - figured that must mean lowercase. My COMPLETED is in uppercase, so that's why it hadn't worked