VBA Excel Copy Entire Column: Code and Tips

VBA & macros · Updated November 20, 2026

To copy an entire column in Excel with VBA, use the column’s Copy method and specify the destination: wsSource.Columns("A").Copy Destination:=wsTarget.Columns("C"). This copies column A to column C, including cell contents and formatting; use the examples below to adapt it for another sheet, values only, or an inserted column.

Basic VBA code to copy a whole column

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and put a macro like this in the module. Change the worksheet names and column letters to match your workbook.

Sub CopyEntireColumn()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Refer to sheets in the workbook containing this macro.
    Set wsSource = ThisWorkbook.Worksheets("Source")
    Set wsTarget = ThisWorkbook.Worksheets("Report")

    ' Copy all of column A to column C.
    wsSource.Columns("A").Copy Destination:=wsTarget.Columns("C")
End Sub

Columns("A") represents the complete worksheet column, not just the cells that currently contain data. The destination argument tells Excel where to place the copy, so a separate Paste command is not needed. The example uses ThisWorkbook, meaning the workbook that contains the macro. If the code is stored in a personal macro workbook or another file, that distinction matters: qualify the source and target workbooks explicitly rather than assuming ThisWorkbook is the file with the data.

Before running any macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, so a mistaken destination can replace important cells without a normal Undo option.

What copying an entire column includes

A worksheet column in modern Excel contains 1,048,576 rows. Copying it therefore copies the whole column range, including blank cells, not merely the populated portion. With the standard Copy Destination approach, Excel copies cell contents and cell formatting using its usual copy behavior. Formulas remain formulas, and relative references may adjust for the new location just as they do in a normal copy-and-paste operation. Absolute references marked with dollar signs remain fixed.

Column width is a separate layout detail worth checking. If matching the source width is important, set it explicitly after copying:

wsTarget.Columns("C").ColumnWidth = wsSource.Columns("A").ColumnWidth

That statement assumes the same wsSource and wsTarget variables as the first macro. It copies the width setting, not the cell contents. If the destination has data, formatting, or formulas that must be retained, consider whether a full-column copy is appropriate: the copy replaces corresponding destination cells and their formatting across that column.

Choose the copy method for the result you need

Use Copy Destination when the goal is to reproduce the cells as they appear and behave, including formulas and formatting. If you only need the displayed or calculated results, assigning values is usually clearer and avoids copying formats:

Sub CopyColumnValuesOnly()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    Set wsSource = ThisWorkbook.Worksheets("Source")
    Set wsTarget = ThisWorkbook.Worksheets("Report")

    ' Transfer values from A to C without copying formatting or formulas.
    wsTarget.Columns("C").Value = wsSource.Columns("A").Value
End Sub

This assigns the source cells’ values to the target cells. A formula in the source contributes its current result, rather than the formula text, and the destination’s existing formatting is not copied over. For example, a source cell displaying a date may contain a date serial value; if the target is formatted as General, it may display as a number. Apply an appropriate number format separately if the result needs to look like a date, percentage, or currency.

Other choices suit narrower needs:

  • Formulas and formatting: use Copy Destination for the full column or a specified range.
  • Values without formatting: assign the source range’s Value to the destination range’s Value.
  • Only part of a column: copy a bounded range such as A1:A500 instead of a million-row column.
  • Column layout: set ColumnWidth separately when the destination width must match.

Copy between worksheets or workbooks

To copy between sheets, qualify both columns with worksheet variables, as in the first example. Without that qualification, VBA may act on the active sheet, which can change when a user clicks another tab. Explicit references make a macro more predictable and avoid relying on whichever worksheet happens to be selected.

For a different workbook, define workbook variables too. For example, if both files are already open, set wbSource and wbTarget to the appropriate Workbooks("filename.xlsx") objects, then set each worksheet from its workbook and use the same copy statement. The workbook names must match the open workbook names. If a workbook is not open, open it deliberately before referring to it; a worksheet reference cannot be resolved from a filename alone.

When transferring formulas across workbooks, check their references afterward. A formula that refers to another sheet or workbook may keep or acquire references based on the original formula and where it is copied. If the target should contain fixed results rather than live formulas, use the values-only approach instead.

