VBA & Macro Problems

How to Transfer Values Between Monthly Excel Workbooks using VBA

Huma Ashraf Ch Sep 25, 2026 869 views

Question details

The user needs to automate the transfer of specific cell values from a previous month's Excel workbook to the current month's workbook across matching worksheets.

Transfer Values Between Monthly Excel Workbooks using VBA

Product: Excel
Device & OS: not provided
Scenario: Updating monthly reports where previous month's ending balances or specific metrics need to be rolled over to the new month's sheets.
Observed behavior: Manual copying is currently required to move data from cell G27 in the previous workbook to cell D3 in the current workbook. The goal is to automate this process via a VBA macro.

Before you start

Before running any VBA macro, ensure you have the exact file path of the previous month's workbook and create a backup copy of both files to prevent accidental data loss.

Solution 1Recommended

Use a VBA Macro to Automate Data Transfer

Create and run a VBA macro to automatically pull values from the previous month's workbook into the current one by matching worksheet names.

Using VBA allows you to loop through all worksheets in your current workbook, find the sheet with the exact same name in the previous month's file, and copy the required cell values instantly.

Open the VBA Editor

Open your current month's workbook and press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window.

Insert a New Module

In the VBA editor, click 'Insert' from the top menu and select 'Module'. This will create a blank space for your macro code.

Add the Macro Code

Paste a VBA script that opens the previous workbook, loops through 'ThisWorkbook.Worksheets', matches the sheet names, and sets Range("D3").Value equal to the previous workbook's Range("G27").Value. Ensure you update the file path in the script to match your previous month's file location.

Run the Macro

Press Alt + F8 in Excel, select your newly created macro from the list, and click 'Run' to execute the data transfer.

Use a VBA Macro to Automate Data Transfer

File Path Configuration: Make sure to replace the placeholder file path in the macro code with the actual location of your previous month's workbook.

WPS Spreadsheet Automation

Automate Workbook Tasks Easily with WPS Spreadsheet

WPS Office provides robust macro and VBA support, allowing you to seamlessly run your Excel scripts and automate repetitive data transfers between monthly workbooks.

1. Open your Workbooks in WPS: Launch WPS Spreadsheet and open your current month's workbook.
2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macros' to open the VBA Editor.
3. Insert and Run Script: Insert a new module, paste your data transfer script, and click run to instantly update your worksheets.

Full compatibility with Microsoft Excel (.xlsx and .xlsm) formatsSupports VBA macros for advanced data automationLightweight design with high processing speed

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a 'Subscript out of range' error when running the macro?

This error usually occurs if the file path is incorrect, or if the worksheet names do not exactly match between the two monthly workbooks.

Do I need to keep the previous month's workbook open to run the macro?

No, you can write the VBA macro to automatically open the previous workbook in the background, extract the required data, and close it without manual intervention.

How can I transfer multiple cells instead of just one?

You can modify the VBA script to specify a range instead of a single cell, such as copying Range("G27:G35") to the corresponding destination range in the current workbook.

Can I run this macro on a Mac?

Yes, Excel for Mac supports VBA, but you must ensure the file path syntax uses Mac's directory structure (using slashes instead of backslashes) for the macro to locate the previous workbook.

Excel & Data Problems