Auto Transfer Data from Master Sheet to Several other Worksheets

rdemaray

New Member
Joined
Jan 18, 2017
Messages
1
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:

DateLog #BuildingRep
12/31/161234565Jane Doe
1/4/1778901210Jane Doe
1/9/1734567815Jane Doe
1/16/1790123420Jane Doe

<tbody>
</tbody>


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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
Code:
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.
Code:
Sub t()
Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,722
Members
448,294
Latest member
jmjmjmjmjmjm

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