Previous Topic Next Topic

Auto populate from master to individual sheets

Closed
MG918 Posts 1 Registration date Monday November 13, 2017 Status Member Last seen November 15, 2017 - Nov 15, 2017 at 11:01 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Nov 15, 2017 at 06:46 PM
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
Related:

1 response

Answer 1 / 1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Nov 15, 2017 at 06:46 PM
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.
0