Reorganize data using an array

Question

I have code that I need to shorten in an array or something like that because its taking a long time to run.

I have a copy sheet & paste sheet.

A table is copied to the copy sheet and the macro reads the heading of each column and pastes it to the paste sheet in the correct location. So it basically reorganizes the data to it can be aligned with other tables.

I have 20 columns that get matched, copied and pasted to paste sheet from copy sheet.

ABC = WorksheetFunction.Match("ABC", Rows("1:1"), 0)
DEF = WorksheetFunction.Match("DEF", Rows("1:1"), 0)... ETC.
Sheets("COPY").Columns(ABC).copy Destination:=Sheets("PASTE").Range("C1")
Sheets("COPY").Columns(DEF).copy Destination:=Sheets("PASTE").Range("C2")

Answer 1

Use a variant array for column header labels and destinations.

dim h as long, hdrs as variant, hnum as variant

hdrs = array("ABC", "C1", "DEF", "C2")

with workSheets("COPY")

    for h=lbound(hdrs) to ubound(hdrs) step 2
        hnum = application.Match(hdrs(h), .Rows("1:1"), 0)
        if not iserror(hnum) then
            .Columns(hnum).copy Destination:=workSheets("PASTE").Range(hdrs(h+1))
        end if
    next h

end with

Obviously you aren't going to overwrite the previous paste operation but this follows your original sample code. Be careful copying full columns; you aren't going to be able to paste into any destination that isn't another full column (e.g. C2 as a destination is one cell short of a full column).

If the destinations were sequential columns, you could just offset one destination by h columns.

I threw in some error control on no match by returning application.match to a variant.