Copying a Set Range from Multiple Worksheets to a New Worksheet

by Allen Wyatt
(last updated April 18, 2020)


Om has hundreds of differently named worksheets all in the same workbook. The structure of each worksheet is essentially the same. He wants to copy a fixed range (A146:O146) from each worksheet to a new worksheet, one row after another.

There are a few ways you can go about doing this. For my money, a simple macro is the best bet. Before getting into the macro-based approach, however, there is a way you can do it using formulas. One rather unique way relies upon defining a formula in the Name Manager. Follow these steps to start:

  1. Display the Formulas tab of the ribbon.
  2. In the Defined Names group, click the Name Manager tool. Excel displays the Name Manager dialog box.
  3. Click the New button. Excel displays the New Name dialog box. (See Figure 1.)
  4. Figure 1. The New Name dialog box.

  5. In the Name field, enter the name ListSheets (note that this is a single word, with no spaces).
  6. In the Refers To field, enter the following formula:
  8. Click the OK button to finish creating the named range. The new range should appear in the Name Manager dialog box.
  9. Click the Close button to dismiss the Name Manager dialog box.

With the formula defined in this way, you can then go to the worksheet on which you want to amalgamate all those ranges. (Let's say that the name of this worksheet is "Summary.") I strongly suggest making sure this worksheet is the very last one in your workbook. On the Summary worksheet, put the following formula in column A of any row:


Copy the formula down however many rows are necessary to represent all the worksheets. In other words, if you have 25 worksheets (not counting the Summary worksheet), copy the formula down 24 rows. Counting the original, you should now have the formula appear in a total of 25 rows.

A side note here: The ListSheets formula—the one you defined in the Name Manager—returns an array of worksheet names. The ROWS function is used to determine which element of that array is returned through the INDEX function. If your Summary worksheet is not the last one in your workbook, it can easily be returned by ListSheets and you will end up pulling values from it. This is undoubtedly something you don't want to do, which is why I suggested making sure Summary was the last worksheet in the workbook.

Now simply copy the formulas from column A to the right so that you have them all the way through column O. The result is that you will have the values from A146:O146 in the cells containing the formulas.

Earlier I said that I think a macro-based approach is the best bet. Here's a short little macro that demonstrates why this is the case.

Sub CopyRange()
    Dim w As Worksheet
    Dim sRange As String
    Dim lRow As Long

    sNewName = "Summary" 'Name for summary worksheet
    sRange = "A146:O146" 'Range to copy from each worksheet

    ActiveSheet.Name = sNewName

    lRow = 2
    For Each w In Worksheets
        If w.Name <> sNewName Then
            'Comment out the following line if you don't want to
            'include worksheet names in the summary sheet
            Cells(lRow, 1) = w.Name
            'If you commented out the previous line, make a change
            'in the following line: change (lRow, 2) to (lRow, 1)
            w.Range(sRange).Copy Cells(lRow, 2)
            lRow = lRow + 1
        End If
    Next w
End Sub

Note that there are two variables (sNewName and sRange) that are set at the beginning of the macro. These represent the name you want used for the new summary worksheet created by the macro and the range of cells you want to copy from each worksheet.

The macro then makes the first worksheet in the workbook active and adds a new worksheet to be used for summarization. This worksheet is assigned whatever name you specified in the sNewName variable. The macro then steps through a loop checking each of the other worksheets. As long as the worksheet isn't the summary one, then the name of the worksheet is placed into column A of the summary sheet and the range specified in the sRange variable (A146:O146) is copied to the summary worksheet starting at column B.

The macro approach is fast and easy. Plus, if you ever need to redo your summary sheet, just delete the old one and rerun the macro.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9812) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Creating a TOC that Includes Specific Styles

Want to create a special TOC that contains different elements of your document? It's easy to do if you consistently use ...

Discover More

Stopping the Deletion of Cells

You can delete cells from a worksheet, and Excel will move the remaining cells either to the left or upwards. Deletions, ...

Discover More

Remembering Workbook Settings from Session to Session

You can spend a lot of time getting your workbook to look "just right." Wouldn't it be great if Excel was able to ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Highlighting Pattern Violations

A common part of working with text strings in a worksheet is normalizing those strings so that they follow whatever rules ...

Discover More

Skipping Hidden Rows in a Macro

As your macro processes information in a worksheet, you may want to make sure that it skips over rows that are hidden. ...

Discover More

Determining an ANSI Value in a Macro

Need to know the character code used for a particular character? In a macro you can use the Asc function to determine the ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two minus 0?

2020-04-19 10:37:10

Alex B

Oops item 1 in my previous comment should read:
Dim sNewName

2020-04-18 22:42:34

Alex B

The macro above has a couple of errors in it:
1) Add the following statement
(most people will have Option Explicit set and this name needs to be defined)

2) Assuming A146:O146 has formulas in it, your copy results will come back with #REF!
since the formulas won't work in the new location
• Replace
w.Range(sRange).Copy Cells(lRow, 2)
• With
' Paste the data
Cells(lRow, 2).PasteSpecial xlPasteValues
' Turn off copy selection
Application.CutCopyMode = False

2020-04-18 05:14:58


There is also another method. On your summary sheet you can have the first column contain the names of the tabs. In each row, use the "Indirect" function to copy from each tab. The easiest way to do this is to fill the first cell with the reference you want to copy from e.g. from the above example, in cell B1, you would have the formula ={tab} A$146. Now change the formula to Indirect and have {tab} refer to the tab name in A1 ($A1). This will give you the result of Tab cell A146. The formula can be copied down and to the right. New rows can be added, all you need to do is change the tab name.
If you want to show various cells which aren't next to each other, you can add a line at the top where you show the column which needs to be selected. The formula should then also refer to the column on row 1 to pick up the column reference.
This can be expanded as much as you want, adding reference lines or columns to allow selection of any cell in a tab. This method of indirect function makes it clearer for the user where information comes from.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.