Auto populate from master to individual sheets [Closed]

Report
Posts
2
Registration date
Monday November 13, 2017
Status
Member
Last seen
November 15, 2017
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,
I have an excel sheet that I would like to auto populate from a master sheet to individual sheets. Basically it is an assignment list that I want to populate to the person I assign it to. It is column A-N. Column A is the project #, and Column C is the person assigned to. I already have sheets created for each individual person. Thank you in advance for any assistance that can be provided.

Thank you

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
212
Hello MG918,

There are a number of ways that this can be done. Following is a VBA code for one method:-

Option Explicit
Sub TransferData()

        Dim ar As Variant
        Dim i As Integer

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

Application.ScreenUpdating = False

For i = 1 To UBound(ar)
         Sheets(ar(i, 1)).UsedRange.Offset(1).ClearContents
         Sheet1.Range("C1", Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
         Sheet1.Range("A2", Sheet1.Range("N" & Sheet1.Rows.Count).End(xlUp)).Copy Sheets(ar(i, 1)).Range("A" & Rows.Count).End(3)(2)
         Sheets(ar(i, 1)).Columns.AutoFit
    Next i
Sheet1.[C1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub


I've assumed that row1 in each sheet has headings and data starts in row2 and that Sheet1 is the Master sheet.

Test the code in a copy of your workbook first. If things don't quite work as hoped, then please upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here. We can then have something to work with and test. Please use dummy data in the sample.

I hope that this helps.

Cheerio,
vcoolio.