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]

Hi & welcome to MrExcel.
If London is selected in column 6, should that row just get copied to the London sheet?

 

[Bloople]

Hi, and thanks.

Yes that's right, and it does with this code.
And once that job is done, I'd select Completed in column 9, then I'd want the whole row to be cut and paste into the 'completed' spreadsheet. I did have another code that did that, but then the city code wouldn't work with it. I'm happy to throwaway all this if anyone has a better version though.

 

[Fluff]

You're code is copying the row to all 5 sheets, regardless of the city.
Try

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 Then
      Target.EntireRow.Delete Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      Target.EntireRow.Delete
   End If
End Sub

 

[Bloople]

Haha I hadn't even realised!
I used your code, the city part works. But when I change column 9 to Completed, I get a run time error 424 - Object required. When I click debug, it takes me to the line: Target.EntireRow.Delete

 

[Fluff]

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

 

[Bloople]

This is the code that works for the Completed bit:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsUse As Worksheet

Dim wsDc As Worksheet

Dim strdc As String

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

If Target.Cells.Count > 1 Then Exit Sub



If Target.Column <> 9 Then Exit Sub ' change 2 to the correct column number



Set wsUse = ThisWorkbook.Sheets("Overview")

Set wsDc = ThisWorkbook.Sheets("Completed")





With wsUse

strdc = Target.Value



If strdc = "Completed" Then

n = .Rows.Count

Target.EntireRow.Copy

wsDc.Range("A" & n).End(xlUp).Offset(1, 0).PasteSpecial xlValues

wsDc.Range("A" & n).End(xlUp).EntireRow.PasteSpecial xlPasteFormats

Application.CutCopyMode = False

Target.EntireRow.Delete

End If



End With



Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

I just don't know how to add in code for the City bit.

 

[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

It works!!! you're a genius! thanks so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Bloople]

Can I ask another cheeky question please? I now have my overview sheet, which for example, has a London job on. This job is copied to the London spreadsheet. When I come to complete it on the Overview sheet, it copies it to the Completed sheet and deletes it from the Overview sheet. Is there anyway for that action to delete the duplicate in the London sheet? Can they be linked up or something?