VBA Copy and Paste Values Across All Excel Tabs Safely

Yes. In Excel for Microsoft 365 on Windows, a VBA macro can take the selected range on the active worksheet and write its values to the same cell addresses on every other worksheet in that workbook. The macro below includes hidden worksheets, replaces existing contents, and does not copy formulas or formatting.

What “copy values to all tabs” means

In Excel, a tab can represent a worksheet or a chart sheet. A worksheet contains cells; a chart sheet contains a chart and has no cell range to receive pasted values. The macro here therefore works across the workbook’s worksheets, not chart sheets. It uses the active worksheet as the source and targets the same range address on every other worksheet in that workbook.

For example, if you select A2:B5 on a worksheet named “January,” the macro writes the contents of A2:B5 on “January” into A2:B5 on every other worksheet. It does not search for matching labels or choose a different destination range. The source worksheet already has the selected values, so the macro skips writing back to it.

“Values” means the current cell results or entries. If the source cells contain formulas, the receiving cells get the formula results as values, not the formulas themselves. This is useful for distributing a fixed set of figures, labels, or calculated results without copying the calculation logic.

VBA macro to copy the selection to every worksheet

Select one rectangular range on the source worksheet before running this macro. The destination range on each worksheet will have the same address and dimensions. For example, selecting C3:D10 writes to C3:D10 on each other worksheet.

Option Explicit

Sub CopySelectionValuesToAllWorksheets()
    Dim sourceSheet As Worksheet
    Dim sourceRange As Range
    Dim targetSheet As Worksheet
    Dim oldScreenUpdating As Boolean

    ' Require a single, rectangular cell selection.
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a cell range on a worksheet first.", vbExclamation
        Exit Sub
    End If

    Set sourceRange = Selection
    If sourceRange.Areas.Count <> 1 Then
        MsgBox "Select one continuous rectangular range.", vbExclamation
        Exit Sub
    End If

    Set sourceSheet = ActiveSheet
    oldScreenUpdating = Application.ScreenUpdating
    On Error GoTo CopyError
    Application.ScreenUpdating = False

    ' Write values to the same address on each other worksheet.
    For Each targetSheet In sourceSheet.Parent.Worksheets
        If Not targetSheet Is sourceSheet Then
            targetSheet.Range(sourceRange.Address).Value2 = sourceRange.Value2
        End If
    Next targetSheet

    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "Values copied to the other worksheets.", vbInformation
    Exit Sub

CopyError:
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "The copy stopped: " & Err.Description, vbExclamation
End Sub

The assignment uses Value2 rather than the clipboard. For a range with several cells, Excel transfers the cell values to the corresponding cells in one operation. The code rejects a multi-area selection, such as several separately selected ranges, because those areas do not form one rectangular source.

How to add and run the macro

Save a copy of the workbook first. Macros change workbook contents, and their changes cannot be undone with Ctrl+Z.
Open the source worksheet and select the range to distribute. Avoid selecting entire rows or columns unless you really intend to overwrite that many cells.
Open the Visual Basic Editor with Alt+F11 on Windows. If your keyboard uses function-key controls, you may also need Fn. On Mac, the shortcut is commonly Option+F11, sometimes with Fn; the Developer tab’s Visual Basic command is another route.
In the editor, choose Insert > Module, paste the code into the module, and return to Excel.
Run the macro using Alt+F8 on Windows, select CopySelectionValuesToAllWorksheets, and choose Run. On Mac, use Tools > Macro > Macros if the keyboard shortcut differs.

If the Developer tab is not visible in Excel for Windows, enable it through File > Options > Customize Ribbon, then select Developer. Macro commands and security prompts can vary with an organization’s Excel policies. Excel for the web can open many macro-enabled workbooks, but it cannot run, create, or edit VBA macros; use the desktop app for this procedure.

Which worksheets are included

The loop uses the source workbook’s Worksheets collection. It processes visible and hidden worksheets in that workbook, but it does not change worksheets in other open workbooks. Very hidden worksheets are also worksheets, so they are included. A hidden tab is not automatically protected from edits made by VBA.

The source worksheet is skipped because it already contains the selected source range. Chart sheets are excluded because they are not worksheets and do not have cell addresses. If you need to update only certain destination tabs, change the loop to identify those sheets by name or other criteria; do not assume that “all tabs” means every sheet in every open workbook.

Values, formulas, formatting, and blank cells

