VBA & Macro Problems

How to Create an Excel VBA Macro to Save a Date and Cell Value to Another Sheet

Olivia Miller Sep 27, 2026 869 views

Question details

The user needs to create an Excel macro that records the current date and a specific cell value (like Sheet1 B12), and appends them to the next available row on a different worksheet (Sheet2) upon a button click.

How to Create an Excel VBA Macro to Save a Date and Cell Value to Another Sheet

Product: Excel
Device & OS: not provided
Scenario: Automating data entry by clicking a button to log a timestamp and a specific input value into a continuously updating database or log sheet.
Observed behavior: A VBA script reads the date and source cell value, determines the next empty row in the destination sheet, and populates the target columns sequentially to create a new record.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to allow VBA code execution, and verify that the Developer tab is enabled in your ribbon.

Solution 1Recommended

Use VBA to Append Data to the Next Empty Row

Write a VBA script to automatically locate the last used row in your destination sheet and insert the current date alongside the source cell value.

By utilizing the End(xlUp) method in VBA, Excel can dynamically find the last populated row in a specified column. Adding 1 to this row number ensures that your new data is appended safely without overwriting previous records.

Open the VBA Editor

Navigate to the Developer tab on the Excel ribbon and click 'Visual Basic', or simply press Alt + F11 on your keyboard.

Insert a New Module

In the VBA Editor, right-click your workbook name in the Project Explorer window, select 'Insert', and click 'Module' to create a blank script window.

Write the Macro Code

Define your macro (e.g., Sub SaveData()). Create variables to set your source sheet (Sheet1) and destination sheet (Sheet2). Use 'NextRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1' to find the next blank row. Assign 'Date' to Sheet2.Cells(NextRow, 1).Value and 'Sheet1.Range("B12").Value' to Sheet2.Cells(NextRow, 2).Value.

Assign the Macro to a Button

Go back to your worksheet, click 'Insert' under the Developer tab, and choose a Form Button. Draw the button on your sheet, select your newly created macro from the prompt, and click OK. Clicking this button will now execute the data transfer.

Use VBA to Append Data to the Next Empty Row

Automation Setup Complete: Every time the button is clicked, a new row will be created in Sheet2 with the exact click date in column A and the value of B12 in column B.

Automate Your Spreadsheets Easily with WPS Office

WPS Spreadsheet provides excellent support for VBA macros, allowing you to seamlessly run scripts that automate data entry and log generation across worksheets.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing your data.
2. Access the Developer Tools: Click on the Developer tab in the ribbon, or press Alt + F11 to launch the macro editor.
3. Add or Edit your Script: Insert a module and paste your VBA code for locating the empty row and appending the data.
4. Trigger the Macro: Insert a shape or form control button, right-click to assign your macro, and execute your task with a single click.

Fully compatible with Microsoft Excel macro-enabled formats (.xlsm).Built-in VBA editor to write, debug, and execute your automation scripts.Lightweight, fast, and completely free for everyday office tasks.Familiar user interface requiring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro not running when I click the button?

Your macro security settings might be restricting the script from running. Go to the Developer tab, click 'Macro Security', and choose to enable macros. Ensure your file is saved as a Macro-Enabled Workbook (.xlsm).

How does VBA know which row is the next empty row?

The standard VBA method uses 'Cells(Rows.Count, 1).End(xlUp).Row'. This tells Excel to go to the very bottom of the sheet in column 1 (A), jump up to the last cell that contains data, and return its row number. Adding '+ 1' gives you the next completely blank row.

Can I use a shape or image instead of a grey form button?

Yes. You can insert any shape from the 'Insert' > 'Shapes' menu, right-click the shape, and select 'Assign Macro'. It will function identically to a standard form button.

How do I include the exact time of the click instead of just the date?

In your VBA script, replace the keyword 'Date' with 'Now'. This will output both the current date and the exact system time when the button was pressed.

Excel & Data Problems