Formula Errors

How to Increment Values Based on Worksheet Order When Copying Sheets

Partner Editor Sep 30, 2026 868 views

Question details

The user wants to automatically increment a cell value based on the previous worksheet's value when copying a sheet, without having to manually update the formula references each time.

How to Increment Values Based on Worksheet Order When Copying Sheets

Product: Spreadsheet
Device & OS: not provided
Scenario: Copying worksheets in a workbook to create a sequential series of data, logs, or numbered records across multiple sheets.
Observed behavior: Copied sheets retain the static reference to the original sheet (e.g., =Sheet1!A1+1) instead of dynamically updating to reference the immediately preceding sheet.

Before you start

Verify that your workbook is saved as a Macro-Enabled Workbook (.xlsm) if you plan to use VBA, and ensure that your sheet tabs follow a consistent naming convention.

Solution 1Recommended

Use a VBA Custom Function to Reference the Previous Sheet

Since there is no native function to dynamically target the previous worksheet, creating a simple User Defined Function via VBA is the most reliable method.

By writing a short macro, you can create a custom formula that always looks at the worksheet immediately preceding the active one, no matter what the sheet is named.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module'. A blank code window will appear.

Enter the VBA Code

Type the following code into the module window: Function PrevSheet(Rng As Range) Application.Volatile PrevSheet = Application.Caller.Parent.Previous.Range(Rng.Address).Value End Function

Apply the Custom Formula

Close the VBA editor. On your new worksheet, select a cell and enter the formula =PrevSheet(A1)+1 to increment the value from cell A1 of the preceding sheet.

Use a VBA Custom Function to Reference the Previous Sheet

Macro-Enabled Workbook Required: You must save your file as a Macro-Enabled Workbook (.xlsm) for the custom function to work properly after closing.

Solution 2

Manually Update References Using Find and Replace

If you prefer not to use macros, you can quickly update the sheet reference in your formulas using the Find and Replace tool immediately after copying the sheet.

Advanced Spreadsheet Features

Easily Manage Worksheets and Macros in WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for complex formulas, dynamic cell referencing, and VBA macros, allowing you to easily increment values across copied sheets just like in Microsoft Excel.

1. Open your Workbook: Launch WPS Office and open your existing spreadsheet file in the WPS Spreadsheet application.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon and click on the 'VBA Editor' icon.
3. Add Custom Macros: Insert a new module and write your custom previous-sheet macro to enable dynamic referencing.
4. Apply Formulas Instantly: Return to your worksheet and use your new custom formula to increment values automatically when copying sheets.

Fully compatible with Microsoft Excel file formats, including Macro-Enabled Workbooks (.xlsm).Built-in Developer tools and VBA editor for custom worksheet functions.Lightweight architecture for fast processing of multi-sheet workbooks.Free and intuitive interface for seamless spreadsheet management.

Frequently Asked Questions

Is there a built-in function to reference the previous sheet without VBA?

No. Neither Microsoft Excel nor WPS Spreadsheet has a native function that automatically references the immediately preceding sheet. You must either use a VBA macro or manually update the references after copying.

Why does my formula still reference Sheet1 after I copy it?

When a worksheet is copied, external sheet references within formulas (such as Sheet1!A1) are treated as absolute sheet references. The system retains the original sheet name rather than dynamically assuming you want to reference the sheet that comes before it.

Can I use the INDIRECT function to reference the previous sheet?

Yes, but it is highly complex. You can use INDIRECT if you have a strict, sequential naming convention (like Day1, Day2) and extract the current sheet name using the CELL function, then subtract 1 to build the previous sheet's name as a string. However, using a custom VBA function is generally much cleaner and less prone to errors.

Excel & Data Problems