Autopopulating multiple sheets from one main sheetClosed

Question

Hello, 

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.

vcoolio · Answer

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)
           .AutoFilter
     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:-

http://ge.tt/2XK3gVn2

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.

Cheerio,
vcoolio.

Autopopulating multiple sheets from one main sheet

1 response

Similar discussions

Subscribe To Our Newsletter!