Copy pasting columns based on headers

**krash297** · 07-14-2013, 12:38 PM

Hello attached is an excel sheet with a macro to copy paste columns having specific heading. will someone help me concise the macro. Right now its a huge page of codes. I am new to vba and learning by recording and then modifying.

**HaHoBe** · 07-14-2013, 01:01 PM

Hi, krash297,

insetad of switching sheets set object to the sheets. And instead of having multiple codes for each item use an array which holds the values and loop through that array:

Please Login or Register  to view this content.

Ciao,
Holger

**krash297** · 07-14-2013, 01:39 PM

i am new to vba i dont know how to use arrays could you explain me how this works so that i can relate it.

**HaHoBe** · 07-14-2013, 02:13 PM

Hi, krash297,

the array stores the names of the items you want to search. In the code it is filled with the line

Please Login or Register  to view this content.

Any item should be packed in quotes (it it is a string) and be separated by comma. This type of array is zero based which means that the first item will have the number of 0.

Next we do have a loop which will go from the lower bound of the array (as I just stated this will be 0) to the number of items being stored - 1. Instead of writing numbers we use the command LBound (lower bound) and UBound (upper bound) which will free us to alter the code if the number of the items to search for is changed.

Ciao,
Holger