Update other sheets when I update Master sheet

GojiraKirrer · Nov 17th 2020

I have been trying to find a way to update the subsheets when I update the master sheet. I dont just want them to be copied from the master sheet. I want the other sheets to delete and move rows from one sheet to another depending on the selection at the drop down list. When for example the 'PO sent' is selected from drop down list I would like the PO sent sheet to add that row with the details. And when that same row is changed with drop down list to for example completed, I would like the entire row to be moved to the last open row on the completed sheet. Is this possible?

rollis13 · Nov 18th 2020

Have a go with the macro I created in the MASTER sheet module.

Code

Option Explicit
Public ws3 As Worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("K:K")) Is Nothing Then
Select Case Target
Case "PR TBC"
Set ws3 = Worksheets("PR TBC")
Case "PR Created"
Set ws3 = Worksheets("PR Crtd")
Case "PR Released"
Set ws3 = Worksheets("PR Rlsd")
Case "Qoutation Requested"
Set ws3 = Worksheets("QouRqstd")
Case "PO Sent"
Set ws3 = Worksheets("POSnt")
Case "Collect at Inventory"
Set ws3 = Worksheets("Collect")
Case "On Route"
Set ws3 = Worksheets("OnRte")
Case "Completed"
Set ws3 = Worksheets("Cmpltd")
End Select
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rw As Long
Dim lr As Long
Dim PRn As Range
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("K:K")) Is Nothing Then
Select Case Target
Case "PR TBC"
Set ws2 = Worksheets("PR TBC")
Case "PR Created"
Set ws2 = Worksheets("PR Crtd")
Case "PR Released"
Set ws2 = Worksheets("PR Rlsd")
Case "Qoutation Requested"
Set ws2 = Worksheets("QouRqstd")
Case "PO Sent"
Set ws2 = Worksheets("POSnt")
Case "Collect at Inventory"
Set ws2 = Worksheets("Collect")
Case "On Route"
Set ws2 = Worksheets("OnRte")
Case "Completed"
Set ws2 = Worksheets("Cmpltd")
End Select
Set ws1 = Worksheets("MASTER")
rw = Target.Row
lr = ws2.Cells(Rows.Count, 2).End(xlUp).Row + 1
ws1.Range("A" & rw).Resize(, 11).Copy ws2.Range("A" & lr)
If Not ws3 Is Nothing Then
With ws3.Range("B:B")
Set PRn = .Find(Range("B" & Target.Row).Value, LookIn:=xlValues)
If Not PRn Is Nothing Then
ws3.Range(PRn.Address).EntireRow.Delete
End If
End With
End If
End If
End Sub

Display More

GojiraKirrer · Nov 18th 2020

Thanks it works but not exactly as I wanted. Is it possible to move the rows entirely from one sheet to another when the drop down selection is changed in the master sheet? Currently the rows are copied from one sheet to another. I would like for it to be deleted from each sheet until it reaches the completed sheet.

GojiraKirrer · Nov 18th 2020

Let me explain a bit more clearly. When I re-classify a row in status, e.g. from my original selection 'PR TBC' and I change it to 'PR Created' it is copying the entire row from the master sheet to the PR created sheet. Which is wonderful but I would like it to remove the line it copied from the PR TBC sheet.

**royUK** · Nov 18th 2020

You shouldn't use different sheets. Keep the data al on one sheet with a column for classification. Then you can use AutoFilter, etc to see individua classifications

rollis13 · Nov 18th 2020

Maybe it's me not understanding.

First of all the Master is the only sheet that has data and all the other are empty, when you change status in column K of sheet Master, lets say from 'nothing' to 'PO Sent', the macro will copy the row to sheet POSnt. When later you make a change on the same row in sheet Master, lets say 'PO Sent' to 'Completed', the macro will once again copy the row this time to sheet 'Cmpltd' and at the same time delete the same row in sheet 'PO Sent'. If in the meantime you have done changes in sheet POSnt these will be lost.

**royUK** · Nov 18th 2020

That's what I'm saying you don't need to have multiple sheets. All the data should be on one sheet. You can then just use AutoFilter to see a particular status

rollis13 · Nov 18th 2020

Sorry I just noticed a bug in my macro. To work as said you must conferm your change with the dropdown -list clicking on another cell; you need to change the Focus before changing to a new status.

**royUK** · Nov 18th 2020

You aren't reading my responses. I keep saying that you don't need to have the other sheets. It's just making work.

As for your macro, it's not even in your workbook.

rollis13 · Nov 18th 2020

royUK, GojiraKirrer has never answered after your first post #5, it's only me trying to understand why my macro wasn't working has it should.

**royUK** · Nov 18th 2020

Sorry, I didn't notice. I'm really busy at the moment.

It looks like your code is moving the data in the row.

rollis13 · Nov 18th 2020

At the moment I only found a 'stupid' workaround for my post #8, please add these 3 line just before the last End If in my macro (post #2) that handles the event Worksheet_Change:

Code

Application.EnableEvents = False
Range(Target.Address).Offset(0, 1).Select 'moves right
Application.EnableEvents = True

GojiraKirrer · Nov 19th 2020

Quote from royUK

You shouldn't use different sheets. Keep the data al on one sheet with a column for classification. Then you can use AutoFilter, etc to see individua classifications

aahh ok. I thought this might not be possible too.

GojiraKirrer · Nov 19th 2020

Quote from rollis13

At the moment I only found a 'stupid' workaround for my post #8, please add these 3 line just before the last End If in my macro (post #2) that handles the event Worksheet_Change:

Code

Application.EnableEvents = False

Range(Target.Address).Offset(0, 1).Select 'moves right

Application.EnableEvents = True

Thanks!!!

Sorry I was a bit busy before to reply.

**royUK** · Nov 19th 2020

I didn't say it was impossible. It is inefficient and unnecessary.

Share