Update multiple sheets when updating master

M

mikeonwow

New Member
Joined
Nov 13, 2017
Messages
10
I have a workbook with multiple sheets for various pieces of info I need to gather depending on the audience. All 5 sheets share 1st 5 columns of Static data and need that visibility. I need to update the master and at the same time update the child spreadsheets. None of the columns are unique by themselves, but a combination of two columns can be. I have attached a simplistic example of the sheet. Child1 and Child2 use different Fields along with the Static fields.

Any help that can be provided to assist will be greatly appreciated. I have searched and searched without success to find someone asking how to do this. Either this is so simple anyone should be able to figure it out or how I want it to behave is not possible. I have tried linking the fields by doing the =Sheet1!A1 and that does not really behave the way I would expect it to.

Request Number AreaObject ID ApprovalField1Field2Field3
1 GL2 Yesdatadatadata
1 AR17 Yesdatadatadata
2 AR6 Yesdatadatadata
2 AP8 Nodatadatadata
2 GL2 Nodatadatadata
3 AR12 Yesdatadatadata
4 AP3 Yesdatadatadata
5 GL16 Yesdatadatadata

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
firsts things first -- figure out how to make a unique reference you can use on the master and all children sheets.
Once you have that, we can work with making a Worksheet_Change vba to update the children.
 
Upvote 0
Roderick_E said:
firsts things first -- figure out how to make a unique reference you can use on the master and all children sheets.
Once you have that, we can work with making a Worksheet_Change vba to update the children.
Click to expand...
Combination of request and object are unique.
 
Upvote 0
If you can create a helper column with those 2 columns concatenated it would make your job easier.
This would need to be done on each sheet; the master and the children.

DO THIS ON A TEST VERSION FIRST

Then on the master, we would right click; select VIEW Code and paste this code:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim chidsheets As String
Dim childsheetsarray() As String
Dim uidcol As String
Dim i As Long
Dim uidcolrng As String
Dim childrow As Long
'configs to set
uidcol = "z" 'should be same on master and and children. Can be any column you want.
childsheets = "Sheet2,Sheet3" 'comma separated list of all the child sheets to affect. Exact names
'----
uidcolrng = uidcol & ":" & uidcol
childsheetsarray = Split(childsheets, ",")


For i = LBound(childsheetsarray) To UBound(childsheetsarray)
If IsError(Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)) = False Then
childrow = 0 'reset
childrow = Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)
'--------------
'which columns to change on child sheets -- repeat this block for each child sheet
If childsheetsarray(i) = "Sheet2" Then 'conditional naming of EACH child sheet
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A") 'see how child copies master? Can change "A" to any column.  Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "G") 'repeat for however many columns of data needed
End If
'-------------
End If
Next i
End Sub
 
Upvote 0
Roderick_E said:
If you can create a helper column with those 2 columns concatenated it would make your job easier.
This would need to be done on each sheet; the master and the children.

DO THIS ON A TEST VERSION FIRST

Then on the master, we would right click; select VIEW Code and paste this code:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim chidsheets As String
Dim childsheetsarray() As String
Dim uidcol As String
Dim i As Long
Dim uidcolrng As String
Dim childrow As Long
'configs to set
uidcol = "z" 'should be same on master and and children. Can be any column you want.
childsheets = "Sheet2,Sheet3" 'comma separated list of all the child sheets to affect. Exact names
'----
uidcolrng = uidcol & ":" & uidcol
childsheetsarray = Split(childsheets, ",")


For i = LBound(childsheetsarray) To UBound(childsheetsarray)
If IsError(Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)) = False Then
childrow = 0 'reset
childrow = Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)
'--------------
'which columns to change on child sheets -- repeat this block for each child sheet
If childsheetsarray(i) = "Sheet2" Then 'conditional naming of EACH child sheet
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A") 'see how child copies master? Can change "A" to any column.  Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "G") 'repeat for however many columns of data needed
End If
'-------------
End If
Next i
End Sub
Click to expand...

Thank you, I will work through this and create the concatenate column as suggested. I appreciate the starting point and will reply once I get it working or if I have any further questions... MUCH APPRECIATED
 