Copy only the used portion when practical

Copying a whole column is convenient, but it can do more work than necessary when a column has only a short list. A bounded range reduces the copy to the rows you want and makes the intended extent explicit. For a list beginning in A1, this example finds the last nonblank cell in column A and copies through that row:

Sub CopyUsedPartOfColumn()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long

    Set wsSource = ThisWorkbook.Worksheets("Source")
    Set wsTarget = ThisWorkbook.Worksheets("Report")

    ' Find the last nonblank cell in column A.
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

    ' Copy A1 through the last nonblank row to C1.
    wsSource.Range("A1:A" & lastRow).Copy _
        Destination:=wsTarget.Range("C1")
End Sub

This assumes the list starts at row 1 and that finding the last nonblank cell is a suitable definition of its extent. If column A is entirely blank, the expression returns row 1, so the macro copies A1. If the list contains gaps, the last nonblank cell can still be found, but the copied range includes the gaps between the first and last rows. If the data is in an Excel table or has a more complicated layout, use that structure to define the range rather than assuming every nonblank cell belongs to the list.

Insert a new column instead of replacing one

Copying to column C places the copied cells in column C; it does not insert a new column or shift existing columns to the right. If you need to preserve the current contents of C and create space for the copy, insert a column first. For data on a single worksheet, this pattern inserts a blank C and then copies A into it:

Sub InsertAndCopyColumn()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Report")

    ' Insert a new column C; existing C and later columns shift right.
    ws.Columns("C").Insert Shift:=xlToRight

    ' Copy column A into the newly inserted column C.
    ws.Columns("A").Copy Destination:=ws.Columns("C")
End Sub

Insertion changes the worksheet layout and may affect formulas, charts, named ranges, or other references that depend on those columns. Review the result before using the macro on a critical workbook. If you only need a copy in an already empty column, skip the insertion step.

Common problems and safe checks

  • Wrong sheet receives the copy: qualify every source and destination with a worksheet variable instead of using unqualified Columns or relying on the active sheet.
  • The destination data disappears: copying to an existing column replaces the corresponding cells. Confirm the destination is safe, or insert a new column first.
  • Dates or numbers look different: a values-only assignment does not carry number formats. Apply the required destination format separately.
  • The macro cannot find a worksheet: check the sheet tab name for spelling and spaces. Worksheets("Report") uses the worksheet name, not its tab position.
  • A macro does not run in the browser: Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros. Use the desktop Excel application.

Excel for Microsoft 365 on Mac supports VBA in the desktop app, although keyboard shortcuts and interface details can vary by keyboard and settings. On many Mac keyboards, Option+F11 opens the Visual Basic Editor; using the Developer tab’s Visual Basic command is another route. Excel for the web does not provide the VBA editor. A workbook containing VBA should be saved in a macro-enabled format such as .xlsm if the macro needs to remain in the file. Only enable macros in workbooks you trust.

Frequently Asked Questions

How do I copy an entire column to another sheet in VBA?

Set variables for the source and target worksheets, then use wsSource.Columns("A").Copy Destination:=wsTarget.Columns("C"). Replace the sheet references and column letters with the ones in your workbook.

Does copying a column with VBA copy formulas too?

Yes. The Copy Destination method copies formulas as formulas, and relative references can adjust to the destination. To transfer results instead, assign the source column’s Value to the destination column’s Value.

How can I copy a column without overwriting the destination?

Insert a new column at the destination position before copying. Existing columns at that position and to its right shift over. Check formulas and layout afterward because inserting a column changes the worksheet structure.

Can I copy only the cells that contain data?

Yes. Find the last relevant row and copy a range such as A1:A&lastRow, as in the bounded-range example. Make sure the rule for finding the last row fits your data, especially if the column has gaps or is blank.

Can VBA copy a full column in Excel for the web?

No. Excel for the web does not run VBA. Open the workbook in the desktop Excel app to use a VBA macro; Excel for Microsoft 365 on Windows and Mac supports desktop VBA.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.