This macro copies cell contents as values only. A formula such as =A1+B1 on the source sheet is replaced by its calculated result on destination sheets. Text, numbers, dates, logical values, and cell errors are transferred as the values they currently represent. Because dates are stored as numbers in Excel, a destination cell with a different number format may display a date serial instead of a familiar date.

Formatting is not copied. The receiving cells keep their existing number formats, fill colors, borders, and other formatting. The macro also does not copy column widths, data validation, comments, hyperlinks as formatting features, or worksheet settings. If formatting matters, use a separate formatting operation after the values are in place.

Destination cells are overwritten. If a source cell is blank, its corresponding destination cell is cleared; this is not a “fill only empty cells” macro. Check the selected source range and the matching destination addresses before running it, especially if those destination cells contain data you need to retain.

Changing the destination or source behavior

The code deliberately uses the same address on each worksheet to make the action predictable. To send A1:B5 to a different range, such as D1:E5, the source and destination addresses need to be handled separately in the code. The two ranges must have the same number of rows and columns for a direct range-to-range transfer.

You can also adapt the loop to exclude particular worksheets, for example a “Summary” tab, or to include only worksheets whose names follow a pattern. Use the worksheet’s exact tab name when adding such a condition, and remember that sheet names can change. A name-based rule should be tested on a workbook copy before it is trusted with important data.

If you want to copy from a fixed source sheet rather than whichever sheet is active, set the source worksheet explicitly and refer to its range by address. That reduces the risk of selecting the wrong tab, but it also makes the macro less flexible if the workbook’s sheet name changes.

Common problems and how to avoid them

Protected destination sheet: A protected worksheet may prevent a write, depending on its protection settings. The macro stops when it hits an error, and worksheets processed earlier may already have changed. Unprotect the necessary sheets or test a copy first.
Unexpected overwritten data: The code replaces every cell in each destination range, including cells corresponding to blanks. Confirm the target address and inspect destination data before running.
Wrong source tab or selection: The active worksheet and current selection determine the source. Click the intended tab and select the intended range before starting the macro.
Different number formats: A value may look different on a destination sheet because its formatting is different. The underlying value can still be correct; inspect the formula bar or apply the required number format separately.
Large selection: Selecting entire columns or many large ranges can take time and may replace more content than intended. Select only the cells you need.
Macro will not run: Confirm the file is saved in a macro-enabled format such as .xlsm and that your security settings allow this trusted macro. Do not enable macros in an unfamiliar file merely to make it run.

VBA compared with manual copying

For a one-off update to a few sheets, copying and using Paste Special > Values manually may be simpler. It makes the source and destination visible, but repeating the operation across many tabs takes time and increases the chance of missing a sheet or pasting into the wrong cells. A macro is more consistent when the same range must be updated repeatedly.

Excel also lets you group worksheet tabs for certain shared edits. Grouping is useful when you intend to make the same edit on several selected sheets, but it is easy to leave sheets grouped and accidentally change them later. For a repeatable source-to-many operation, the macro makes the source range explicit and loops through the worksheets. Whichever method you use, verify the results on a small workbook copy first when the destination data matters.

Safety and workbook recovery

VBA changes are not reversed by Ctrl+Z. Save a separate copy before running a macro, particularly when it will overwrite many worksheets. After it runs, check representative destination cells, including cells that were blank or contained formulas in the source. If the results are wrong, close without saving and reopen the backup, or restore from a known version; do not rely on Undo.

Use only macros you understand or trust. A macro can alter workbook data, and enabling macros from an unknown source can expose you to harmful code. This example is limited to writing the selected values to worksheets in the same workbook, but it still makes irreversible changes within the current editing session.

Frequently Asked Questions

Does the macro copy formulas or only their results?
It copies values only. A formula in the selected source range becomes its current calculated result on the destination worksheets; the formula itself is not transferred.

Will it copy values to hidden sheets?
Yes. The code loops through all worksheets in the source workbook, including hidden and very hidden worksheets. It skips chart sheets because they do not contain cell ranges.

Can I copy the range to a different cell address on each tab?
Not with the code as written. It uses the same address on every worksheet. To use different destinations, the macro needs explicit destination addresses or sheet-specific rules.

Can I run VBA in Excel for the web?
No. Excel for the web does not run or edit VBA macros. Open the workbook in the desktop version of Excel to run this macro.

Can the macro be undone with Ctrl+Z?
No. VBA actions generally clear Excel’s Undo history, so Ctrl+Z will not restore the previous cell contents. Save a backup before running the macro.

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.