Upvote 0
I just thought, we need a way to add new unique references from the master to the children. Let me know if this is a concern and I can modify the code to account for it.
 
Upvote 0
Ok I just went ahead and did the work to add new uid lines to the childsheets. See code below:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim chidsheets As String
Dim childsheetsarray() As String
Dim uidcol As String
Dim i As Long
Dim uidcolrng As String
Dim childrow As Long
Dim lastrow As Long
'configs to set
uidcol = "z" 'should be same on master and and children. Can be any column you want.
childsheets = "Sheet2,Sheet3" 'comma separated list of all the child sheets to affect. Exact names
'----
uidcolrng = uidcol & ":" & uidcol
childsheetsarray = Split(childsheets, ",")
On Error Resume Next
For i = LBound(childsheetsarray) To UBound(childsheetsarray)
'add new uid to childsheets
If IsError(Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)) = True And Trim(Target) <> "" Then
lastrow = 0 'reset
lastrow = Sheets(childsheetsarray(i)).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
If lastrow <> 0 Then
Sheets(childsheetsarray(i)).Cells(lastrow, "z") = Cells(Target.Row, "z")
End If
End If
'add to existing uid rows
If IsError(Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)) = False Then
childrow = 0 'reset
childrow = Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)
'--------------SAMPLE ADDING TO CHILD [B]SHEET2[/B]
'which columns to change on child sheets -- repeat this block for each child sheet
If childsheetsarray(i) = "Sheet2" Then 'conditional naming of EACH child sheet
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A") 'see how child copies master? Can change "A" to any column.  Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "G") 'repeat for however many columns of data needed
End If
'-------------


'--------------SAMPLE ADDING TO CHILD [B]SHEET3[/B]
'which columns to change on child sheets -- repeat this block for each child sheet
If childsheetsarray(i) = "Sheet3" Then 'conditional naming of EACH child sheet
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A") 'see how child copies master? Can change "A" to any column.  Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "G") 'repeat for however many columns of data needed
End If
'-------------


End If
Next i
Resume Next
End Sub
 
Upvote 0
Roderick_E said:
Ok I just went ahead and did the work to add new uid lines to the childsheets. See code below:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim chidsheets As String
Dim childsheetsarray() As String
Dim uidcol As String
Dim i As Long
Dim uidcolrng As String
Dim childrow As Long
Dim lastrow As Long
'configs to set
uidcol = "z" 'should be same on master and and children. Can be any column you want.
childsheets = "Sheet2,Sheet3" 'comma separated list of all the child sheets to affect. Exact names
'----
uidcolrng = uidcol & ":" & uidcol
childsheetsarray = Split(childsheets, ",")
On Error Resume Next
For i = LBound(childsheetsarray) To UBound(childsheetsarray)
'add new uid to childsheets
If IsError(Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)) = True And Trim(Target) <> "" Then
lastrow = 0 'reset
lastrow = Sheets(childsheetsarray(i)).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
If lastrow <> 0 Then
Sheets(childsheetsarray(i)).Cells(lastrow, "z") = Cells(Target.Row, "z")
End If
End If
'add to existing uid rows
If IsError(Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)) = False Then
childrow = 0 'reset
childrow = Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)
'--------------SAMPLE ADDING TO CHILD [B]SHEET2[/B]
'which columns to change on child sheets -- repeat this block for each child sheet
If childsheetsarray(i) = "Sheet2" Then 'conditional naming of EACH child sheet
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A") 'see how child copies master? Can change "A" to any column.  Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "G") 'repeat for however many columns of data needed
End If
'-------------


'--------------SAMPLE ADDING TO CHILD [B]SHEET3[/B]
'which columns to change on child sheets -- repeat this block for each child sheet
If childsheetsarray(i) = "Sheet3" Then 'conditional naming of EACH child sheet
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A") 'see how child copies master? Can change "A" to any column.  Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "G") 'repeat for however many columns of data needed
End If
'-------------


End If
Next i
Resume Next
End Sub
Click to expand...

I have been out of the office, and yes I had noticed this but didn't want to look a gift horse in the mouth. I apply these changes to my test system first and once validated will move them into the production version. I will be forever indebted to you for this. Thank you soooooo very much.
 
