Copy the Latest Monthly Data Column with Excel VBA
Question details
The user needs an Excel VBA macro that copies the latest monthly data column into the next available column without fixed column references.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A monthly worksheet contains data in successive columns, and the macro should extend the data range by one column.
- Observed behavior
- The existing macro uses fixed references such as AN and AO, so it does not automatically advance to the latest data column.
Check the worksheet name, the row containing month headers, and whether the latest month is the rightmost populated header. Test the macro on a sample workbook with the same layout before using it on your monthly data.
Find and copy the last populated monthly column with VBA
Use this approach when row 1 contains headers and the rightmost populated header identifies the latest monthly data column.
The macro finds the last header in row 1, finds the last populated row in that column, and copies the range to the column immediately to its right. It stops if the destination column already contains data.
This identifies the next available column, not the current calendar month. After copying, update the new header or adapt the code to match your month format. Copied formulas may adjust their relative references.
Open the workbook in desktop Excel, then select Developer > Visual Basic. In the VBA editor, select Insert > Module.
Paste this code into the module and replace Sheet1 with your worksheet name: Sub CopyLatestMonth() Dim ws As Worksheet Dim lastCol As Long, lastRow As Long Set ws = ThisWorkbook.Worksheets("Sheet1") If Application.WorksheetFunction.CountA(ws.Rows(1)) = 0 Then MsgBox "No headers found in row 1." Exit Sub End If lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column If lastCol = ws.Columns.Count Then MsgBox "There is no column to the right." Exit Sub End If If Application.WorksheetFunction.CountA(ws.Columns(lastCol + 1)) > 0 Then MsgBox "The destination column contains data." Exit Sub End If lastRow = ws.Cells(ws.Rows.Count, lastCol).End(xlUp).Row ws.Range(ws.Cells(1, lastCol), ws.Cells(lastRow, lastCol)).Copy _ Destination:=ws.Cells(1, lastCol + 1) MsgBox "Column copied. Update the new month header." End Sub
In Excel, select Developer > Macros, choose CopyLatestMonth, and select Run. Check the copied values, formulas, and formatting, then edit the new column's month header.
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) to retain the VBA code.

Manage Monthly Spreadsheets with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing monthly workbooks. Its Excel format support makes moving everyday spreadsheet work straightforward, although it does not fix Excel VBA issues and macro support can vary by edition.

Frequently Asked Questions
Does this macro find the current calendar month?
No. It copies the column with the rightmost populated header in row 1 to the next column. Update the copied header manually, or modify the code to identify a header matching the current date.
Why does the macro say the destination column contains data?
The macro checks the entire destination column with CountA before copying. Inspect the column immediately to the right of the latest header and move or remove existing data only if it is safe to do so.
How do I keep the macro when I save the workbook?
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Saving as XLSX does not retain VBA code.
What if my month headers are not in row 1?
Change the row references in the header check and last-column calculation to your header row, and adjust the copied range to include the rows you need. Test the revised code on a sample workbook.




