Help with Print and SaveAs Macro

Dear All,

Please, I need help with a SaveAs macro that will Print and SaveAs.

1. I have designed a form on a worksheet which needs to be populated with data from other worksheets in the workbook.

2. When that is complete, the form needs to be printed out and saved in a folder named ‘COMPLETE’ on the desktop.
3. The worksheet that houses the form has a number of command buttons used for some other purposes, but the PRINT AREA of the form has been defined as B9:M64

4. What I need is for the form to be printed out (when complete) based on the defined PRINT AREA and then SavedAs in the COMPLETE folder (using the name on Cell D7.

5. The saved copy should be the form that is printed out (as an ordinary form and not as a macro-enabled form/file)

6. I found a PRINT macro and SAVEAS macro differently. I combined both of them and tried to tweak it to give me the result that I desire. I ended up with the following:

Please Login or Register  to view this content.

7. The Print part on its own works OK. However, the SaveAs part does not work correctly. Tested on its own, the SaveAs part saves the entire worksheet (including all the command buttons on the sheet and the formulas in each of the cells, the worksheet tab name, etc.) That is not what I want.

8. I want what is printed to be just what is also SavedAs, please.

I should be grateful for help with the above (especially the SaveAs part) to enable me to achieve the outcome that I desire.

Thanks all for your help.

Newqueen

Can someone, please, kindly help with this thread.

Thanks

Newqueen

What do you mean by "an ordinary form"? excel.xlsx? PDF?

Hi Jolivanes,

Thanks for your kind response. I am sorry if my thread was not clear on that.

I mean Excel.xlsx, NOT PDF.

When the Form is printed out, it just prints out as a Form designed. The printed output does not come with the command buttons on the sheet etc. It is just the defined Print Area (i.e. the Form itself) that is printed. This is also what I want saved, if it is possible. I suppose that would be Excel.xlsx.

Thanks for your help.

Newqueen

For saving, this should work.

Please Login or Register  to view this content.

Thank you Jolivanes for the Save macro that you provided above. Using my baby knowledge in vba, I added it to the 'Print' macro and came up with the following:

Please Login or Register  to view this content.

1. Unfortunately, the 'Saving' part of the macro does not give me the desired outcome.

2. The 'Print' part of the macro still seems OK when I do a Print Preview. It prints only the defined Print Area of the Worksheet i.e. B9:M64 of the Worksheet.

3. But the added 'Saving' part saves the entire worksheet, including the command buttons and tab name of the worksheet.

4. I am thinking that if the PrintArea B9:M64 is also to the 'Saving' part of the macro, it will produce the desired outcome, i.e. the macro will save only the area of the worksheet that is printed which is the same area that should be saved.

5. I do not know how to add the defined Print Area to the 'Saving' part.

6. If it is possible to add that, please could you kindly help with that. I am confident that it should solve the problem.

In any case, I rely on your superior opinion in these matters.

Thanks for all your help.

Newqueen

Try on a copy of your original (different name).
If the outcome is what you have in mind, code can be cleaned up I think.

Please Login or Register  to view this content.

Hi Jolivanes,

Thanks for all your time and help. I am grateful.

I have tried the macro that you provided in Post #7 above. Unfortunately, the outcome is still not quite what I need:

1. The macro deletes the command buttons on top - including, unfortunately, the company logo.

2. It still does not save the defined Print Area which is the area that is Printed out when a print-out is desired.

3. To clarify exactly what I need, I have attached a SAMPLE2 Excel file which contains a mock template of a designed form.

4. As you will see from the form, it has a defined Print Area which is C5:O35.

5. If you do a print-out of the form, it will get a print-out only of the area C5:O35.

6. That same area that is printed is the exact area that I would like saved, if it is possible.

I hope that with the attached Sample2 file, I have been able to make clearer what I am requesting for.

Thanks for everything.

Newqueen

I hope you're not too surprised that it does not do what you want.
In my code the print area is set to B9:M64 as you have in Post #6.
Now you say it should be C5:O35.
I can't find it anywhere where you ask for deleting everyting except a logo.
Need to know the name of the logo so it can be exempt. (Picure 1 or Our Company Logo or whatever)
The file should be named as the text in D7. Nothing in D7.
Do not use merged cells. Use Center Across Selection instead.

If you only supply half the information or wrong information you'll never get a proper answer.

See attached

Hi Jolivanes,

Thanks heaps for your kindness and patience. That is exactly what I need. Your kindness and expertise are very much appreciated.

Again, thanks very much.

Newqueen

Thank you for letting us know that all is as you requested.
Good luck and enjoy the weekend