VBA & Macro Problems

Copy the Latest Monthly Data Column with Excel VBA

Khadija KhanKhadija Khan Nov 18, 2026 868 views

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.

Copy the Latest Monthly Data Column with Excel VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

Open the workbook in desktop Excel, then select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the macro

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

3
Run and check the result

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.

4
Save the macro

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) to retain the VBA code.

Find and copy the last populated monthly column with VBA
Adapt the layout if needed: If headers are not in row 1 or unrelated headers appear to the right of the monthly data, change the boundary-finding logic before running the macro.
Free Microsoft Office alternative

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.

Copy and edit monthly spreadsheet columns using familiar controlsOpen and save common Microsoft Excel formats such as XLSXUse a lightweight office suite with a familiar interfaceMove everyday spreadsheet editing to WPS Office without rebuilding your workbook
microsoft office alternative - wps office

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.