by Allen Wyatt
(last updated October 29, 2016)
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:
Figure 1. The expanded Find tab of the Find and Replace dialog box.
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel provides ways to reference the column or row number of a cell, but it doesn't provide a built-in way to reference a ...Discover More
One day you are just editing your worksheet like you normally do, then you see an error that says "Cannot shift object ...Discover More
Excel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.