Excel VBA Copy and Paste a Column: Examples and Tips

To copy a column with Excel VBA, use sourceWs.Columns("B").Copy Destination:=targetWs.Columns("D"), replacing the worksheet variables and column letters with yours. This copies the source column’s cells, including their formulas and formatting; if you need values only, use a value assignment or Paste Special instead.

What copying a column means in VBA

Excel’s Copy command can transfer more than the text you see in a cell. Depending on the method, the result can include formulas, number formats, font and fill formatting, borders, data validation, and other cell attributes. A normal copy also adjusts relative formula references for the new location, much as copying and pasting with the mouse does.

There are two common scopes. A full-column copy means all 1,048,576 rows in a worksheet column, including empty cells. A used-range copy means only a selected range, such as B1:B500. Full-column copying is convenient when you want to reproduce a whole column, while a bounded range is usually more efficient for a large workbook or a routine that runs frequently.

Copying cells does not necessarily mean copying the column’s width. If matching widths matter, set the destination column width separately. Also decide whether formulas should remain formulas or become fixed values: those are different outcomes, and choosing the wrong one can change later calculations.

Copy a complete column to another worksheet

In this example, the macro copies column B from a worksheet named Source to column D on a worksheet named Report. The worksheet names must match the workbook’s tab names. The code uses explicit worksheet references, so it does not rely on whichever sheet happens to be active.

Option Explicit

Sub CopyWholeColumn()
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet

    ' Identify the source and destination sheets.
    Set sourceWs = ThisWorkbook.Worksheets("Source")
    Set targetWs = ThisWorkbook.Worksheets("Report")

    ' Copy column B to column D, including cell content and formatting.
    sourceWs.Columns("B").Copy Destination:=targetWs.Columns("D")
End Sub

To put the copy on the same worksheet, set both variables to that sheet and choose a destination column that does not overwrite the source. For example, copying column B to D on the same sheet uses the same statement after assigning both variables to the appropriate worksheet. If the macro is stored in a separate workbook, remember that ThisWorkbook means the workbook containing the macro, not necessarily the workbook currently selected in Excel.

Before running a macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a backup is the reliable way to recover from an unintended overwrite.

Copy only the rows that contain data

For routine work, copying only the populated portion of a column can avoid processing a million cells. The following example assumes column B has a header or data in row 1 and that the last nonempty cell in B defines the end of the range. It copies the cells to column D on Report.

Option Explicit

Sub CopyUsedPartOfColumn()
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Dim lastRow As Long

    ' Set the worksheets used by this macro.
    Set sourceWs = ThisWorkbook.Worksheets("Source")
    Set targetWs = ThisWorkbook.Worksheets("Report")

    ' Find the last nonempty cell in source column B.
    lastRow = sourceWs.Cells(sourceWs.Rows.Count, "B").End(xlUp).Row

    ' Copy from row 1 through that last row.
    sourceWs.Range("B1:B" & lastRow).Copy _
        Destination:=targetWs.Range("D1")
End Sub

The End(xlUp) method moves upward from the bottom of the column to find the last nonempty cell. If column B is completely blank, this expression returns row 1, so this sample still copies B1. Add a check for an empty source if that case is possible in your workflow. Also note that a formula returning an empty string is still a formula in a cell; it may be treated as nonempty by this kind of last-row search.

If the source has occasional blank rows, the method still finds the last nonempty cell below them and copies the intervening blanks too. That is normally desirable when row positions must stay aligned. If you need to copy only selected records, define the criteria separately rather than assuming “used range” means “every cell contains data.”

Paste values instead of formulas

A standard copy preserves formulas. For example, a copied formula such as =B2+C2 may become =D2+E2 when pasted two columns to the right, because Excel adjusts relative references. When the destination should contain the current results rather than live calculations, assign the source range’s values to the destination range.

With a header or data in B1 and the last used row found as above, this assignment copies values only. It does not copy the source formatting, and formulas in the source become their calculated results in the destination.

Option Explicit

Sub CopyValuesOnly()
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Dim lastRow As Long

    ' Set the source and destination sheets.
    Set sourceWs = ThisWorkbook.Worksheets("Source")
    Set targetWs = ThisWorkbook.Worksheets("Report")

    ' Find the last nonempty source cell in column B.
    lastRow = sourceWs.Cells(sourceWs.Rows.Count, "B").End(xlUp).Row

    ' Transfer calculated values from B to D without using the clipboard.
    targetWs.Range("D1:D" & lastRow).Value = _
        sourceWs.Range("B1:B" & lastRow).Value
End Sub

For a same-sized, contiguous range, assigning one range’s Value to another is usually a straightforward way to transfer results without invoking Copy and Paste. It does not carry formats, comments, or column width. If the source contains formulas that have not recalculated as expected, the destination receives the values Excel currently has available; calculate the workbook first when fresh results are essential.

Copy values and formatting together

Sometimes a report needs fixed values but should retain the source’s appearance. Use Copy followed by Paste Special for values and then formats. This still uses Excel’s clipboard, but it separates the content from the styling. The example below assumes row 1 through the last nonempty cell in B is the desired range.

Option Explicit

