Template Issues

How to Create a Reusable Excel Template for New Worksheets

Camila Milosovich Sep 30, 2026 869 views

Question details

The user wants to configure a workbook so that any newly created worksheet automatically inherits a predefined layout, including headers, footers, rows, columns, and titles, instead of being completely blank.

How to Create a Reusable Excel Template for New Worksheets

Product: Excel
Device & OS: not provided
Scenario: Setting up standardized forms or reports within a single workbook where identical formatting and structural elements are required for every new entry or page.
Observed behavior: By default, clicking the standard 'New Sheet' button creates a completely unformatted, blank worksheet rather than applying the desired custom template design.

Before you start

Design your master worksheet with all necessary headers, logos, formatting, and locked cells before attempting to automate the duplication process.

Solution 1Recommended

Automate New Sheet Creation Using a VBA Macro

Use a Worksheet_Change VBA event to automatically copy your master template sheet whenever you trigger a specific action, ensuring exact formatting replication.

Since the default 'New Sheet' button in spreadsheet software always generates a blank page, you must use a VBA macro for a customized workflow. This script can detect a specific cell change, duplicate the formatted sheet, and clear old input data automatically.

Prepare the trigger cell

On your master template sheet, create a Data Validation dropdown list in a designated cell with an option named 'New Sheet'.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor. Locate your workbook in the Project Explorer panel and double-click the template worksheet object.

Insert the Worksheet_Change event

In the code window, select 'Worksheet' from the left dropdown and 'Change' from the right. Write a script that triggers when your specific dropdown cell is changed to 'New Sheet'.

Program the copy and clear functions

Add code to copy the active sheet, place it at the end of the workbook, clear the contents of specific input ranges (e.g., Range("A2:B10").ClearContents), and reset the original dropdown value.

Save as a Macro-Enabled Workbook

Go to File > Save As and choose the 'Excel Macro-Enabled Workbook (*.xlsm)' or 'Excel Macro-Enabled Template (*.xltm)' format so the automation continues to work.

Automate New Sheet Creation Using a VBA Macro

Macro Security settings: Ensure macros are enabled in your Trust Center settings, otherwise the VBA script will not execute when you select the dropdown option.

Solution 2

Manually Duplicate the Template Worksheet

If you cannot use macros, manually copying the master sheet is the most reliable way to preserve complex layouts and headers.

Efficiently Manage Templates with WPS

Create and Manage Reusable Spreadsheets Easily in WPS Office

WPS Spreadsheet provides a seamless environment for designing reusable templates and running VBA macros, ensuring your document formatting remains perfectly consistent across all new worksheets.

1. Design your master layout: Open WPS Spreadsheet, design your headers, footers, and data areas, and format the page exactly as required.
2. Access the VBA tools: Navigate to the Developer tab to launch the VBA Editor where you can input your Worksheet_Change duplication script.
3. Save your automated workbook: Save the file as a Macro-Enabled Workbook to preserve your layout automation.
4. Generate new sheets: Trigger your dropdown or macro button to instantly generate a newly formatted sheet ready for data entry.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .xltm)Robust support for VBA macros to automate sheet duplicationAdvanced page layout tools for designing professional headers, footers, and formsLightweight, fast performance with an intuitive user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard 'New Sheet' plus button create a blank page instead of my template?

The default behavior of spreadsheet software is to insert a completely unformatted, blank grid when the '+' (New Sheet) button is clicked. To replicate a specific layout automatically within an active workbook, you must use a VBA macro or duplicate an existing sheet manually.

Can I set a custom worksheet template as the default for the entire application?

Yes, you can save a formatted blank workbook as 'Sheet.xltx' in your application's XLSTART folder. However, this alters the default for all newly inserted sheets globally across all files, which may not be desirable if you only want the template for one specific project.

How do I clear specific input cells when copying the template via VBA?

Within your VBA duplication script, use the ClearContents method on the specific ranges. For example, immediately after the sheet copy command, add 'ActiveSheet.Range("B2:B10, E5:E8").ClearContents' to erase previous inputs while keeping formulas and formatting intact.

Document & File Issues