VBA & Macro Problems

How to Automatically Reference the Previous Worksheet in Excel (VBA)

Maira Mehtab Sep 22, 2026 872 views

Question details

The user needs to automatically update formulas in a newly copied payroll worksheet so they reference the immediately preceding sheet rather than the original one, specifically for a balance-forward column.

Product: Excel
Device & OS: not provided
Scenario: Copying a weekly payroll workbook where the new sheet's balance-forward column must calculate using data from the previous week's worksheet.
Observed behavior: Formulas in the newly copied worksheet incorrectly continue referencing the original worksheet instead of updating to reference the immediately preceding sheet.

Before you start

Before running a VBA macro to automate worksheet referencing, ensure you have enabled the Developer tab in your spreadsheet application and saved your workbook as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a VBA Macro to Copy and Update References

Since spreadsheet software lacks a built-in function to detect worksheet creation order, a VBA macro is the most effective way to copy the last sheet and dynamically update formula references.

A VBA script can automate the duplication of your weekly payroll sheet and use a find-and-replace command within the code to update the sheet name referenced in your formulas.

Open the VBA Editor

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

Insert a New Module

Click 'Insert' in the top menu and select 'Module' to create a blank canvas for your script.

Write the Macro Script

Write a VBA script that identifies the last active sheet, duplicates it, and uses the `Replace` function on your specific balance-forward range (e.g., I6:M19) to swap the original sheet name for the preceding sheet's name.

Run the Macro

Save your file. Whenever you need to generate a new weekly sheet, press ALT + F8, select your new macro, and click 'Run'.

Solution 2

Use a Structured Database-Style Table Design

Consolidating your weekly payroll data into a single master table eliminates the need to copy sheets and manually adjust formula references.

Efficient Spreadsheet Automation

Automate Weekly Payroll Worksheets with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly copy sheets and automatically update previous worksheet references without manual edits. It provides a highly compatible and lightweight environment for heavy workbooks.

1. Open Your Workbook: Launch WPS Spreadsheet and open your weekly payroll workbook containing the balance-forward formulas.
2. Access the Developer Tools: Navigate to the 'Tools' tab on the top ribbon and click 'Developer' to access the VBA editor.
3. Insert the Automation Script: Insert a new module and paste your macro code designed for duplicating sheets and updating formula references.
4. Run the Macro Automatically: Execute the macro via the Macros menu to instantly generate your new weekly payroll tab with properly updated balance-forward references.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) file formatsRobust built-in VBA support for creating, running, and editing macrosLightweight software that handles complex formulas and large datasets smoothlyFamiliar tabbed interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Is there a standard Excel formula to reference the previous worksheet?

No, standard spreadsheet formulas do not detect worksheet creation order or automatically reference a 'previous' sheet by its position tab. You must use a VBA macro or define custom names using legacy XLM macro functions to achieve dynamic sheet referencing.

Why do formulas still reference the original sheet after I copy a worksheet?

When you duplicate a worksheet, cross-sheet references in existing formulas remain statically locked to the specific sheet name they originally pointed to. They do not update dynamically based on the new sheet's position in the workbook.

How do I update the balance-forward column manually if I don't want to use VBA?

You can use the 'Find and Replace' feature. Highlight the specific range containing your balance-forward formulas (such as I6:M19), press Ctrl + H, search for the old sheet's name, and replace it with the correct preceding week's sheet name.

Excel & Data Problems