Adding a loop around current code

[gazpol]

In short, I have a sheet with about 30 different columns in. The order of these columns can vary from month to month.

I also have a table which has a list of the column names in, but in a specific order.

The following code works fine for one column (I replace CUSTOMNAME with the column header), but rather than doing this 30 times is there a way to loop through all the columns based on the ordering and names in my separate table?

Thanks guys

    'Filter column to show Y's
    Dim rngSL As Range
    Set rngSL = Range("A1:CI" & Cells(Rows.Count, "A").End(xlUp).Row)
    SL = WorksheetFunction.Match("[B]CUSTOMNAME[/B]", rngSL.Rows(1), 0)
    rngSL.AutoFilter Field:=SL, Criteria1:="Y"
    'Copy LOCATIONID and MAINSERVICE into CQC Matched sheet and paste at the bottom
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("CQC matched").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Offset(1, 1).Select
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "[B]CUSTOMNAME[/B]"
    'Delete out the rows which have just been matched
    Sheets("CQC working").Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    'Remove filter
    ActiveSheet.ShowAllData

 

[Dryver14]

You could loop through all the names to set customname to change but as things stand it would copy over itself each time so you would only see the results from the last filter on the page

 

[gazpol]

Would the bit of code in the middle not solve that?
Selection.Copy
Sheets("CQC matched").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Dryver14]

Yes it would, sorry,

Dim rngSL As Range
Dim i As Integer



For i = 1 To 30
Cells(i, 26).Select ' or wherever you want to place your list (column Z in this case)
CUSTOMNAME = Cells(i, 26).Value





Set rngSL = Range("A1:CI" & Cells(Rows.Count, "A").End(xlUp).Row)
SL = WorksheetFunction.Match("CUSTOMNAME", rngSL.Rows(1), 0)
rngSL.AutoFilter Field:=SL, Criteria1:="Y"
'Copy LOCATIONID and MAINSERVICE into CQC Matched sheet and paste at the bottom
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("CQC matched").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Offset(1, 1).Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "CUSTOMNAME"
'Delete out the rows which have just been matched
Sheets("CQC working").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
'Remove filter
ActiveSheet.ShowAllData




Next


End Sub

 

[gazpol]

Do I also need to remove the quotation marks from "CUSTOMNAME"?

Sorry this is probably a really dumb question but when you put

For i = 1 To 30
Cells(i, 26).Select ' or wherever you want to place your list (column Z in this case)
CUSTOMNAME = Cells(i, 26).Value

my table is in a separate sheet called lookup, cells A1 to A32, how do I amend that?

Thank you so much, this is much more involved than anything I've done before!!

 

[Dryver14]

I've change the column from Z to A and yes you need to remove the quotation marks from CUSTOMNAME.

If you have option explicit set then you will need to dim CUSTOMNAME as string

Dim rngSL As Range
Dim i As Integer



For i = 1 To 32
Cells(i, 1).Select ' or wherever you want to place your list (column Z in this case)
CUSTOMNAME = Cells(i, 1).Value





Set rngSL = Range("A1:CI" & Cells(Rows.Count, "A").End(xlUp).Row)
SL = WorksheetFunction.Match(CUSTOMNAME, rngSL.Rows(1), 0)
rngSL.AutoFilter Field:=SL, Criteria1:="Y"
'Copy LOCATIONID and MAINSERVICE into CQC Matched sheet and paste at the bottom
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("CQC matched").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Offset(1, 1).Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = CUSTOMNAME
'Delete out the rows which have just been matched
Sheets("CQC working").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
'Remove filter
ActiveSheet.ShowAllData




Next

 

[gazpol]

Sorry I don't think I explained it properly.

For i = 1 To 32
Cells(i, 1).Select

works fine if my table is in the same sheet as the rest of the data, but my CUSTOMNAME list is in a different sheet named 'lookup'. So just for that step I need it to find Cells(i, 1) in Sheets("lookup"), but I'm not sure how to do that.

Thanks again

 

[Dryver14]

Worksheets("Lookup").Cells(i, 1).Select

 

[gazpol]

Ah, it's that simple. Thanks again, think I'm all sorted now