Can I Improve This Code?

Hey guys,

I've made a macro that works perfectly, but I'd like to improve the code (for educational purposes).

I've got a macro that sets a filter to a value in a cell, then copies the range to a sheet with the same name as the filter. I've set up variables for each cell, but I want to loop through the copy rather than repeating it.

Instead of repeating the code and changing Group1 to Group2, how can I use a loop to do this?

Thanks,
Lucas

P.S Code below

Dim Group1 As String
Dim Group2 As String
Dim Group3 As String
Dim Group4 As String

Application.ScreenUpdating = False


Group1 = Worksheets("Lookup").Cells(3, "E").Value
Group2 = Worksheets("Lookup").Cells(4, "E").Value
Group3 = Worksheets("Lookup").Cells(5, "E").Value
Group4 = Worksheets("Lookup").Cells(6, "E").Value

' Loop 1

    Sheets("PS Group 09-09-13").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$AO$77772").AutoFilter Field:=41, Criteria1:= _
        Group1
    Sheets.Add.Name = Group1
    Sheets("PS Group 09-09-13").Select
    Range("A3").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets(Group1).Select
    Range("A1").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Sheets("PS Group 09-09-13").Select
    Application.CutCopyMode = False
    Range("AO3").Select

It was challenging to tell what you were really trying to accomplish with all the "Selections" as it gives us no reference. Try this and let me know how close i got:

Sub test1()
Dim ws As Worksheet:    Set ws = Sheets("Lookup")
Dim ws2 As Worksheet:   Set ws2 = Sheets("PS Group 09-09-13")
Dim icell As Long
Dim GroupX As String

Application.ScreenUpdating = False

For icell = 3 To 6
    GroupX = ws.Range("E" & icell).Value
    ws2.Range("$A$3:$AO$77772").AutoFilter Field:=41, Criteria1:=GroupX
    Worksheets.Add(After:=Sheets(Worksheets.Count)).Name = GroupX
    ws2.Range("A3:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Copy Destination:=Sheets(GroupX).Range("A1")
Next icell

Application.ScreenUpdating = True

End Sub

Hey stnkynts,

Great response mate. Although the code didn't quite work, I wasn't after a functioning bit of code anyway. What you demonstrated perfectly was the syntax of how to set the variables and loop through them. Now I can recreate my code with this technique. Not to mention can appropriate it for other uses!

Thanks! Thread marked as solved

Lucas