Copying Filtered Results into a New Workbook using Macro's

Hi,

I'm not a regular Macros user so have very limited experience.

I'm currently have a workbook which refers to lots of different suppliers. I use the auto filter to view specific suppliers, but if I want to send this workbook to a supplier the information on the other suppliers is confidential and therefore the whole book can't be sent.

Therefore I would like a Macro to export the filtered results into a new excel workbook which can then be sent to suppliers.

I realise that there is an advanced filter option which can do this, however I need to make it as simple and intuative as possible as there will be many people using the spread sheet over time. So I would ideally like to put an "export filter results" at the top of the spread sheet.


IS there somewhere you could point me to read up on what's required?


Thanks,

Stephen

Hi Roy,

I have tried this at it seems to return runtime error 438

at this point

rng.Copy newWb.Cells(2, 1)

its probably me, but i thought i could use this code as well

cheers

another Stephen

sorry i changed the 1,1 to 2,1

Hi Roy,

Thanks very much for the reply, some very helpful stuff there.

I can get the selection I need now to a new workbook and have also copied the column widths and formatting accross to. I kept getting a Runtime error 13 for the Save As loop, but have now deleted that as I don't need this to be automatic.

However I need also to copy the row height accross as there may be pictures in some of the cells. Is there any way to do this?


Thanks,

Stephen

Hi Roy,

I am totally new to VB and found this thread here - great stuff. I got the Macro to work in Excel 2010, however, I would like to copy the header rows (10 rows prior to filtered rows) as well, and maintain the column width from the source. Can you please help?

Many thanks,

Rene