Macro to save certain sheets from excel automatically

**Libster78** · 01-22-2010, 06:48 AM

Hi there,

I'm not sure if this is possible but I want to be able to click print and after it has printed, transferred and cleared I would like it to save the purchase order and continuation sheet.

If this is being done on different computers is it possible for it to simply bring up the dialog box and the user save it where they would like?

All help appreciated

Many Thanks

Libby

**Domski** · 01-22-2010, 07:25 AM

Something like this should do the trick:

Please Login or Register  to view this content.

Change the sheet names as required.

Dom

**Libster78** · 01-22-2010, 01:07 PM

Fab thank you
Libby

**Libster78** · 01-25-2010, 10:02 AM

Hi there

Sorry I did do this but it is the sheets that have been copied which I want to save.

is there a change in the code for this?

Thanks

**Libster78** · 01-25-2010, 10:17 AM

I tried

Please Login or Register  to view this content.

but it is not saving the sheets I have copied. how do I change the code?

Thanks in advance

Libster78

**Domski** · 01-25-2010, 10:30 AM

Do you mean you are copying the sheets to a new workbook and then running the code? The code does the copying for you here:

Please Login or Register  to view this content.

Please use code tags when posting code.

Dom

**Libster78** · 01-25-2010, 12:39 PM

Dom,

I am sorry the save seems to be working fine. It must have been me.

Can you have a look at my full code and see if you can see why when I click the print the code stops at NewInvoice1 and says

run time error "1004", Unable to set Locked property of the Range class

It seems to work on its own but not the way I have put it in to the IF code unde PrintInvoice()

Thanks

**Domski** · 01-26-2010, 05:13 AM

I tried running the code and it did work okay without an error for me but would point out a couple things...

I think your Invoice code can be cut down to this:

Please Login or Register  to view this content.

All the locking and unlocking of cells seemed to be unnecessary.

You've used merged cells quite a lot which VBA really doesn't like, I would suggest changing all the merged cells to Center Across Selection as this will throw up less possible errors.

Hope it helps,

Dom