Save Worksheet to New Excel File

**BrownTeddyBear** · 07-24-2009, 07:32 AM

Hi Everybody,

Thank you everybody for your help so far.

I currently have a worksheet called 'invoice' and I would like to have a button on this sheet which will export the worksheet to a new workbook and then save it.

Each invoice is unique so I would like when the button is clicked to save the invoice with the invoice number included in the file name. I would effectively like to save a new invoice with a new filename so I will have various invoice files. The format will obviously be xls.

I was also looking to create a button which will print the invoice as well.

Is there a macro I can apply to buttons to achieve these?

If you have any suggestions, they would be greatly appreciated.

Kind regards,

Brown Teddy Bear

**highbarger** · 07-24-2009, 08:59 AM

I think this will do the job. This code will name the file with the contents of cell D1. Just change the destination folder listed in the code to something appropriate.

Please Login or Register  to view this content.

**jrussell** · 07-24-2009, 09:02 AM

These should help:
http://www.mvps.org/dmcritchie/excel/saveas.htm
http://www.mvps.org/dmcritchie/excel/pathname.htm

**BrownTeddyBear** · 07-24-2009, 06:21 PM

Hi,

Thank you so much for your response. Your code works an absolute treat!

I appreciate your help.

I'm just wondering and I know this is probably simple, but is there a macro also which I can apply to a button to automatically print an invoice when cicked? I tried recording a macro to print the invoice but nothing worked.

Thanks again.

Kind regards,

Brown Teddy Bear

**BrownTeddyBear** · 07-24-2009, 06:43 PM

Hi,

Sorry about that print macro.

I've worked it out.

Thank you for your help Highbarger and Jrussell with the code and links.

Kind regards,

Justin

**BrownTeddyBear** · 07-25-2009, 03:57 AM

Hi there,

I thought this was resolved but unfortunately I'm getting a 'debug' error everytime I run the macro now.

Here is my code:

Please Login or Register  to view this content.

I have had a great deal of help from so many people on Excel Forum regarding this and I'm so grateful. Obviously, I've got something in the code which is causing the debug message and not letting the invoice sheet save to a new workbook. Does anybody know why this is happening?

Any help with this would be greatly appreciated.

Kind regards,

Brown Teddy Bear

**BrownTeddyBear** · 07-25-2009, 09:09 AM

Hi Everybody,

Ok. I went back to basics and tried implementing the code again and now it works. The invoice saves successfully and there is no error message. The only thing which I think may have been causing the problem was that one of the command buttons was in design mode. That's the only thing which was different.

I'm just wondering, is there a way I can stop the 'debug' error coming up when an invoice is actually duplicated? It gives me the 'save as' option but is there some code I can put in to make sure that 'debug runtime' message doesn't come up after?

Any help would be greatly appreciated as always.

Kind regards,

Brown Teddy Bear

**BrownTeddyBear** · 07-25-2009, 06:07 PM

Hi,

I'm just wondering if somebody can tell me if my query makes sense?

If not, I'll change it. If so, some help would be greatly appreciated.

Kind regards,

Brown Teddy Bear