How to Keep Formatting and Locked Cells When Splitting Excel Sheets with VBA

Описание: Discover how to enhance your Excel VBA macro to maintain formatting, including row heights and column sizes, while also preserving locked cells during the split process.
---
This video is based on the question https://stackoverflow.com/q/77989745/ asked by the user 'user22737370' ( https://stackoverflow.com/u/22737370/ ) and on the answer https://stackoverflow.com/a/77992051/ provided by the user 'taller' ( https://stackoverflow.com/u/22192445/ ) 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, comments, revision history etc. For example, the original title of the Question was: Is there a way to keep formatting (rows and column size) and locked cells on a macro?

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.
---
Enhancing Your Excel VBA Macro: Keeping Formatting and Locked Cells

If you work extensively with Excel, you might have encountered the need to split a large worksheet into multiple workbooks. However, what if that process compromises the formatting, including row heights, column widths, and locked cell settings? In this guide, we will address how to enhance your Excel VBA macro so that you can maintain all necessary formatting while splitting your workbook.

The Challenge: Losing Formatting and Locked Cells

When you split a worksheet using a macro, it often results in a loss of formatting that can mean hours of manual adjustments afterward. This issue is especially frustrating when you have defined row heights, specific column widths, or locked cells to protect sensitive information. The initial concern is how to have a macro that keeps these elements intact during the split. Let’s take a closer look at how to solve this problem.

The Solution: Updating Your VBA Code

To retain the formatting during the workbook split, you can modify your existing VBA code with several key adjustments. Below are the main strategies you’ll employ:

Copy entire rows to retain row height.

Use xlPasteColumnWidths to maintain column width.

Freeze panes: Although this cannot be copied directly, you can set it for the new workbook.

Locked cell status: Ensure that the locked status is preserved in the new workbooks.

Here’s the updated VBA code with comments for clarity:

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

Key Changes Explained

Copying Entire Rows: By copying the entire row, you ensure that the row height is maintained in the new workbook.

Maintaining Column Widths: The use of xlPasteColumnWidths allows for the preservation of custom column widths which are vital for presenting data neatly.

Locked Cells: The inclusion of methods to lock all cells while allowing specific columns (like T, U, V) to be editable helps secure sensitive data effectively.

General Workbook Protection: Protecting the workbook with a password ensures that only authorized users can unlock and edit specific cells or sheets.

Conclusion

By following the steps outlined above and updating your VBA macro appropriately, you can efficiently split Excel sheets without losing critical formatting and locked cell settings. This not only saves you time but also ensures that your spreadsheets remain professional and secure. If you have further questions or need assistance, feel free to reach out for help!