Upvote 0
Still having issues when pasting a range of cells copying through to child sheets. I have included my version of your suggestion and live data. if I copy a row or column and paste below The child sheets do not receive all of the data. The first cell will be populated and thats all.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim childsheets As String
Dim childsheetsarray() As String
Dim uidcol As String
Dim i As Long
Dim uidcolrng As String
Dim childrow As Long
Dim lastrow As Long




''L is a column in the table, I can't imagine that it should be an issue?


'configs to set
uidcol = "L" 'should be same on master and and children. Can be any column you want.
childsheets = "DevTrack,ApproveTrack,ReleaseTrack" 'comma separated list of all the child sheets to affect. Exact names
'----


uidcolrng = uidcol & ":" & uidcol
childsheetsarray = Split(childsheets, ",")


On Error Resume Next
For i = LBound(childsheetsarray) To UBound(childsheetsarray)
If IsError(Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)) = True And Trim(Target) <> "" Then
lastrow = 0 'reset
lastrow = Sheets(childsheetsarray(i)).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1


If lastrow <> 0 Then
Sheets(childsheetsarray(i)).Cells(lastrow, "L") = Cells(Target.Row, "L")
End If
End If

'add to existing uid rows
If IsError(Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)) = False Then
childrow = 0 'reset
childrow = Application.Match(Cells(Target.Row, uidcol), Sheets(childsheetsarray(i)).Range(uidcolrng), 0)
'--------------
'which columns to change on child sheets -- repeat this block for each child sheet


If childsheetsarray(i) = "DevTrack" Then 'conditional naming of EACH child sheet

'see how child copies master? Can change "A" to any column. Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A")
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "B")
Sheets(childsheetsarray(i)).Cells(childrow, "C") = Cells(Target.Row, "C")
Sheets(childsheetsarray(i)).Cells(childrow, "D") = Cells(Target.Row, "D")
Sheets(childsheetsarray(i)).Cells(childrow, "E") = Cells(Target.Row, "E")
Sheets(childsheetsarray(i)).Cells(childrow, "F") = Cells(Target.Row, "F")
Sheets(childsheetsarray(i)).Cells(childrow, "G") = Cells(Target.Row, "G")
Sheets(childsheetsarray(i)).Cells(childrow, "H") = Cells(Target.Row, "H")
Sheets(childsheetsarray(i)).Cells(childrow, "I") = Cells(Target.Row, "I")
Sheets(childsheetsarray(i)).Cells(childrow, "J") = Cells(Target.Row, "J")
Sheets(childsheetsarray(i)).Cells(childrow, "K") = Cells(Target.Row, "K")
End If 'DevTrack

'conditional naming of EACH child sheet


If childsheetsarray(i) = "ApproveTrack" Then
'see how child copies master? Can change "A" to any column. Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A")
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "B")
Sheets(childsheetsarray(i)).Cells(childrow, "C") = Cells(Target.Row, "C")
Sheets(childsheetsarray(i)).Cells(childrow, "D") = Cells(Target.Row, "D")
Sheets(childsheetsarray(i)).Cells(childrow, "E") = Cells(Target.Row, "E")
Sheets(childsheetsarray(i)).Cells(childrow, "F") = Cells(Target.Row, "F")
Sheets(childsheetsarray(i)).Cells(childrow, "G") = Cells(Target.Row, "G")
Sheets(childsheetsarray(i)).Cells(childrow, "H") = Cells(Target.Row, "H")
Sheets(childsheetsarray(i)).Cells(childrow, "I") = Cells(Target.Row, "I")
Sheets(childsheetsarray(i)).Cells(childrow, "J") = Cells(Target.Row, "J")
Sheets(childsheetsarray(i)).Cells(childrow, "K") = Cells(Target.Row, "K")
End If


