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.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,290
Office Version
2013
Platform
Windows
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
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top