auto populate from master to individual sheets Closed

Question

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

vcoolio · Answer

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.

Auto populate from master to individual sheets

1 response

Similar discussions

Subscribe To Our Newsletter!