Search for 3 specific columns and copy

**csynic** · 07-05-2011, 04:46 AM

Hi everyone,

I have a worksheet with lots of random rows and columns. I would like to use VBA to find 3 specific columns, copy those columns and paste them into a new sheet. What would the code look like?

Ctrl+f doesn't seem to work very well because after highlighting my first column, i want to search for my next column but excel will run the search in the highlighted column.

I tried using relative references, but again that wouldn't work because the second column I need might be 6 cells to the left on the first worksheet, but 2 cells to the right on the second worksheet. Any thoughts? Thanks in advance!

**TMS** · 07-05-2011, 05:09 AM

Post a sample workbook with some typical data indicating which columns to select and why and where you want them to be copied.

Regards

**csynic** · 07-06-2011, 01:07 AM

hi, here is the sample file. basically i would like to search for 3 specific column titles (doctor, discipline, recovery) in sheet 1 and paste the entire column (along with the column contents) in to sheet 2.

**TMS** · 07-06-2011, 04:43 PM

OK, this works:

Please Login or Register  to view this content.

I am not overly happy with it, in that I have to select the sheets. That said, it seems to do what you have asked for.

There's probably a neater way of finding the column numbers and it's not really scaleable ... but it would easily cope with, say, half a dozen columns.

I'm guessing there's a better way by storing the columns in arrays but that's not my strength.

Lastly, there's no error checking so, if a heading isn't there, the results will be unpredictable.

Regards