Copying a Set Range from Multiple Worksheets to a New Worksheet

by Allen Wyatt
(last updated April 18, 2020)

3

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:
  7. =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1),1),"")
    
  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:

=INDIRECT(INDEX(ListSheets,ROWS($A$1:$A1))&"!"&CELL("address",A$146))

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

    Worksheets(1).Select
    Worksheets.Add
    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.

Note:

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

MORE FROM ALLEN

Conditionally Deleting Rows

Want to delete a bunch of rows in a worksheet based on the value in a certain cell of each row? There are a couple of ...

Discover More

Determining an Integer Value

One of the math functions you can use in your macros is the Int function. It provides a way for you to derive an integer ...

Discover More

Defining Protected Sections as a Building Block

Building Blocks can be very useful in creating documents from standard parts and pieces. But what if the text you want to ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Magnifying Only the Current Cell

You can use the Zoom feature of Excel to magnify what Excel shows of your workbook, but it affects the entire screen. ...

Discover More

Setting Row Height in a Macro

Macros can be used to change the formatting of your worksheet, if desired. One change you might want to make is to the ...

Discover More

Worksheet Events

You can create macros that are automatically executed whenever certain events occur within a worksheet. This tip details ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 three more than 4?

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
sNewName
(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
w.Range(sRange).Copy
' Paste the data
Cells(lRow, 2).PasteSpecial xlPasteValues
' Turn off copy selection
Application.CutCopyMode = False


2020-04-18 05:14:58

Rene

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
Subscribe

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.