Link rows on different sheets, so same actions apply

[Bloople]

So, now my first problem has been kindly fixed, I have another query. This will just be a nice bonus if I can get it to work.

I have a workbook with an overview sheet of jobs. When I select a city in column 6, for example London, the entire row is copied to the London spreadsheet.
When I come to complete the job, I select Completed in column 9 on the Overview sheet and it copies it the entire row to the Completed sheet and deletes it from the Overview sheet.

What I'm now after is to find a way for that action to delete the duplicate in the London sheet? As it is, I have to go into the City sheet and delete the job manually.

I was advised that there needs to be a unique reference for each job (thanks Fluff) and this might be an issue. Most jobs have a unique reference number but there are some anomolies in which rather than a ref number, it could be a name or address, and these could be repeated in future so it wouldn't be unique. eg, the reference column (Column D) will have 1234, 2833, 1834, and so on, then there might be one where the only reference would be Bloople. And next month, there might be another job relating to Bloople, and you'd never know what number you're up to so can't just add a number on the end.

 

[Bloople]

Oh sorry, I hadn't yet, but it will be in column J

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Overview sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make an entry in column F or column I and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F,I:I")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim fnd As Range
    Select Case Target.Column
        Case Is = 6
            Target.EntireRow.Copy Sheets(Target.Value).Cells(Sheets(Target.Value).Rows.Count, "A").End(xlUp).Offset(1)
        Case Is = 9
            If Target = "Completed" Then
                Target.EntireRow.Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1)
                Set fnd = Sheets(Target.Offset(, -2).Value).Range("J:J").Find(Target.Offset(, 1).Value)
                If Not fnd Is Nothing Then
                    Sheets(Target.Offset(, -2).Value).Rows(fnd.Row).Delete
                End If
            End If
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Bloople]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Overview sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make an entry in column F or column I and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F,I:I")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim fnd As Range
    Select Case Target.Column
        Case Is = 6
            Target.EntireRow.Copy Sheets(Target.Value).Cells(Sheets(Target.Value).Rows.Count, "A").End(xlUp).Offset(1)
        Case Is = 9
            If Target = "Completed" Then
                Target.EntireRow.Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1)
                Set fnd = Sheets(Target.Offset(, -2).Value).Range("J:J").Find(Target.Offset(, 1).Value)
                If Not fnd Is Nothing Then
                    Sheets(Target.Offset(, -2).Value).Rows(fnd.Row).Delete
                End If
            End If
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Thank you. I tried this and when I chose COMPLETED on the overview sheet, I got an error that said:

Run-time error '9':
subscript out of range

When I clicked debug, it highlighted the following line:
Set fnd = Sheets(Target.Offset(, -2).Value).Range("J:J").Find(Target.Offset(, 1).Value)

 

[mumps]

The error indicates that the City sheet in question does not exist. It's hard for me to tell for sure what the problem is without seeing your data. Can you post a screenshot (XL2BB) of both sheets including all the data you are using (de-sensitized if necessary) I tested the macro on some dummy data and it worked as you requested.

 

[Bloople]

It's just the one I posted before - that was an XL2BB capture of Overview, but the city sheets are exactly the same, just titled differently. Normally, the rest of the cells would have some data in, but that;s just text entries, no formula, no lists, nothing else.

The city part works of your code, when I choose a city, it copies the row to the relevant city sheet. All fine.
It's the completed part that doesn't work. I copied the code just as you wrote it. The cell content is 'completed' and the sheet is 'completed. But it doesn't get cut and pasted to the completed sheet, and it doesn't get deleted from the city sheet.

 

[mumps]

Are the City, Status and Unique ID in columns F, I and J respectively?

 

[Bloople]

Are the City, Status and Unique ID in columns F, I and J respectively?

Yes they are

 

[mumps]

Click here to download my sample file. I used "w", "x" and "y" as the city names. Enter an "x" in F3 in the Overview sheet, press the RETURN KEY and that row will be copied to the "x" sheet. Next enter "Completed" in I3, press the RETURN KEY and that row will be copied to the "Completed" sheet and deleted from the "x" sheet. If your data is organized the same way as in my sample, then the macro will work for you as you requested.

 

[Bloople]

Click here to download my sample file. I used "w", "x" and "y" as the city names. Enter an "x" in F3 in the Overview sheet, press the RETURN KEY and that row will be copied to the "x" sheet. Next enter "Completed" in I3, press the RETURN KEY and that row will be copied to the "Completed" sheet and deleted from the "x" sheet. If your data is organized the same way as in my sample, then the macro will work for you as you requested.

ok so I downloaded yours and can see how it works. It did delete the row from the City sheet when I wrote Completed in row I, so that's good, but it didn't delete the row from Overview sheet.

Also, I then tried adding another row, so I put 4 in the J column, underneath your 1-3. When I put w in the City column it copied the row to the w sheet, but when I typed Completed in column I, I got that same runtime error again.

Also I've now realised that when I put the City in Colum F, it copies the row as it is. Whereas it's natural progression to start from left to right, and after the City column, there are other columns of data, which are filled in afterwards, and so they don't get copied. Maybe I'm asking too much of this and it's certainly way beyond my capabilities anyway. (and I'm sure everything I wrote is confusing you more! )

 

[mumps]

This version should take care of the error and delete the row from Overview. However, if there are other columns that don't get filled in until afterwards, we will have to take another approach. What is the first blank column on the right of your Overview sheet? Will this always be the case or can the number of columns change?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F,I:I")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim fnd As Range
    Select Case Target.Column
        Case Is = 6
            Target.EntireRow.Copy Sheets(Target.Value).Cells(Sheets(Target.Value).Rows.Count, "A").End(xlUp).Offset(1)
        Case Is = 9
            If Target = "Completed" Then
                Target.EntireRow.Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1)
                Set fnd = Sheets(Target.Offset(, -3).Value).Range("J:J").Find(Target.Offset(, 1).Value)
                If Not fnd Is Nothing Then
                    Sheets(Target.Offset(, -3).Value).Rows(fnd.Row).Delete
                    Target.EntireRow.Delete
                End If
            End If
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub