Hi, rs1aj,
I´ve added a new sheet to the workbook called Tables. Please have a look at it as it holds the correlation between the information from your Master Spreadsheet and the individual Vendor Sheets which should be completed for the other sheets you have on hand. I marked two cells with yellow and question marks as I wasn´t able to guess at what values would be needed to be copied over there. For text for these cells would be "Please check!!!" unless you fill the cells with the proper columns.
The code I used is placed in ThisWorkbook:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim wsTab As Worksheet
Dim wsMaster As Worksheet
Dim var As Variant
Dim varPart As Variant
Dim lngCol As Long
On Error GoTo Exit_Here
Select Case Sh.Name
Case "Master Spreadsheet", "Tables"
'do nothing
Case Else
If Target.Count = 1 And Target.Column = 1 And Target.Row > 1 Then
'I´ve added this line, you may comment it out once the fields have been filled as required
MsgBox "Please fill out the '??' fields in Sheet 'Tables'!", vbExclamation
Set wsTab = Worksheets("Tables")
Set wsMaster = Worksheets("Master Spreadsheet")
var = Application.Match(Sh.Name, wsTab.Columns(1), 0)
If wsTab.Cells(var, 2).Value = False Then GoTo Exit_Here
If Not IsError(var) Then
varPart = Application.Match(Target.Value, wsMaster.Columns(1), 0)
If Not IsError(varPart) Then
Application.EnableEvents = False
For lngCol = 2 To Sh.Cells(1, Columns.Count).End(xlToLeft).Column
If wsTab.Cells(var, lngCol + 2) <> "??" Then
Sh.Cells(Target.Row, lngCol).Value = wsMaster.Cells(varPart, wsTab.Cells(var, lngCol + 2)).Value
Else
Sh.Cells(Target.Row, lngCol).Value = "Please check!!!"
End If
Next lngCol
Application.EnableEvents = True
End If
Else
MsgBox "Sheet ' & sh.name & ' is not listed in Sheets 'Tables'. Please add the information"
End If
End If
End Select
Exit_Here:
Application.EnableEvents = True
Set wsTab = Nothing
Set wsMaster = Nothing
End Sub
Please take your time going through the code and to check if the result is near to what you want (and if you can add the information into Tables by yourself as needed).
Workbook attached.
Ciao,
Holger
Bookmarks