Updating multiple worksheets at once; four source, three target

[Silver Fox]

Dear all,

Please see the file attached (there are the same columns on all sheets, apart from on "BLANK"). The "Sort" sheets 1-4 contain rows that appear at various places on the three "Raw data" sheets. The "Sort" sheets are mutually exclusive; there is no repetition of data among them.

When I make an entry into the "ACTION" or "REASON" columns on any of the "Sort" sheets, I want that entry to be replicated on all relevant rows throughout the "Raw data" sheets. Matching of the rows is to be via Criteria 1 and 2.

I would preferably like the replication to be as I make the entries (is this possible), but could also hit an update button periodically.

I am a very much a beginner in these matters. What is the best approach here - to group the sheets, VLOOKUP, pivot tables, etc? How straightforward will this macro be? Has anyone any magic routines in their libraries to hand?

Thanks in advance for any help that can be offered.

[Silver Fox]

Sorry, the title should read: "... three target"

I am currently looking into writing a private sub for each of the "Source" worksheets. This would activate when a change was made to that sheet's ACTION and/or REASON columns, group that sheet with the three "Raw data" ones and make the changes to the relevant cells (assuming this is possible). Or the macro could just loop through the rows of the three "Raw data" sheets and copy IF.

Does this sound like the way forward or am I barking up the wrong tree? Or is my data structured badly in the workbook from the outset?

Any inspiration would be very gratefully received. Many thanks again.

[mdmackillop]

Or the macro could just loop through the rows of the three "Raw data" sheets

Correct approach but use Find rather than loop which needs to check each cell. It's a lot quicker.

Worksheet code
[vba]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Then
Application.EnableEvents = False
FillData Target
Application.EnableEvents = True
End If
End Sub

[/vba]
Module code
[vba]
Option Explicit
Sub FillData(Target As Range)
Dim Crit1 As String, Crit2 As String
Dim FirstAddress As String
Dim i As Long, Col As Long
Dim c As Range
Crit1 = Cells(Target.Row, 3)
Crit2 = Cells(Target.Row, 4)
Col = Target.Column
For i = 1 To 3
FirstAddress = ""
With Worksheets(i).Columns(3)
Set c = .Find(Crit1, LookIn:=xlValues, MatchCase:=True)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If c.Offset(, 1) = Crit2 Then Sheets(i).Cells(c.Row, Col) = Target
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next
End Sub
[/vba]

[Silver Fox]

Wow, that is pure genius and would have taken me about three months from where I am now! Thank you very much indeed, mdmackillop.

[mdmackillop]

It seems that way at the start!
Always remember that all this VBA code comes down to a series of simple steps, and the the VBA Help file does give a lot of assistance. In this case, the whole of the Find routine was copied from Help; I added in the bit to check the adjoining cell and the bit to loop three worksheets. Triggering it from the worksheets is a little more "advanced", but as you see, is very basic code.

[Redness86]

mdmackillop,

I have a similar problem and I need your help too please.

My source and target are different workbooks. When I update the cost of an item in source.xls (12 sheets), I'd like the new cost to be reflected anywhere that item is found in target.xls (26 sheets).

The sheets in both workbooks have the following setup:

ColA = Qty
ColB = PartNum
ColC = PartDesc
ColD = UnitMeasurement
ColE = UnitMaterialCost
ColF = UnitLaborCost
ColG = UnitEquipCost
ColH = ExtMaterialCost
ColI = ExtLabor Cost
ColJ = ExtEquipCost

The criteria to find will be in columns B and C. Updates will be made in columns E-G, and target.xls will be open. How would you adapt your code for this? Please help.

Thanks in advance!

redness

[mdmackillop]

Hi Redness
Happy to have a look at this, but please post in a new question with a reference here.
MD