Excel VBA: Copy a Column and Paste Values Only Safely

To copy a column in Excel VBA and paste only its values, copy the source column and use PasteSpecial with xlPasteValues. For example, copy column A on a worksheet named Source into column B on a worksheet named Destination with the macro below; it transfers formula results and other cell values, not the formulas or source formatting.

Basic VBA code for a values-only column copy

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste this procedure into the module. Change the worksheet names or column letters to match your workbook.

Sub CopyColumnValuesOnly()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' Use worksheets in the workbook that contains this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Source")
    Set destinationSheet = ThisWorkbook.Worksheets("Destination")

    ' Copy column A, then paste values into column B.
    sourceSheet.Columns("A").Copy
    destinationSheet.Columns("B").PasteSpecial Paste:=xlPasteValues

    ' Clear Excel's moving copy border and clipboard mode.
    Application.CutCopyMode = False
End Sub

Before running a macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover if the wrong column or destination is specified. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), and run the macro from Excel desktop; Excel for the web does not run VBA macros.

What “paste values” does in Excel

A cell containing a formula has both a formula and a calculated result. For example, a source cell with =A2&" "&B2 might display “Ada Lovelace.” A normal copy carries the formula, so the destination may recalculate it using references at the new location. A values-only paste instead writes the current result, “Ada Lovelace,” as text. Changing the source later will not update that pasted result.

The same idea applies to numbers, dates, text, logical values, and cell errors: Excel transfers the cell contents as values rather than the underlying formulas. The operation does not copy the source column’s number formats, fonts, fills, borders, column width, or other formatting. The destination keeps its existing formatting. That distinction matters with dates and percentages: a date value can arrive as a number but look like a serial number if the destination is formatted as General.

In the macro, xlPasteValues is Excel’s built-in paste type for this operation. It is not the same as xlPasteAll, which includes more than values. The copy-and-paste approach uses Excel’s clipboard, but it does not require selecting cells or switching to the worksheet on screen.

Copy only the used rows instead of a million-row column

A worksheet column contains 1,048,576 rows. Copying an entire column is convenient when you truly want to target every row, but it may be wasteful in a large workbook. If the data begins in row 1 and column A is the source, you can find its last nonempty cell and transfer just that range. This version assigns values directly, without using the clipboard.

Sub CopyUsedRowsAsValues()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim lastRow As Long

    ' Use worksheets in the workbook that contains this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Source")
    Set destinationSheet = ThisWorkbook.Worksheets("Destination")

    ' Do nothing if the source column contains no values or formulas.
    If Application.WorksheetFunction.CountA(sourceSheet.Columns("A")) = 0 Then Exit Sub

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

    ' Put the source results into destination column B as values.
    destinationSheet.Range("B1:B" & lastRow).Value = _
        sourceSheet.Range("A1:A" & lastRow).Value
End Sub

This range-based version assumes the data starts at row 1. If row 1 is a header, it includes the header too; change both range starts to row 2 if you want to copy data but leave the destination header alone. The last-row calculation looks only at column A, so it is suitable when that column defines how far the records extend. If other columns contain records below the final entry in A, use the relevant key column or calculate the final row using a different rule.

The procedure writes only through lastRow. If the destination column already contains older data below that row, those leftover cells remain untouched. Decide whether that is desirable before running it. If not, clear the intended destination range first, taking care not to erase unrelated content.

Using direct assignment instead of Copy and PasteSpecial

For a same-sized source and destination range, assigning one range’s Value property to another is a concise way to transfer results without formulas or clipboard state. The second macro demonstrates this for a bounded range. It leaves destination formatting in place, just as a values-only paste does.

Direct assignment is often useful inside longer procedures because it avoids selecting a range and avoids the copy border. It is important to keep the ranges aligned and the same size. Assigning a two-dimensional range to another range with mismatched dimensions can produce unexpected results or an error. For a full-column assignment, the ranges must also be handled carefully because every row is included; use a bounded range when the data is limited.

There is a small distinction between Excel’s displayed value and the raw stored representation. For ordinary text and numbers, .Value is usually a straightforward choice. Excel may represent dates and currency specially when VBA reads them through .Value; if a procedure needs the underlying numeric representation, .Value2 is another property to consider. Neither property copies number formatting, so set the destination format separately if the displayed appearance matters.

Adapting the macro to different columns and workbooks

