Auto-Populating Rows from Master Sheet into Other Sheets

lizadams1194 - Mar 11, 2021 at 04:44 PM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Mar 11, 2021 at 08:20 PM

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!

1 response

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Updated on Mar 11, 2021 at 08:27 PM
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)
                End With
        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:-

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.


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.