VBA & Macro Problems

How to Automatically Record Excel Formula Inputs and Results in Another Sheet

Steve KSteve K Nov 2, 2026 869 views

Question details

The user wants to use an Excel macro to automatically log a specific cell's input value and its corresponding formula-calculated result onto a different worksheet without overwriting previous historical records.

How to Automatically Record Excel Formula Inputs and Results in Another Sheet
Product
Excel
Device & OS
not provided
Scenario
A user enters data into an input cell which triggers a formula. They need a continuous, automated record of every input and its resulting output stored sequentially row-by-row on a separate tracking worksheet.
Observed behavior
The intended goal is that whenever the input cell is changed, the macro immediately copies both the input value and the formula result as static values to the next available empty row on the target destination sheet.
Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your spreadsheet application's Trust Center settings before writing and executing VBA code.

Solution 1Recommended

Use a Worksheet_Change VBA Event

This method utilizes a VBA event macro tied to the worksheet to monitor a specific input cell. Once the cell is updated, the script automatically copies the input and the formula result to the next available empty row on a separate tracking sheet.

By utilizing the Worksheet_Change event, you can tell Excel to execute code the moment a user finishes typing in a specific cell. To ensure that only historical data is kept, the macro must copy the '.Value' of the cells rather than the formulas themselves.

It is crucial to correctly identify the next empty row on your target sheet so that previous logs are preserved. This is typically done by finding the last used row in a specific column and adding one to it.

1
Open the VBA Editor

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

2
Insert the Worksheet Code

In the Project Explorer pane on the left, double-click the worksheet where your input cell is located (for example, Sheet1).

3
Write the Worksheet_Change Event

Select 'Worksheet' from the left dropdown menu and 'Change' from the right dropdown menu above the code window. Write an IF statement checking if 'Target.Address' matches your input cell (e.g., $A$1).

4
Identify the Target Row

Within the IF block, declare a variable for the next empty row. Use a statement like 'NextRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1' to find the next available slot on your tracking sheet.

5
Copy the Values

Assign the values to the target sheet. For example: 'Worksheets("Sheet2").Cells(NextRow, 1).Value = Target.Value' to copy the input, and 'Worksheets("Sheet2").Cells(NextRow, 2).Value = Range("B1").Value' to copy the formula result.

6
Test the Macro

Close the VBA editor. Enter a new value in your monitored cell on Sheet1, then switch to Sheet2 to verify that the input and calculated result were logged in a new row.

Use a Worksheet_Change VBA Event
Timing Formula Calculation: Because Excel calculates formulas almost instantaneously, the Worksheet_Change event usually captures the newly updated formula result perfectly. If you are dealing with very heavy calculations, you may need to add a brief delay or switch to the Worksheet_Calculate event.

Automate Data Logging with WPS Spreadsheet

WPS Office Spreadsheet provides robust support for macros and VBA, allowing you to easily automate tasks like recording formula inputs and outputs sequentially to a secondary log sheet.

  1. 1. Open your Workbook in WPS Office: Launch WPS Spreadsheet and open your existing Excel workbook containing the formulas.
  2. 2. Access the Developer Tab: Go to the 'Developer' tab on the top ribbon and click 'VBA Editor' to open the scripting window.
  3. 3. Apply the Tracking Code: Double-click your input sheet in the project pane, paste your Worksheet_Change event macro to record the values, and save your workbook as a Macro-enabled file.
Fully compatible with Microsoft Excel macro files (.xlsm)Built-in VBA editor for writing, debugging, and executing custom scriptsLightweight application that handles large datasets and complex calculations smoothlyFamiliar user interface making migration completely seamless
microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro overwriting the same row instead of moving to the next?

This happens if the macro cannot correctly identify the last used row dynamically. Ensure you are using the code snippet `Cells(Rows.Count, 1).End(xlUp).Row + 1` to find the first empty row rather than hardcoding a specific row number.

Can I record multiple input cells at once?

Yes. You can modify the Worksheet_Change event to monitor an intersection of a designated range using `Intersect(Target, Range("A1:A10"))`. You can then loop through the changed cells to log multiple inputs simultaneously.

Will the macro copy the formula or just the calculated result?

To ensure your history log remains static and accurate, your VBA code should be set to copy the `.Value` property. If you copy the entire cell object, the formula itself will be transferred and it will attempt to recalculate on the new sheet based on relative references.

Why isn't my Worksheet_Change event triggering when I type?

First, ensure macros are enabled in your Trust Center settings. If they are, check if `Application.EnableEvents = False` was accidentally triggered by a previous macro and never reset. If so, you will need to run a quick script containing `Application.EnableEvents = True` to restore background event monitoring.