Handy Excel Macros
(Microsoft Excel 2003)

Find Data across multiple pages

This searches rightwards across multiple pages for the value you type.

The search includes the page you are on so if you don't want to search that page then go to the next right page.

Option Compare Text

Sub Find_after_unfilter_sheets()

' Written by Barrie Davidson & expanded by Mark van Zoest

'Searches from current worksheet rightwards (Search includes current one)

Application.ScreenUpdating = False

Dim SheetCount As Integer

Dim Counter As Integer

Dim CurrentSheet As Integer


CurrentSheet = ActiveSheet.Index

SheetCount = ActiveWorkbook.Sheets.Count

For Counter = CurrentSheet To SheetCount



With ActiveSheet

   If .FilterMode Then .ShowAllData

 End With


Next Counter

Application.ScreenUpdating = True




'Searches from current worksheet onwards (Search includes current sheet)

Dim datatoFind As String

Dim sheetCount2 As Integer

Dim Counter2 As Integer

Dim currentSheet2 As Integer

On Error Resume Next

currentSheet2 = ActiveSheet.Index

datatoFind = InputBox("Please enter the value to search for")

If datatoFind = "" Then Exit Sub

sheetCount2 = ActiveWorkbook.Sheets.Count

If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)

For Counter2 = currentSheet2 To sheetCount2


Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _False).Activate

If ActiveCell.Value = datatoFind Then Exit Sub

Next Counter2

If ActiveCell.Value <> datatoFind Then

MsgBox ("Value not found")


End If

End Sub


Back to Index Page