How to Use VBA to Copy Cells to Monthly Sheets in Another Workbook
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.

- 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.
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.
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.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in your spreadsheet program.
In the VBA editor, go to the top menu and select Insert > Module to create a blank module for your macro.
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
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.
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.

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. Open your Macro-Enabled Workbook in WPS: Launch WPS Spreadsheet and open your .xlsm file containing the data and VBA scripts.
- 2. Access the Developer Tools: Go to the Developer tab on the ribbon. If it is hidden, enable it in the WPS settings.
- 3. Launch the VBA Editor: Click on Visual Basic or press Alt + F11 to open the VBA environment.
- 4. Run your Code: Paste your script into a module and click the Run button to effortlessly copy data to your monthly sheets.

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.




