Stop duplicate saving

Hi,
I've created the following VBA code to print an excel worksheet to PDF which works ok, my only problem is that it also saves the excel sheet as the new PDF file name, how do I stop this ? (I still need the code to generate the PDF file names as cell E6 and the date).

Please Login or Register  to view this content.

Thanks for any tips

This is working just like a normal Save As ... menu selection would work. I suggest that you automatically close the new workbook that you just saved and reopen the original one.

1. Put the name of the file of the active workbook in a variable
2. do the SaveAs like you are doing
3. Close the active workbook
4. Open the file whose name you retained.

You should be good to go. Before you start this sequence, you need to decide whether the current data in the workbook needs to be updated on disk.

Thanks Blane,
That would work ok, but it keeps saving the file, so I would end up having lots of copies with different names.

I am going to generate this report aprox 500 times per month so that woud be 500 copies !

Any other suggestions ?

You shouldn't end up with multiple copies unless you change the file name.

How about using the SaveCopyAs method of the Workbook Class? I haven't tried it, so I don't know if it will save files as PDFs or not.