Define Array by based on row 1 value instead of column number

  • My current macro identifies the columns I want to output and the order in which to do so as follows:

    columnsToKeep = Array(1, 13, 36, 2, 3, 24, 8, 12)

    I am running into problems because the source data is no longer going to be in the same order consistently. There will always be a heading in row 1 and the values will remain to accurate to the data below so I want to use that value but can't seem to make it work. The result would end up something like:

    columnsToKeep = Array(Item, Sequence, Sales Order Priority, Batch Number, Lot No, Firmed, Header/Line Status, Req Compl Date)

    I realize that I will end up needing to define these but I am not sure how to put it all together.

    Thank you!

  • I don't know what you want to do once when you find the Column but here is an example.
    Change references as required.

    Sub Maybe()
    Dim columnsToKeep, i As Long, sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
        columnsToKeep = Array("Item", "Sequence", "Sales Order Priority")    '<---- expand as required
            For i = LBound(columnsToKeep) To UBound(columnsToKeep)
                sh1.Cells(1, sh1.Rows(1).Find(columnsToKeep(i)).Column).EntireColumn.Copy sh2.Cells(1, sh2.Cells(1, sh2.Columns.Count).End(xlToLeft).Column).Offset(, 1)
            Next i
    End Sub
  • I over simplified. I am defining a range for my source data an the specific columns I want to keep along with output order. Here is a better representation of the code before I go on to apply an advanced filter criteria:

    So I will need to come up with a way to derive the column numbers used in the red lines above based on the column name in row 1 rather than assuming the columns will always be in the same order.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!