Copy-Paste VBA Code: Add, Run, and Troubleshoot It

To copy and paste VBA code into Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code into the new module, and run it from Excel’s Developer > Macros menu. Save a backup first: macros can change workbook data, and their actions cannot be undone with Ctrl+Z.

What copy-pasting VBA code means

VBA, or Visual Basic for Applications, is the programming language used to automate tasks in desktop Excel. Copy-pasting VBA code usually means copying text from a trusted source and placing it in the Visual Basic Editor, not pasting it into a worksheet cell. The code is saved as part of a macro-enabled workbook and can then be run to carry out instructions such as formatting a report or copying a range.

There are two different kinds of copying that can be confused. You can copy and paste the VBA text itself into the editor, or you can write a VBA macro that copies worksheet cells. The first is how you add a macro; the second is something the macro can do. The example below demonstrates the second task.

Where VBA code goes in Excel

VBA code belongs in the Visual Basic Editor, usually inside a standard module. A module is a container for procedures, which are the named blocks of code that Excel can run. For an ordinary macro that you start yourself, a standard module is the clearest place to put it.

If the Developer tab is not visible, select File > Options > Customize Ribbon, check Developer, and select OK.
Open the Visual Basic Editor with Alt+F11, or select Developer > Visual Basic.
In the editor, select the workbook where the macro belongs in Project Explorer. If that panel is hidden, press Ctrl+R.
Select Insert > Module. Excel adds a module, often named Module1, beneath the workbook’s Modules folder.
Click the blank code pane and paste the copied code with Ctrl+V. On a Mac, use Command+V.

On Excel for Mac, open the editor with Developer > Visual Basic; Option+F11 may also work, depending on the keyboard and function-key settings. The editor’s Insert > Module command is available there too. If a shortcut does not work, use the ribbon or menu. Excel for the web does not include the Visual Basic Editor, so adding or running VBA requires the desktop application.

A simple VBA copy-and-paste example

This macro copies the contents of cells A2:B10 on a worksheet named Sheet1 to D2:E10 on the same sheet. It copies formulas and formatting as well as cell contents, as a normal Excel copy does. The destination is calculated from the source range’s size, so it remains correctly sized if you adjust the source range in the code.

Sub CopyRangeToDestination()
    ' Declare the source and destination ranges.
    Dim sourceRange As Range
    Dim destinationRange As Range

    ' Identify the range to copy on Sheet1.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:B10")

    ' Size the destination to match the source range.
    Set destinationRange = ThisWorkbook.Worksheets("Sheet1").Range("D2").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Copy the cells, including formulas and formatting.
    sourceRange.Copy Destination:=destinationRange
End Sub

Before running a macro, save a separate copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a backup lets you recover if the result is not what you intended. Also check that Sheet1 exists in the workbook and that D2:E10 is an appropriate destination; existing content there will be overwritten.

To adapt the example, change the worksheet name and source address in the Set sourceRange line, then change D2 if you want another starting destination. Worksheet names must match the sheet tab exactly, including spaces. If the worksheet is called Monthly Report, for example, use that name between the quotation marks. The destination’s dimensions are set automatically to match the source.

How to run the macro

After pasting the code, save the file as an Excel Macro-Enabled Workbook (*.xlsm). Choose File > Save As, select the macro-enabled workbook type, and save. A standard .xlsx workbook cannot store VBA code; Excel may warn that the macro will be removed if you save the VBA project in that format.

To run the example from the worksheet, select Developer > Macros, choose CopyRangeToDestination, and select Run. You can also press Alt+F8 in Windows to open the Macro dialog. On Mac, use Developer > Macros if the shortcut differs with your keyboard settings. To run from the Visual Basic Editor, click inside the procedure and press F5. Running from Excel’s Macro dialog is often easier because it shows the available macro names.

A macro’s name is the text immediately after Sub and before the parentheses. The procedure must be in a standard module and should not be declared Private if you want it listed in the Macro dialog. If you have several procedures, select the exact one you intend to run rather than relying on whichever code pane happens to be open.

Copying values instead of formulas and formatting

The example uses Excel’s regular copy operation, so it brings formulas, formatting, and other copied cell properties along with the cell contents. Sometimes that is not what you want. For example, a report may need the displayed results but not formulas that refer back to the original sheet.

For a values-only copy, replace the sourceRange.Copy line with the assignment below. It copies the evaluated values into the destination cells, but does not copy the source formatting.

' Copy values only, without copying formulas or formatting.
destinationRange.Value = sourceRange.Value