If childsheetsarray(i) = "ReleaseTrack" Then
'see how child copies master? Can change "A" to any column. Can even be different
Sheets(childsheetsarray(i)).Cells(childrow, "A") = Cells(Target.Row, "A")
Sheets(childsheetsarray(i)).Cells(childrow, "B") = Cells(Target.Row, "B")
Sheets(childsheetsarray(i)).Cells(childrow, "C") = Cells(Target.Row, "C")
Sheets(childsheetsarray(i)).Cells(childrow, "D") = Cells(Target.Row, "D")
Sheets(childsheetsarray(i)).Cells(childrow, "E") = Cells(Target.Row, "E")
Sheets(childsheetsarray(i)).Cells(childrow, "F") = Cells(Target.Row, "F")
Sheets(childsheetsarray(i)).Cells(childrow, "G") = Cells(Target.Row, "G")
Sheets(childsheetsarray(i)).Cells(childrow, "H") = Cells(Target.Row, "H")
Sheets(childsheetsarray(i)).Cells(childrow, "I") = Cells(Target.Row, "I")
Sheets(childsheetsarray(i)).Cells(childrow, "J") = Cells(Target.Row, "J")
Sheets(childsheetsarray(i)).Cells(childrow, "K") = Cells(Target.Row, "K")
End If
'-------------
End If
Next i
Resume Next
End Sub


PMC #DESCRIPTIONTICKET ID

OBJECT IDAPPROVALENHANCEMENT
OR DEFECT		STATUSTRACKDEV TEAMTARGET RELEASEDEVELOPERSEQID
DO NOT CHANGE THIS COLUMN
450585 Credit Hold Report - Change/Add/Update Credit Hold Report to be more efficient for users REP_OTC_OM_004YES FOT TESTINGOTCTCS2018-March-1.0 1
452483INVOICE PRINT Change GSA Schedule label on Invoice Print to enable all public sector contracts to use the capability REP_OTC_OM_003YES FOT TESTINGOTCTCS2018-March-1.0 2
459505Demantra Collection Missed Logic to Calculate Booking History for Forecast CalculationDefect 16156EXT_PSP_PLN_009YES FOT TESTINGPSP 2018-March-1.0Bill Smith3
459505Demantra Collection Missed Logic to Calculate Booking History for Forecast CalculationDefect 16156EXT_PSP_PLN_012AYES FOT TESTINGPSP 2018-March-1.0John Jones4
459505Demantra Collection Missed Logic to Calculate Booking History for Forecast CalculationDefect 16156EXT_PSP_PLN_012BYES FOT TESTINGPSP 2018-March-1.0 Jane Doe5
462029Remove references to Dealer Fee Payments in AP016 & AP004 to enable Payments for Marketing Programs INT_RTR_AP_004YES FOT TESTING 2018-March-1.0 6
462029Remove references to Dealer Fee Payments in AP016 & AP004 to enable Payments for Marketing Programs INT_RTR_AP_016YES FOT TESTING TCS2018-March-1.0 7
479457Def 17901/15605:Tentative schedule fix REP_PSP_PLN_009 - Cancelled ISOs are showing up on the tentative schedule report with no SSD
 REP_PSP_PLN_009YES MIG PRDPSPTCS2018-March-1.0 8

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
So you are pasting data into the master? My code was designed to trigger on an individual entry; not a paste of multiple cells. We could change that so it loops through each master row each time but that could possibly really slow things down.
Alternatively, we keep the code as is and add an "Update" button that does a one time loop on the master to get everything to work. We could even place this code in a "BeforeClose" call so that if the user forgets to press the update it will be done automatically before the master workbook is closed.

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
call updatechildren
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Update row data using another worksheet data
Replies
1
Views
151
denz0215
D
Kaz09
  • Question
Copy rows of data from multiple worksheets to based on column equalling YES
Replies
0
Views
110
Kaz09
Kaz09
G
  • Question
Master excel that is updated by; and updates other excel files in folder.
Replies
1
Views
387
AFPathfinder
A
H
  • Question
Update master sheet automatically when changes are made to split sheet VBA
Replies
0
Views
329
helplessnoobatexcel
H
J
  • Question
Update main sheet, when making an update in the sheet with the power query connection
Replies
0
Views
232
jamobe
J

Forum statistics

Threads
1,215,022
Messages
6,122,721
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top