Excel Macro: Set Print Area and Saved as PDF (each Worksheet)

Hi excelforum Experts,

I have a separate excel macro workbook that contains two buttons named "Consolidate" and "Print PDF" respectively. I would like to focus on the "Print PDF" button since the other button works fine though it is not incorporated into the excel macro that I have mentioned or as attached.

I am having an error upon running the macro as shown on screenshot below and I could not find what's wrong with the code or I might overlooked some codes here. I appreciate if there could be someone that would help me figure these things out. Please see screenshots below for reference:

Run-time Error.png

Working VBA Code.png

Here are the factors that I need to consider in order to arrived at my desired output. Please see also attached workbooks for reference:

1.) The "Print PDF" button will work on the other open/ active workbook. This workbook is an output of my "Consolidated" button.
2.) Each worksheet has fluctuating number of rows and columns, so I need to Set Print Areas ALL worksheets before converting to PDF.
3.) Each worksheet name will be used as file name and all worksheets will be saved to my desired file path.


Warmest regards,
Arnel

Hi davesexcel,

Thanks for taking time answering my query.

I am sorry if I made you confused but what I am trying to print into PDF after setting the print range is the "Raw Data" workbook which contains five(5) worksheets. The other workbook named "Print to PDF Generator" is the one I needed to run the macro and this should not be formatted nor be printed.

I tried running the macro adding the code that you have provided but I am still getting the same error as shown on screenshots on my previous post. I appreciate if you could please help me figure out and fix the missing pieces. Thank you!

Warmest regards,
Arnel

Hi bakerman2,

Thanks for your immediate feedback!

I replaced the existing codes that I have with the codes that you provided but I am getting an error shown on screenshot below.

Run-time Error.png

Working VBA Code.png

I appreciate if you could please help correct these codes for me to come up with my desired output. Thank you so much!

Warmest regards,
Arnel

Hi bakerman2,

Thanks for your feedback!

I already corrected the code but still I am getting an error. There were set of codes that were highlighted in yellow after clicking on the "debug" button that appears on my screen. Please see screenshot below:

Run-time Error.png

Warmest regards,
Arnel

Hi bakerman2,

I am sorry for the inconvenience and thank you for taking the time in helping me resolve this issue. However, an error keeps on appearing as shown on screenshot below:

Run-time Error.png

I tried researching online with regards to the same error but I could not find any solutions yet. Does the below applications affect the existing codes we have?

1.) MS Excel 2007
2.) CutePDF Writer

I appreciate your help. Thanks!


Warmest regards,
Arnel

Hi davesexcel,

Thanks for coming back!

Please refer to the screenshot below for the error that I am getting:

Run-time Error.png

Please Login or Register  to view this content.

The codes above were highlighted in yellow after clicking the "debug" option.

Warmest regards,
Arnel

Hi bakerman2,

I am attaching the workbooks that I am working with for your reference.

It looks like the error comes from the "ExportAsFixedFormat" portion.

I would like to refresh the flow of the macro that I am going to accomplish with the help of you. After clicking the Print PDF button from the Print to PDF Generator workbook, the macro should run with the following steps:

1.) Go to the "Raw Data" workbook and Set Print Area of each worksheet (I always have 10-50 worksheets with fluctuating no. of rows and columns).
2.) Print each worksheet as PDF.
3.) Save each worksheet on my desired folder which will be at the range C6 of Print to PDF Generator workbook.
4.) Save and Close the Print to PDF Generator workbook.
5.) Open the location of the folder which contain the PDF files (output).

I am going to review all the codes that you have provided and will take notes. This will help me understand the process that can be used in the future.

Thanks for your patience and kindness.

Warmest regards,
Arnel

Hi bakerman2,

I am still getting an error in the line shown below:

Please Login or Register  to view this content.

By the way, Range("C6") is on the "Print to PDF Generator" workbook. We may need to activate this workbook after the other one (i.e. "Raw Data" workbook). Thanks!

Warmest regards,
Arnel

Hello there,

I am glad to inform you that the codes that you both provided worked fine upon testing with the other computer. I am assuming that it was because of the PDF application that was installed on my computer which was CutePDF. I tried testing the code with the other computer which was using the Bullzip PDF and it worked well.

For the sake of the future reader of this thread, may I know what PDF applications you both are using that worked fine? I will try to use the codes with the other PDF applications that will not be mentioned on this thread and I will find out what applications will worked well and will not.

By the way, I bumped into the website shown below and found some relevant information with regard to this thread. This could be of great help.

https://www.excelguru.ca/content.php...age=2#comments

I will be marking this thread as "SOLVED" once I got your responses. Thank you so much for your help and efforts. Cheers!

Warmest regards,
Arnel