If the destination should retain its existing number formats, this values-only approach is useful. If you need formats but not formulas, VBA can copy formats separately, but that requires additional instructions. Be clear about the intended result before choosing a method: a formula copied normally may adjust its relative references at the new location, while a value-only copy stores the result rather than the formula.

Why pasted VBA code may fail

A macro that does not appear or run often has a simple cause. Check the following before changing Excel’s security settings or rewriting the code:

Code is in the wrong place: A macro intended to run from the Macro dialog should usually be in a standard module, not pasted into a worksheet cell or an unrelated sheet object.
The workbook is saved as .xlsx: Save as .xlsm to retain the VBA project, and confirm any format warning rather than choosing to discard the code.
The sheet or range name is wrong: The example needs a sheet tab named Sheet1. A misspelled name can produce a Subscript out of range error.
Only part of the procedure was copied: A complete procedure needs its opening Sub line and closing End Sub. Check that all lines were pasted and that no unrelated text was included.
The macro is not listed: Check that it is a Public procedure in a standard module and that the Macro dialog is showing the correct workbook.
The pasted text has altered punctuation: Code requires ordinary VBA punctuation. Replacing straight quotation marks with typographic “smart quotes,” for example, can cause a compile error.

When Excel highlights a line after an error, read the message and inspect that line and the lines immediately before it. In the Visual Basic Editor, select Debug > Compile VBAProject to identify some syntax and compile problems. A compile error means VBA cannot interpret the code as written; a run-time error means the code started but encountered a problem while executing, such as referring to a worksheet name that does not exist.

Security and safe use of copied code

VBA can make changes to a workbook and, depending on the code and permissions, interact with other parts of the computer. Treat a macro as executable software, not as harmless text. Read the code or have someone qualified review it, and get it from a source you trust. Be especially cautious if code is hard to follow, asks you to enable access to external resources, or performs actions unrelated to the task you expected.

Excel may block macros or display a security warning when a workbook is opened. Do not respond by enabling every macro globally or lowering security for all files. Follow your organization’s policies, and enable a macro only when you understand its purpose and trust the workbook’s source. In a workplace, an administrator may control these settings. A digitally signed macro can help identify its publisher, but a signature alone is not proof that the code is safe.

Keep an untouched backup before running unfamiliar code, and test changes on a copy of the workbook with a small sample. If a macro edits or deletes data, a backup is particularly important because Ctrl+Z does not undo VBA changes. Close the workbook without saving if you need to discard the results and have not made other changes you want to keep.

VBA in desktop Excel, Mac, and the web

Excel for Microsoft 365 on Windows includes the Visual Basic Editor and supports VBA macros. Excel for Mac also supports many VBA tasks, but some Windows-specific features, controls, and integrations are not available or behave differently. A macro that relies on Windows-only components may need changes before it works on Mac; copying the same code does not guarantee cross-platform compatibility.

Excel for the web can open and edit many workbook types, but it cannot create, edit, or run VBA macros. To work with a VBA project, open the workbook in desktop Excel. If you need an automation that works in the browser, Office Scripts may be an option in supported Microsoft 365 environments. Office Scripts use TypeScript rather than VBA and are a separate feature, so VBA code cannot simply be pasted into an Office Script editor.

Frequently Asked Questions

How do I paste VBA code into Excel?
Open the Visual Basic Editor with Alt+F11 on Windows, select Insert > Module, click the code pane, and press Ctrl+V. On Mac, use Developer > Visual Basic and Command+V to paste.

Where should I paste a VBA macro?
For a macro you want to run from Developer > Macros, paste it into a standard module created with Insert > Module. Worksheet and workbook code modules have different roles and are commonly used for event procedures.

How do I run pasted VBA code?
Save the workbook as .xlsm, then select Developer > Macros, choose the procedure name, and select Run. You can also run a procedure from the editor by clicking inside it and pressing F5.

Can I undo a VBA macro with Ctrl+Z?
Generally, no. Excel’s undo history does not normally reverse changes made by VBA. Save a backup before running a macro, especially if it changes, replaces, or deletes worksheet data.

Can I paste or run VBA in Excel for the web?
No. Excel for the web does not provide the Visual Basic Editor or run VBA macros. Open the workbook in desktop Excel to work with its VBA project.

Why is my macro not showing in the Macro dialog?
Confirm that the procedure is in a standard module, is not declared Private, and belongs to the workbook you are viewing. Also check that the code starts with a valid Sub declaration and that the file is saved in a macro-enabled format.

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.