Assigning/Applying Variables to Copy/Paste Columns

**Gard5096** · 12-05-2012, 02:13 PM

Hello Macro Veterans,

I am trying to write a macro that filters a worksheet, and then copies over certain columns to another worksheet in the same workbook. I want to assign variables because new columns are frequently added, changing the coordinates of other column titles. As of now, I have the variables working for the filtering part, but I am not sure how to create/apply variables to the part of the code that grabs the column and pastes it into another worksheet. I have placed dotted lines around the part of the code that I am having trouble with. It is currently hard-coded with the U2:U, but variables are necessary for flexibility.

The worksheet i want to copy from is "MOD"
The worksheet i want to copy to is "NO WELCOME OR 30MDL"

Hope someone can help, thank you.

Nick

Please Login or Register  to view this content.

**Gard5096** · 12-05-2012, 06:44 PM

Does nobody know how to do this?

**JBeaucaire** · 12-05-2012, 09:50 PM

A copy command inherently will ignore rows hidden by an AutoFilter. So you don't need to worry about setting the range. Use the whole range and only the visible rows will be copied.

You don't even need the SpecialCells function.

Please Login or Register  to view this content.

Are you implying column U itself needs to be variable? What method would you employ for finding that column first?

**Gard5096** · 12-05-2012, 10:36 PM

Jerry, thank you for replying.

There are going to be 40+ columns in the worksheet, and after filtering 2-3 of them, I will need to copy entire columns. Some of the cells in these columns that are being copied are blank, which is why I used the special cells function. I need to copy all null and not null cells in the desired columns, is this function still not necessary?

As for the column U being a variable, I believe that's what I want to do. Each column has a title is the first row, but new columns may be added periodically. I want to identify the title of the column I need, and then copy the all cells in that column from row 2 until the bottom of the data set.

The purpose of this is to not have to change the coordinates from U2:U to V2:V when new data is added in column F or L.

I hope this explains it a little better. Let me know if you need more clarification.

Thank you,

Nick

**JBeaucaire** · 12-06-2012, 03:25 AM

So, use the technique you've already used to find the first two columns to find the 3rd. Then use the CELLS() method instead of the RANGE() method to call the values from that column because CELLS() lets you use a number for the column if you want.

Maybe this is all you need:

Please Login or Register  to view this content.

**Gard5096** · 12-06-2012, 04:58 PM

this worked great, you are my hero!! thank you.

**Gard5096** · 12-06-2012, 05:03 PM

I actually have one more question on your coding. Why did you put the range as ("1:1") instead of ("A1:ZZ1")?

i = Application.WorksheetFunction.Match("MGR_NAME", Range("1:1"), 0)

Thank you

**JBeaucaire** · 12-07-2012, 11:59 AM

I have a habit of not writing things that are "version" specific if it can be helped. Excel 2003 doesn't have a column called ZZ. Also, there's no reason to define a range of columns when pointing the the entire row 1 (however many columns that may be) does the job.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.