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. ...


Changing Table Cell Text Direction

When creating a table, you can turn the orientation of the text, within a cell, by ninety degrees in either direction ...

Discover More

Linking Word Documents

Want to add one document to another document? You can do it by adding links, described in this tip.

Discover More

Changing Pronoun Gender

Sometimes it is necessary to change the gender of various pronouns used in a document. If you have a stock document that ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Workbook Events

You can create macros that run whenever Excel detects a certain event happening within an entire workbook. This tip ...

Discover More

Resetting Default Names for New Worksheets

When you add a new worksheet to a workbook, Excel gives it a default name that consists of "Sheet" followed by a number. ...

Discover More

Engineering Calculations

Need to normalize your data in some way so that all your values are in a given format? This tip presents a number of ...

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}] (all 7 characters, in the sequence shown) 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 9 + 5?

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.