Sub CopyValuesAndFormats()
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Dim lastRow As Long

    ' Identify the sheets involved.
    Set sourceWs = ThisWorkbook.Worksheets("Source")
    Set targetWs = ThisWorkbook.Worksheets("Report")

    ' Determine the source range's final row.
    lastRow = sourceWs.Cells(sourceWs.Rows.Count, "B").End(xlUp).Row

    ' Copy calculated values, then apply the source cell formats.
    sourceWs.Range("B1:B" & lastRow).Copy
    targetWs.Range("D1").PasteSpecial Paste:=xlPasteValues
    targetWs.Range("D1").PasteSpecial Paste:=xlPasteFormats

    ' Clear the moving copy border and clipboard state.
    Application.CutCopyMode = False
End Sub

Paste Special formats applies cell formatting; it does not convert formulas into values, which is why this example pastes values first. If you also need the column width, set it explicitly after the cell operations with targetWs.Columns("D").ColumnWidth = sourceWs.Columns("B").ColumnWidth. Check the destination before running: pasting a shorter range may leave old cells below it unchanged, while pasting a longer range can overwrite existing content.

Choose the method that fits the result

Choose based on what the destination should contain, not just on how the source looks. A regular copy is the closest VBA equivalent to copying and pasting a column in the worksheet. A direct value assignment is more focused when only displayed results are needed.

Formulas and cell formatting: use Copy Destination. Check formula references after copying, especially if formulas contain relative references.
Values only: assign .Value from one equally sized range to another, or use PasteSpecial Paste:=xlPasteValues.
Values plus appearance: paste values and formats separately, then set column width if needed.
The entire worksheet column: use Columns("B") when the full column is genuinely required; otherwise use a bounded range.
Repeated or automated reporting: qualify every worksheet and workbook reference, and define how the destination’s old contents should be handled.

Copying is not the same as moving. A copy leaves the original cells in place. To move data, you would need to copy it and then deliberately clear the source, or use a cut operation; clearing should be done only after confirming the destination has the intended result.

Run the macro safely in Excel

In Microsoft 365 for Windows, save the workbook in a macro-enabled format such as .xlsm if it contains VBA. Open the VBA editor with Alt+F11, insert a standard module through Insert > Module, paste the code, and edit the worksheet names and column letters. Return to Excel and run the macro from the Macro dialog or the VBA editor. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

On Excel for Mac, VBA macros are supported in the desktop application, but the keyboard shortcut can depend on the keyboard and function-key settings. Open the Visual Basic Editor from Tools > Macro > Visual Basic Editor, or use Developer > Visual Basic if that control is available on the ribbon. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros; use desktop Excel for these steps.

Only enable macros in files from sources you trust. A macro can change or delete workbook data, and Excel does not provide a Ctrl+Z undo history for VBA actions. Make a backup, verify the source and destination sheet names, and test on a copy before using a macro on an important workbook.

Common problems and how to fix them

“Subscript out of range”: a worksheet name in the code does not match a sheet in the workbook referenced by the code. Check spelling, spaces, and which workbook ThisWorkbook refers to.
The wrong sheet is copied: code that uses ActiveSheet or unqualified Range expressions depends on the current selection. Use worksheet variables and qualify each range, as in the examples.
Existing destination data disappears: copying overwrites cells in the target range. Choose an unused destination or clear only a deliberately selected old output range after checking what it contains.
Formulas show different results: relative references adjust when copied. Use values-only transfer if the destination should be static, or use absolute references in formulas when references should remain fixed.
Formatting or width is missing: cell copying and column sizing are distinct concerns. Paste formats when needed and set ColumnWidth explicitly for consistent layout.
The macro does not run: confirm that macros are allowed for the file, that the code is in a standard module, and that the workbook is open in desktop Excel rather than Excel for the web.

One further issue is the meaning of “last row.” A single key column may not represent the extent of a table if cells below it contain data in other columns. In that situation, find the last row using a column that is reliably populated for every record, or define the source range from the actual table. Avoid relying on a worksheet’s overall used range without checking it: formatting left far below the data can make the apparent used area larger than expected.

Frequently Asked Questions

How do I copy a column to another sheet in VBA?
Set variables for the source and destination worksheets, then use sourceWs.Columns("B").Copy Destination:=targetWs.Columns("D"). Replace the sheet names and column letters with the ones in your workbook.

How do I paste values only with VBA?
For same-sized ranges, assign the source range’s Value to the destination range’s Value. You can also copy the source and use PasteSpecial Paste:=xlPasteValues; both approaches leave out source formatting.

Does VBA copy column width when copying cells?
Do not rely on a cell copy to set the destination width. Set it explicitly with targetWs.Columns("D").ColumnWidth = sourceWs.Columns("B").ColumnWidth when matching widths is required.

How can I copy a column without changing formulas?
If you mean preserving formulas as formulas, a normal copy preserves them but adjusts relative references for the new position. If you mean keeping the source formula references unchanged, consider absolute references in the formulas or copy results as values instead.

Can I run VBA in Excel for the web?
No. Excel for the web can store and open a macro-enabled workbook, but VBA macros must be run or edited in desktop Excel for Windows or Mac.

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.