Export Columns to New Worksheet

I am using the following Macro to copy certain columns to a new Worksheet in a particular order which is working fine however I have two issues:

1 - When the new worksheet is created the column widths are not being exported over so I have go to the new worksheet an manually adjust the width again.

2 - I have a number of columns which will be move when additional columns are added i.e. from column AB to AC which means I have to change the code again.Is there a way to track these so I do not have to change the code again. In row 5 each of the columns has a unique heading.

Please Login or Register  to view this content.

Can anyone help?

Thanks. I actually prefer the second piece of code you provided with the array. The only issue is my headings are on Row 5 so that's where I want the Macro to do the search for the heading and then copy the entire column including Row 1,2,3,4 above the headings i.e.all the data in the column after it finds the heading in Row 5.

I tried this before and now tried it again after getting confirmation from you with the same result.

When the headings are on Row1 the export works fine. When the headings are moved to Row5 and the code is changed to Row(5) the export sheet is created but it is BLANK.

Any suggestions?

This now works fine which is brilliant. The last amendment - I have a large block of columns i.e. from AZ to FC which will never change which I will want in columns FA:JD in the export . I was wondering if I could add these somewhere as AZ:FC so I do not have to enter all the names again. If this cannot be done I will have to add these in manually (108 names). This was quite simple in the code I posted.

If needed I could post a sample.

Awesome that works perfect!!! Before I mark this as complete is there a way of making certain columns in the export hidden. I am sure there is something quite simple.

There are a few columns i.e. 'address', 'city' in the original spreadsheet which I want exporting to a new sheet however I want them hidden in the new sheet.

The plan is to export say Columns A:FC however from within these columns I want certain ones columns hidden when they appear in the export.

Hope that makes sense.