Autopopulating multiple sheets from one main sheet

HayleyN - Updated on Nov 27, 2017 at 08:09 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Nov 27, 2017 at 10:17 PM

I am trying to create a spreadsheet that uses one Master sheet (called Master), and then populates other worksheets with the data from that main sheet. I want to use Column C 'Business Owner' as the data that determines which subsheet that row goes into. So, if I enter 'David' into Column C, I want all data from that row (columns A-T) on the Master sheet to copy into the worksheet called 'David', and same for 'Veronica', 'Celeste' etc. This is a working document that will be updated by multiple users, so if any updates are made to the rows at any time, I want all of those updates to copy across to the corresponding subsheet whenever they are made. I'm currently using the below code, which works to some degree; it copies the row to the correct sheet when I enter a value in row C, but if I update the row in any other column, the updates doesn't copy across.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("C:C")) Is Nothing Then Exit Sub

If Target.Value = "David" Then
Range(Range("A" & Target.Row), Range("AU" & Target.Row)).Copy _
Sheets("David").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

I'm very new to Macros so any help would be much appreciated!

Many thanks.

1 reply

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Nov 27, 2017 at 10:17 PM
Hello Hayley,

Following is a code that should work for you:-

Option Explicit
Sub TransferData()

        Dim ar As Variant
        Dim i As Integer

Application.ScreenUpdating = False

ar = Sheet1.Range("C2", Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp))

For i = LBound(ar) To UBound(ar)

Sheets(ar(i, 1)).UsedRange.Offset(1).ClearContents

With Sheet1.[A1].CurrentRegion
           .AutoFilter 3, ar(i, 1)
           .Offset(1).EntireRow.Copy Sheets(ar(i, 1)).Range("A" & Rows.Count).End(3)(2)
     End With
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub

The code needs to be placed in a standard module and assigned to a button.

Following is the link to a sample file for you to play with:-

In the sample, if you click on the "UPDATE" button, you'll see that each row of relevant data for each individual in Column C will be transferred to the individual's sheet. If you change any data in any column and then click on the button again, the data in the individual's sheet will be updated/refreshed.

I hope that this helps.