Automatically set Dynamic Print Area through Macro/VBA

[stratloveslenny]

I'm trying to make the worksheet called "Medications" have a dynamic print area. I've tried to edit the Print_Area through "Define Name," however, a problem with this is that it auto-deletes itself whenever anyone accesses Page Setup. Also, I notice that settings in the page setup (page orientation, scaling fitting pages wide X tall, etc.)

I feel like the best option will be a Macro/VBA to manually correct these issues. I hope someone can help. I basically need these things to happen:
1) On the "Medications" worksheet, the worksheet's print width should start from Column C and end at Column H.
2) On the "Medications" worksheet, the worksheet's print height should adapt to the data. Meaning, if there's an entry in cell C12, the print area = C1:H12. If there's an entry in cell F20, the print area = C1:H20.
3) "Front Page" should be in portrait, "Labs" should be in portrait, "Medications" should be in landscape, "Assessment Data" should be in portrait, and "Care Plan" should be in portrait. The Medications worksheet is the only worksheet that needs to have a dynamic print range. The others can stay static as they are.

Please let me know if you have any suggestions.

ThanksCare Plan Template.xlsx

[davesexcel]

There is nothing in the attachment.

[stratloveslenny]

Edited. Sorry about that!

[davesexcel]

Run this before you print.

Please Login or Register  to view this content.