Auto Transfer Data from Master Sheet to Several other Worksheets

[rdemaray]

Hi all!

I've been researching this for days and cannot seem to find the proper way of doing what I need! I feel like it's pretty simple.....

I have a master sheet where data is entered as such:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Log #[/TD]
[TD]Building[/TD]
[TD]Rep[/TD]
[/TR]
[TR]
[TD]12/31/16[/TD]
[TD]123456[/TD]
[TD]5[/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]1/4/17[/TD]
[TD]789012[/TD]
[TD]10[/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]1/9/17[/TD]
[TD]345678[/TD]
[TD]15[/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]1/16/17[/TD]
[TD]901234[/TD]
[TD]20[/TD]
[TD]Jane Doe[/TD]
[/TR]
</tbody>[/TABLE]


Then I have a worksheet for each log # (there could be several) and I need the data from those line items from the master sheet to feed to specific cells on the other worksheets. How can I get the info that I enter on the master sheet to automatically feed to the other worksheets cells? Can it be a formula or does it need to be a macro? There are no forumlas within the data I want transferred.

Thank you in advance for your help! Any info is much appreciated.

 

[JLGWhiz]

If you use formulas, you would need to put formulas in each of your destination sheets to capture the values entered on the source sheet. If you use code, like that below, you would only need it in the source sheet and it would copy your entries to the destination sheets to the next available row.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("A:D"), Target) Is Nothing Then
    If Application.CountA(Range("A" & Target.Row).Resize(1, 4)) = 4 Then
        Range("A" & Target.Row).Resize(1, 4).Copy Sheets(CStr(Range("B" & Target.Row).Value)).Cells(Rows.Count, 1).End(xlUp)(2)
    End If
End If
Application.EnableEvents = True
End Sub

This code should be copied to the worksheet code module of the source worksheet. To access the code module, right click the sheet name tab then click 'View Code' in the pop up menu. Paste the code into the large code pane, close the VB editor and if not already done, save the workbook as a macro enabled workbook to preserve the code.
What the code does: The code will trigger with any change made on the worksheet, but will only execute the copy action when columns A:D of the same target row have data in them. When all four columns on a row have data, that row will be copied to the next available row on the worksheet identified to the data in column B of the source sheet. The code assumes that the worksheet name will be exactly the same as the data in column B. If not, the code fails and will require modification to make the copy statement effective. If an error occurs and the code does not complete, it will be necessary to reset the event trigger. You can put the code below in your standard code module 1 and run it when events will not fire.

Sub t()
Application.EnableEvents = True
End Sub