Copy Rows from Master List to different Sheets based on a Cell Value

[kazamimi]

Hello, I have a workbook with a few sheets: All Forms, Form A, Form B, Form C, and Form D.

I want a macro that will search column C on the All Forms Sheet, and depending on the Value listed there, Copy the entire row to the relative Sheet.

So, if Row 1 on the All Forms Sheet has “Form B” in Column C, it will copy that entire row to the Form B sheet…if Row 2 says “Form A” in Column C, it will copy that entire Row to the Form A sheet.

I would like to create a button that runs the macro, so that when I click it, it will overwrite the existing rows on the various sheets-effectively updating those sheets with the most current info from the All Forms sheet (the “master sheet”) without creating duplicate entries every time I run the macro…

 

[kazamimi]

Additional info: there are about 300 entries on the All Forms sheet, with about 30-50 Rows that need to be copied to each of the relative sheets (Form A, Form B, etc.)

 

[vcoolio]

Hello Kazamimi,

Try the following:-

Option Explicit
Sub Test()

        Dim ar As Variant, wsAF As Worksheet, wsD As Worksheet, i As Long
        
        Set wsAF = Sheets("All Forms")
        ar = Array("Form A", "Form B", "Form C", "Form D")

Application.ScreenUpdating = False

        For i = 0 To UBound(ar)
        Set wsD = Sheets(ar(i))
        wsD.UsedRange.Offset(1).Clear
                With wsAF.[A1].CurrentRegion
                        .AutoFilter 3, ar(i)
                        .Offset(1).EntireRow.Copy wsD.Range("A" & Rows.Count).End(3)(2)
                        .AutoFilter
                End With
        Next i

Application.ScreenUpdating = True

End Sub

As I don't know the set out of your worksheets, I'm assuming that the All Forms sheet has headings in row1 with data starting in row2.

I hope that this helps.

Cheerio,
vcoolio.