To change the source or target, edit the column references in the code. For example, sourceSheet.Columns("D") and destinationSheet.Columns("F") copy column D to column F. The worksheet names must match the visible sheet tabs exactly, including spaces. If the sheet is named “Monthly Data,” write that full name inside the quotation marks.

ThisWorkbook means the workbook where the VBA code is stored. That is a useful safeguard when the macro is in the same workbook as the data. ActiveWorkbook, by contrast, refers to whichever workbook is active when the code runs. Using the active workbook can be appropriate for a general-purpose macro, but it can also target the wrong file if several workbooks are open.

For workbooks other than the one containing the macro, use explicit workbook references rather than assuming that the active sheet is correct. A macro can refer to an already open workbook by its name, but that workbook must be open and its name must match. Cross-workbook copies also deserve extra care: verify both workbook and worksheet names before running, and avoid relying on whichever sheet happens to be selected.

Common problems and how to prevent them

“Subscript out of range”: A worksheet name in the code does not match a sheet in ThisWorkbook, or the code is stored in a different workbook than expected. Check the sheet tab names and which workbook contains the module.
The result looks like a number instead of a date: Values were transferred, but the destination did not receive the source number format. Apply a suitable date format to the destination if the underlying value is a date.
Old destination entries remain: A bounded macro changes only the rows it targets. It does not automatically remove extra values below the copied range.
Nothing seems to happen: Confirm that macros are enabled, the procedure is in a standard module, and the named source column contains data. Excel for the web cannot execute VBA.
The pasted result changes unexpectedly: Check whether the code uses a normal copy rather than xlPasteValues, or whether the chosen destination cells have formatting that changes how values appear.
The macro cannot be undone: This is expected. VBA actions generally do not populate Excel’s Undo history. Restore from the backup or rerun a carefully designed recovery process.

Also check for merged cells, protected sheets, and filters if a paste fails or the result does not match expectations. A protected destination may prevent writing unless the macro has been designed to handle protection. A filtered view does not mean the macro automatically targets only visible records: a whole-column range includes hidden and filtered-out rows as well. If the requirement is to copy visible cells only, that is a different task and needs code that explicitly targets visible cells.

Run the macro and choose the right method

On Windows, use Alt+F8 to display the Macro dialog, select the procedure, and choose Run. You can also run it from the Visual Basic Editor. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, then select Developer. On Mac, VBA is available in the desktop app, but keyboard shortcuts can vary by keyboard; use the Visual Basic Editor or the Macro command from Excel’s menus if the Windows shortcut does not apply. The web version does not support running VBA.

Choose the method that matches the job:

Use Copy and PasteSpecial when you want Excel’s explicit Paste Values behavior and are comfortable using the clipboard.
Use bounded .Value assignment when you know the exact data range and want a direct transfer without copying the whole column.
Use the full column when the macro must cover every row, including rows that may be populated later. Consider workbook size and calculation workload before doing this repeatedly.

If the task is occasional and does not need automation, use the Excel interface instead: copy the source cells, select the destination, then choose Home > Paste > Values. The keyboard route is Ctrl+C, then Ctrl+Alt+V to open Paste Special, followed by V and Enter in the standard Windows dialog. On Mac, shortcuts differ by keyboard and Excel version; the Home > Paste menu is a dependable route. A VBA macro is most useful when the same transformation must be repeated or incorporated into a larger process.

Frequently Asked Questions

How do I copy an entire column and paste values only in VBA?
Use sourceSheet.Columns("A").Copy, then destinationSheet.Columns("B").PasteSpecial Paste:=xlPasteValues. Clear clipboard mode afterward with Application.CutCopyMode = False.

Does PasteSpecial values keep formulas?
No. It writes the formula’s current result as a value, so the destination does not recalculate from the original formula. The source formula remains unchanged.

Does values-only paste copy formatting or column width?
No. The destination keeps its existing cell formatting and column width. Apply formatting separately if you want dates, percentages, or other values to display in a particular way.

Can I copy a column to another worksheet?
Yes. Set separate worksheet variables for the source and destination, then copy from one sheet’s column and paste into the other sheet’s column. Confirm both sheet names are correct before running the macro.

Can I undo a VBA column copy with Ctrl+Z?
Usually not. Macro actions do not provide the normal Excel Undo history, so save a backup copy before running code that changes workbook data.

Will the macro work in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but it cannot run VBA macros. Use Excel desktop for Windows or Mac to execute VBA.

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.