Finding a Worksheet with a Specific Value in a Specific Cell

by Allen Wyatt
(last updated October 29, 2016)

2

Kelly has a workbook that contains hundreds of worksheets. Each worksheet represents an individual project for her company. Cell C4 of each worksheet contains the project number for that particular project. Finding individual projects out of all the hundreds in the workbook is cumbersome, at best. She wonders if macro could be created that would all the user to enter a job number, find the worksheet that has that job number in cell C4, and then display that worksheet.

Before addressing how to do this in a macro, you should understand that you may be able to find the desired project using Excel's native tools. All you need to do is to follow these steps:

  1. Press Ctrl+F. Excel display the Find tab of the Find and Replace dialog box.
  2. Click the Options button to expand the dialog box. (See Figure 1.)
  3. Figure 1. The expanded Find tab of the Find and Replace dialog box.

  4. Using the Within drop-down list, choose Workbook.
  5. If cell C4 in each worksheet contains only the project number, select the Match Entire Cell Contents check box.
  6. Click Find Next.

That's it; you should be taken to the first instance of cell that contains your project number. If your workbook contains project numbers in multiple cells (besides cell C4), then using the Find tool can be a bit tedious; you'll need to keep clicking Find Next until you locate the correct worksheet. In such a situation, a macro may be the best solution because it allows you to specifically look at cell C4 and ignore whatever is in any other cell.

The following macro prompts the user to enter a project number. It then searches each worksheet in the workbook for the first worksheet in which cell C4 contains that project number. If found, the macro displays the worksheet and activates the cell. If not found, a message is displayed indicating such.

Sub FindProject1()
    Dim wks As Worksheet
    Dim sCell As String
    Dim sProj As String

    sCell = "C4" 'cell with Project number
    sProj = InputBox("What Project are you looking for?")
    For Each wks In Worksheets
        If wks.Range(sCell) = sProj Then
            wks.Activate
            wks.Range(sCell).Activate
            MsgBox ("Project '" & sProj & "' is in:" & vbCrLf & wks.Name)
            Exit Sub
        End If
    Next wks
    MsgBox ("Project is not found")
End Sub

There are two potential problems with this macro. First, if your project number is actually a number, the macro may not find it as it looks for text strings. Second, if there are multiple worksheets that use the same project number, you'll never know it because the macro always stops at the worksheet with the first matching project number in cell C4. You can overcome both of these problems by making a couple of changes in the macro.

Sub FindProject2()
    Dim wks As Worksheet
    Dim sCell As String
    Dim sProj As String
    Dim vSheets As New Collection
    Dim sTemp As String

    sCell = "C4" 'cell with Project number
    sProj = InputBox("What Project are you looking for?")
    For Each wks In Worksheets
        If wks.Range(sCell).Value = sProj Or _
          wks.Range(sCell).Value = Val(sProj) Or _
          wks.Range(sCell).Text = sProj Then
            vSheets.Add wks
        End If
    Next wks

    Select Case vSheets.Count
        Case 0
            sTemp = "Project " & sProj & " was not found "
            sTemp = sTemp & "in this workbook."
            MsgBox sTemp
        Case 1
            wks.Activate
            wks.Range(sCell).Activate
        Case Else
            sTemp = "Project " & sProj & " was found on more "
            sTemp = sTemp & "than one sheet:" & vbCrLf
            For Each wks In vSheets
                sTemp = sTemp & wks.Name & vbCrLf
            Next wks
            MsgBox sTemp
    End Select
End Sub

With this version of the macro, the project worksheet is displayed only if the project number is unique among all the worksheets. If there are multiple instances where the project number is the same, you are notified of the name of those worksheets, but no worksheet is selected.

You could, if desired, make the macro much simpler by renaming all your worksheets to the project number. This would also have the added benefit of ensuring that no project actually uses two worksheets. (All worksheet names must be unique, so if each worksheet uses a project number as a name, you could only have one worksheet per project number.) If you take this approach, you could use a macro similar to the following to find and jump to the desired worksheet:

Sub FindProject3()
    On Error GoTo ErrorHandler
    Sheets(InputBox("Enter Project Number:")).Activate
    Exit Sub
ErrorHandler: MsgBox "No such Project"
End Sub

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

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

Using the Organizer to Manage AutoText

There are times you need to move your AutoText entries from one template or document to another. Use Organizer to do this ...

Discover More

Placing Textbox Text Into a Worksheet

Want to get rid of your text boxes and move their text into the worksheet? It's going to take a macro-based approach, ...

Discover More

Hiding and Protecting Columns

Want to hide certain columns within a worksheet so the contents are not visible to others? The answer lies in formatting the ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Unhiding Multiple Worksheets

You can hide a bunch of worksheets at the same time, but Excel makes it impossible to unhide a bunch at once. You can, ...

Discover More

Workaround for Multiple Rows of Worksheet Tabs

If you've got a lot of worksheets in your workbook, you may want to display their tabs in to rows at the bottom of the Excel ...

Discover More

Switching Headers in a Frozen Row

Excel allows you to "freeze" rows in your worksheet. What if you want the rows that are frozen to change as you scroll ...

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 four less than 9?

2016-11-11 10:50:33

Steve Jones

In the example of finding a common return (same data, multiple sheets) for the FIND function when checking the workbook, instead of clicking the 'Find Next' button, click the 'Find All' button. This will provide a list of worksheets that has the data, plus the cell where the data is located. This way you can select the link for the desired worksheet and the Find function takes you to the cell on that sheet.


2016-10-29 07:49:40

Robert

When using the project number as a worksheet name, Excel will detect any duplicate project number and assign a trailing (#) to tell you it had found a duplicate project number. Pretty clever.
The # will tell you how many projects have the same name.


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.