export filtered data (including header row) to new workbook, maintain column width

Hi All,

I am new to VB and this forum.

I am working with an Excel worksheet which lists the data to many different suppliers. I cannot send this spreadsheet out without some sort of manipulation to ensure that a supplier does not see the other suppliers data. So I am using the autofilter to sort out the rows of data that pertain to a specific supplier, then copy and paste into a new WB before sending - a very time consuming task.

I would like to create a Macro that copies the header rows (10 rows prior to filtered data) and filtered results to a new workbook, which I can then save with a new file name and send to the supplier. The copied columns should maintain the format including the width.

I found this Macro from royUK (see link below) which works great for exporting, however, it doesn't include the header rows and the column width defaults to 8.43.

http://www.excelforum.com/excel-prog...ng-macros.html

I've modified the code to make it work for Excel 2010, however, failed miserably trying to make changes to copy header rows and to maintain the column width. Any help is greatly appreciated.


Sincerely,

Rene

Can anyone help with this, please? The macro I currently have is as follows:

Please Login or Register  to view this content.