Auto-Populating Rows from Master Sheet into Other SheetsClosed

Question

Hello, 

I'm attempting to create a distribution list spreadsheet that will have all the names, emails, and agencies in one master sheet, and then have separate sheets for the various distro lists I use (so you can quickly send an email to one group of people selected from the master list). I want the entire row (across columns A-E) to populate in the designated sheet. I also want the ability for the row to be auto-populated into several sheets if necessary (as one person might need to be added to multiple distro lists). 

I've seen a few examples of people doing similar things, but nothing so far is totally what I'm looking for. Any help is appreciated!

vcoolio · Answer

Hello Liz,

I'm not too sure as to how your workbook is set out but here is a code that could help steer you in the right direction:-

Option Explicit
Sub Test()

Dim ar As Variant, i As Integer
        Dim wsA As Worksheet, ws As Worksheet
        Set ws = Sheets("Master")
        ar = Array("Agency 1", "Agency 2", "Agency 3", "Agency 4")

Application.ScreenUpdating = False

For i = 0 To UBound(ar)
           Set wsA = Sheets(CStr(ar(i)))
                With ws.[A1].CurrentRegion
                        .AutoFilter 2, ar(i)
                        .Offset(1).EntireRow.Copy wsA.Range("A" & Rows.Count).End(3)(2)
                        .AutoFilter
                End With
                
                wsA.Columns.AutoFit
        Next i

Application.ScreenUpdating = True

End Sub

This code basically transfers all relevant data to the individual agency worksheets (I've assumed four Agencies in the array used in the code).

Following is a link to a sample workbook with the code implemented and assigned to the "TEST ME" button:-

https://wetransfer.com/downloads/63a412ffe66a2f5b9aeff6e6f71c156020210312011416/49518a

Click on the button to see how it works.

If you need further assistance then please upload a sample of your file to a free file sharing site such as WeTransfer or Drop Box then post the link to your file back here. Please ensure that your sample is an exact replica of your actual file and if your data is sensitive then please use dummy data.

I hope that this helps.

Cheerio,
vcoolio.

P.S. Please advise if you'd like the transferred data to be deleted from the Master sheet or if you'd rather keep all data in the Master sheet and just have the destination sheets refreshed.

Auto-Populating Rows from Master Sheet into Other Sheets

1 response

Similar discussions

Subscribe To Our Newsletter!