Appending data dynamically to sheet

[flaaron]

I have a series of large spreadsheets including thousands of columns. I want to be able to search through all columns and pull out certain columns and move those columns to another sheet for processing.

I do this currently by using the WorksheetFunction.Match function to match and store column positions and then copy the columns as shown below:

'Find the columns I want
esxtime1 = WorksheetFunction.Match("(PDH-CSV 4.0) (EDT)(0)", Rows("1:1"), 0)
counter1 = WorksheetFunction.Match("\\hcsesxicore?\Physical Cpu Load\Cpu Load (1 Minute Avg)", Rows("1:1"), 0)
.
.(shortened for brevity sake)

'Copy the columns to another sheet
Sheets(orgsheet).Columns(esxtime1).Copy Destination:=Sheets("Pivot_Source").Range("A1")
Sheets(orgsheet).Columns(counter1).Copy Destination:=Sheets("Pivot_Source").Range("B1")
.
.(shortened for brevity sake)

My desire is to be able to copy the data into the first empty column instead of having to statically map it because if I have to change the columns I want, I have to redefine the mappings in the copy code.

Ideally I want to throw the copy operations into a For loop, but looking to solve the copy appending issue first.

Thanks!
~Aaron

 

[jbeaucaire]

To copy into the next empty column:

Sheets(orgsheet).Columns(esxtime1).Copy Destination:=Sheets("Pivot_Source").Cells(1, Sheets("Pivot_Source").Columns.Count).End(xlToLeft).Offset(,1)