Copying a Set Range from Multiple Worksheets to a New Worksheet

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



Figure 1. The New Name dialog box.

4. In the Name field, enter the name ListSheets (note that this is a single word, with no spaces).
5. In the Refers To field, enter the following formula:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1),1),"")

6. Click the OK button to finish creating the named range. The new range should appear in the Name Manager dialog box.
7. 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.

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