R
RiverGully
Starting in the first worksheet (Sheet00.Name), I wish to search all visible
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]
Private Sub FindCellContent()
'On Error GoTo addError1
Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet
' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell
' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select
' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next
'addError1:
' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub
End Sub
Thank you... Clive
worksheets for a value (value of the active cell).
(The user of the spreadsheet will click a command button for this VBA to run)
[I have turned off the On Error instruction, while testing this out...]
Private Sub FindCellContent()
'On Error GoTo addError1
Dim CellLocation As Range
Dim CellContent As String
Dim Sh As Worksheet
' Capture cell information
Set CellLocation = ActiveCell
CellContent = ActiveCell
' Start at the first Worksheet
Sheets(Sheet31.Name).Select
Range("A1").Select
' Search for CellContent in all Visible Worksheets
For Each Sh In ThisWorkbook.Worksheets
' If Sh.Visible = xlSheetVisible Then
If Cells.Find(What:=CellContent, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
Exit For
End If
' End If
Next
'addError1:
' Application.GoTo Reference:=CellLocation
' MsgBox "Cell content not found in other worksheets"
' Exit Sub
End Sub
Thank you... Clive