How to copy and paste data into multiple worksheets in Excel?

Excel offers an efficient "Fill" > "Across Worksheets" feature, which allows users to copy a selected block of data and paste it into the identical positions of multiple worksheets simultaneously. This approach works well when you want to distribute the same information (such as a template, header, or a constant value) to equivalent places in several sheets without having to copy and paste one by one. Here’s how you can use this feature:

1. Select the range in your current worksheet that you intend to copy and paste into other worksheets. Ensure that this range is correctly formatted and contains the data you wish to replicate.

2. Move to the Sheet Tab bar at the bottom of your workbook. To select multiple worksheets:

• Hold down the "Ctrl" key and click the sheets one by one to select nonadjacent sheets.
• Hold the "Shift" key, click the first worksheet, then the last sheet you want, to select all sheets in between.

3. With all intended sheets selected, go to the "Home" tab on the Ribbon, then click "Fill" > "Across Worksheets". This command is specially designed to help users execute batch operations across several sheets. See screenshot:

4. In the "Fill Across Worksheets" dialog box, choose the type of content you wish to paste:

• All: Copies the data, formulas, and formatting together (recommended if you want a complete duplication).
• Contents: Copies only the cell values and formulas, without the formatting.
• Formats: Copies only the formatting and not the actual content.

The originally selected range of data is now simultaneously replicated into the same range in all the chosen worksheets. This method is ideal for updating templates or standard layouts across department or monthly report sheets.

For users who are comfortable with automation or have regularly recurring copy-paste tasks that the standard Excel tools can’t easily manage, using a VBA macro can provide more flexibility. VBA allows you to define exactly how and where data is copied and pasted, supporting both simple and complex scenarios, such as copying from different ranges, handling variable sheet names, or applying custom rules during the operation.

1. Click Developer > Visual Basic. In the Microsoft Visual Basic for Applications window that appears, select Insert > Module, and paste the following VBA code into the module window:

Sub CopyRangeToMultipleSheets()
    Dim ws As Worksheet
    Dim SrcRange As Range
    Dim DestRange As Range
    Dim SheetName As String
    Dim ListOfSheets As Variant
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set SrcRange = Application.Selection
    Set SrcRange = Application.InputBox("Select the range to copy:", xTitleId, SrcRange.Address, Type:=8)
    
    ListOfSheets = Application.InputBox("Enter target sheet names separated by commas (e.g. Sheet2,Sheet3):", xTitleId, "", Type:=2)
    
    Application.ScreenUpdating = False
    
    For i = 0 To UBound(Split(ListOfSheets, ","))
        SheetName = Trim(Split(ListOfSheets, ",")(i))
        
        If SheetName <> "" Then
            Set ws = Worksheets(SheetName)
            Set DestRange = ws.Range(SrcRange.Address)
            
            SrcRange.Copy
            DestRange.PasteSpecial xlPasteAll
        End If
    Next i
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    MsgBox "Copying completed.", vbInformation
End Sub

2. Click the Run button (or press F5) to execute the macro. You’ll be prompted to select the range you want to copy, then prompted to enter the target worksheet names (comma-separated if more than one). The macro will then copy your chosen data to the same range in each specified worksheet.

This method introduces Kutools for Excel’s "Dynamically Refer to Worksheets" tool, a feature designed to help you gather the same cell (or range) from several different worksheets and display the results together in a new summary worksheet. This is particularly helpful for compiling standardized reports or collecting identical metrics, such as monthly sales from each team or store, without manual repetition.

1. First, create a new worksheet to act as your summary or destination sheet by clicking the "New" button in the Sheet Tab bar. This helps keep your collected data separate and organized.

2. Next, select the cell (for example, cell D7) in your new summary worksheet where you want to display the imported value. Go to the "Kutools" tab on the Ribbon, click "More" under the "Formula" group, and select "Dynamically Refer to Worksheet". See screenshot:

3. In the "Fill Worksheets References" dialog box:

• Select an appropriate option from the "Fill Order" drop-down—this determines how references are filled, either row-by-row or column-by-column.
• Check the worksheets from which you want to pull data in the "Worksheet List."
• Click the "Fill Range" button to populate the selected area with references from those sheets, then click "Close" to finish.

The content from the chosen cell (or range) in each worksheet will be dynamically listed in your summary worksheet, keeping all data linked and automatically updated if the original cell changes. See screenshot:

Excel formula allow you to dynamically reference data from other worksheets, so you can pull information from the same cell or range across several sheets without repeatedly copying and pasting. This is useful for summary sheets, dashboards, or whenever you need to display up-to-date data aggregated from various sheets. Two key techniques include using the INDIRECT function.

Using INDIRECT for dynamic references:
If your sheet names are not adjacent or you want to build references dynamically (for example, based on a list of sheet names in your workbook), the INDIRECT function is helpful. This can also be used to retrieve data from specific cells in non-contiguous sheets.

1. Suppose you have a list of target sheet names in A2:A4 (e.g., Sheet1, Sheet2, Sheet3), and you want to fetch cell B5 from each sheet into cell B2, then B3, etc. In B2, enter:

=INDIRECT("'"&A2&"'!B5")

Copy this formula down alongside your list. Each cell will show the value in cell B5 from each respective sheet.

When you need to consolidate or merge all records stored in multiple worksheets into a single sheet for analysis or reporting, Kutools for Excel provides a convenient "Combine (Worksheets)" utility. This approach is especially suitable for compiling historical data, merging worksheets from different departments, or producing master lists for further data processing.

1. Click on "Kutools Plus" > "Combine" to open the Combine Worksheets dialog box. This powerful tool simplifies complex batch operations in large workbooks.

2. In the wizard's Step 1 of 3, select "Combine multiple worksheets from workbook into one worksheet", then click "Next". See screenshot:

3. In Step 2 of 3:

• In the "Workbook list" section, ensure only the current workbook is checked.
• In the "Worksheet list", select all sheets you wish to combine.
• The "same range" button is useful for quickly specifying identical ranges in each worksheet, streamlining setup for workbooks with consistent structure.

4. Step 3 of 3 allows you to adjust additional settings, such as handling headers, managing blank rows, and formatting—configure as required, then click "Finish".

5. A prompt will appear to ask if you want to save your combination scenario. Select "Yes" to save for future use, or "No" if you don't need to reuse the setup.

The utility will finish by copying and consolidating data from all chosen worksheets into a new worksheet, simplifying further data analysis or reporting in one unified location.

Mirror/link cells across worksheets in Excel

Define named range across worksheets in Excel

Create chart across/from multiple worksheets in Excel

Apply conditional formatting across worksheets/workbooks