Move Data from Master List to Sheets with City Name

[meggenm]

I have a Master worksheet that has both Dallas and Austin listed in the City Column. I tried to use an "if" formula to populate the Dallas worksheet with the row information that shows Dallas as the city and one to also populate the Austin worksheet with all Austin row information.

I am fairly new at Excel so really appreciate your help. This is pretty fabulous to be able to get help from professionals!

Thanks!!



Short Sample Workbook attached.

[jolivanes]

Depending on the amount of data you have. If you have a lot, AutoFilter might be better.

Sub Maybe()
Dim c As Range
    For Each c In Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)
        c.Offset(, -2).Resize(, 4).Copy Sheets(c.Value).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next c
End Sub

[xor]

If you do not have an awful lot of data you might want to try an array formula.
Please note that Master!A25 is named d and Master!C2:C5 is named City.

[jolivanes]

This might be faster if you have a large amount of data.
See attached

Sub With_AutoFilter()
Dim cityArr, i As Long
cityArr = Array("Dallas", "Austin")
    Application.ScreenUpdating = False
    With Range("A1").CurrentRegion
        For i = LBound(cityArr) To UBound(cityArr)
            .AutoFilter Field:=3, Criteria1:=cityArr(i)
            .Offset(1).Copy Sheets(cityArr(i)).Cells(Rows.Count, 1).End(xlUp).Offset(1)
        Next i
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub