VBA & Macro Problems

How to Use VBA to Copy Cells to Monthly Sheets in Another Workbook

Olivia MillerOlivia Miller Nov 10, 2026 869 views

Question details

The user wants to create a VBA macro that copies data from specific cells in one workbook to the next available row in another workbook, dynamically selecting the destination worksheet based on a date value.

How to Copy Cells to Monthly Sheets in Another Workbook Using VBA
Product
Spreadsheets
Device & OS
not provided
Scenario
Automating data entry or archiving records by organizing copied data into specific monthly sheets based on a date cell.
Observed behavior
The user needs the correct VBA code to identify the destination month sheet from a date and write values to the next available blank row without overwriting existing data.
Before you start

Ensure that both your source workbook and the destination workbook (e.g., "Data arc.xlsx") are open, and verify that the destination workbook contains worksheets named with the abbreviated month format (e.g., Jan, Feb, Mar). Enable macros in your spreadsheet software before running the code.

Solution 1Recommended

Use VBA to Dynamically Map and Append Data

A VBA macro that reads the date cell, formats it to identify the target sheet, and appends data to the next empty row.

This VBA method allows you to automatically archive data. The script reads a date in a specific cell (like C5), formats it to a 3-letter month abbreviation (like 'Jan'), and looks for a sheet with that exact name in your destination workbook. It then finds the last used row in the target columns and pastes the source cell values in the next empty row.

1
Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in your spreadsheet program.

2
Insert a New Module

In the VBA editor, go to the top menu and select Insert > Module to create a blank module for your macro.

3
Paste the Macro Code

Copy the following VBA script and paste it into the module window: Sub Test() Dim wsS As Worksheet, wbT As Workbook, wsT As Worksheet, stM As String Set wsS = ActiveSheet Set wbT = Workbooks("Data arc.xlsx") stM = Format(wsS.Range("C5").Value, "mmm") Set wsT = wbT.Worksheets(stM) wsT.Cells(wsT.Rows.Count, "A").End(xlUp).Offset(1).Value = wsS.Range("C4").Value wsT.Cells(wsT.Rows.Count, "M").End(xlUp).Offset(1).Value = wsS.Range("E6").Value End Sub

4
Customize the Cell References

Modify 'wsS.Range("C5")' to point to your actual date cell. Adjust 'wsS.Range("C4")' and 'wsS.Range("E6")' to match your source data cells, and change the letters '"A"' and '"M"' to match your target columns in the destination sheets.

5
Run the Macro

Ensure the destination workbook is open. Press F5 while inside the code window, or assign the macro to a shape or button on your worksheet to execute it easily.

Use VBA to Dynamically Map and Append Data
Destination Workbook Must Be Open: If your destination workbook is not open when you run the script, you will need to add a Workbooks.Open("C:\path\to\Data arc.xlsx") command before setting the wbT variable.

Automate Workflows with WPS Spreadsheet VBA

WPS Office Spreadsheet provides robust support for VBA macros, allowing you to automate repetitive tasks like cross-workbook data transfers. Enjoy seamless compatibility with Microsoft Excel macro scripts (.xlsm) in a lightweight and cost-effective productivity suite.

  1. 1. Open your Macro-Enabled Workbook in WPS: Launch WPS Spreadsheet and open your .xlsm file containing the data and VBA scripts.
  2. 2. Access the Developer Tools: Go to the Developer tab on the ribbon. If it is hidden, enable it in the WPS settings.
  3. 3. Launch the VBA Editor: Click on Visual Basic or press Alt + F11 to open the VBA environment.
  4. 4. Run your Code: Paste your script into a module and click the Run button to effortlessly copy data to your monthly sheets.
Full support for VBA macros and Excel (.xlsm) filesSeamless Microsoft Excel formatting and formula compatibilityLightweight design with fast processing for large datasetsFree core features for everyday spreadsheet management
QA img-9

Frequently Asked Questions

What happens if the destination monthly sheet doesn't exist?

If the macro tries to set wsT = wbT.Worksheets(stM) and the sheet (e.g., 'Jan') does not exist, the code will throw a 'Subscript out of range' error. You can prevent this by manually creating the 12 month sheets beforehand or adding a VBA function to check if the sheet exists and create it if necessary.

How do I make the macro open the destination workbook automatically?

You can replace Set wbT = Workbooks("Data arc.xlsx") with Set wbT = Workbooks.Open("C:\Your\Folder\Path\Data arc.xlsx"). This instructs VBA to automatically locate and open the file from your local drive.

Can I copy cell formatting along with the values?

The provided code only transfers raw values using .Value = .Value. To copy formatting, you need to use the .Copy and .PasteSpecial methods instead, specifying Paste:=xlPasteFormats and Paste:=xlPasteValues.

Why is the macro overwriting my last row instead of creating a new one?

The End(xlUp) method looks from the very bottom of the worksheet up to the last non-empty cell. If it is overwriting data, it may be because the target column (e.g., Column A) is completely empty. Ensure your target columns have headers so the macro correctly identifies the last used row.