Setting print area (for PDF export) based on conditions

[NG1]

I have a ton of cells and pivot charts that calculate/populate based on data from an Access Database. This data is used to create monthly reports

Every product type has its own calculations and charts, but there are some months where a product is not produced so its specific calculations are zero and its pivot charts are blank

I have code that generates a PDF, but I only want it to generate a PDF for the products that were produced instead of all the products, essentially excluding calculations that are zero

This is where I am having issues

I want to have code that determines a product has been manufactured and add that section to the print area which is used to generate the PDF

I've tried essentially creating check boxes that used conditional IF THEN statements to set the print area, but there are too many products/combinations to create individual IF statements for each scenario

Is there a way to tell excel this is the print area, but if this condition is met (product is created) add it to the print area? Instead of using a replacement effect like an IF statement?


Alternatively is there a way to have excel export and the user can still select the pages via a macro?

-Nathan

[Leith Ross]

Hello NG1,

Welcome to the Forum!

I would have the macro filter the data to exclude zero counts then copy the data to another sheet. This would keep the range contiguous, which is what you want for a Print Area, and could be reused by the macro to create the individual PDF files.

[NG1]

I have headers and pivot charts that I would like to also have exported to PDF, would the macro include those?

I need everything to be on one PDF file, which is where I am running into the issues. I can set print area and just exclude section if I was actually printing or making individual PDF files, but I can get it to make a contiguous range if if any products aren't manufactured (instead I am left with "blank" sections)

I also don't know how I would move sections of the sheet to another sheet if the products vary each month because I wouldn't have a standard cell only the second sheet that the data would flow too

[NG1]

Alternatively (what I am now trying), can I hide the sections of data when they are blank (if statement with essentially a check box when the data is present) and print just the visible cells?

[NG1]

I believe I got everything to work by if statements that hide rows when the calculations are zero, then setting it to only export the print area that is unhidden