Скачать или смотреть Set Page Properties in Excel for a New Workbook with VBA

Learn how to automatically set page properties in Excel when creating a new workbook using VBA to avoid scaling issues when printing.
---
This video is based on the question https://stackoverflow.com/q/63005624/ asked by the user 'Marcia Jones-Smythe' ( https://stackoverflow.com/u/13966450/ ) and on the answer https://stackoverflow.com/a/63005676/ provided by the user 'Andreas' ( https://stackoverflow.com/u/11971785/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to set the page properties when a new workbook is created?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Set Page Properties When Creating a New Workbook in Excel Using VBA

Creating a new workbook in Excel and attaching it to an email can be a straightforward task, especially with the help of VBA (Visual Basic for Applications). However, one common problem users encounter is that the newly created workbook doesn't have the desired page setup. This often leads to inconvenient scaling issues when printing. Luckily, you can automate the process of setting these properties through VBA, which will ensure that your printed workbook appears just as you intended.

Understanding the Issue

When a new workbook is created in Excel, it may not automatically adjust its page properties (like scaling or print areas). As a result, when you try to print your spreadsheet, it may not fit correctly on the page, leading to multiple pages being printed for a single sheet. This can be frustrating, especially if you're trying to share the workbook via email.

The Solution

To resolve this, you need to insert code into your existing VBA script that specifies the print area and sets the scaling options automatically when the new workbook is created. Here’s how to do it step-by-step.

Step 1: Setting Up Your VBA Environment

Make sure you have access to the Visual Basic for Applications editor in Excel. You can usually open it by pressing ALT + F11 in Excel.

Step 2: Modify Your Existing VBA Code

You will need to integrate the following code snippet into the section of your script where you are copying data to the new workbook. Here's a refined version of your existing code with the necessary modifications highlighted.

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Key Code Adjustments Explained

Print Area: .PageSetup.PrintArea = "$A$1:$Z$12" defines the area of the sheet that you want to print. Make sure to adjust the range according to your data.

Scaling Settings:

Zoom: Setting .PageSetup.Zoom = False disables the automatic zoom scaling.

Fit to Page: Using .PageSetup.FitToPagesTall = 1 and .PageSetup.FitToPagesWide = 1 ensures that the content of your sheet will fit on a single page in both dimensions, preventing unwanted page breaks.

Conclusion

By integrating these adjustments into your VBA code, you can streamline the process of sending Excel workbooks via email while ensuring that they are formatted correctly for printing. This not only saves time but also improves the professionalism of your documents, as they’ll appear as intended when opened by the recipients.

Happy coding, and enjoy creating flawless spreadsheets!