how to find the data in between the sheets?

[Junaid]

Hi All,

I have a doubt, i have 25 sheets in the workbook in that i want to find the
data among the sheets, the data which i want to find, that will be in the 26
sheet (A1).

Thanks in advance!

 

[Norman Jones]

Hi Junaid,

In a standard module, try something
like:

'==========>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim SH2 As Worksheet
Dim Rng As Range
Dim sStr As String

Set WB = Workbooks("myBook.xls") '<<==== CHANGE
Set SH2 = WB.Sheets("Sheet26") '<<==== CHANGE

sStr = SH2.Range("A1").Value

For Each SH In WB.Worksheets
On Error Resume Next
With SH
If .Name <> SH2.Name Then
Set Rng = .Cells.Find _
(What:=sStr, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
Searchorder:=xlByRows, _
MatchCase:=False)
End If
End With
On Error GoTo 0
If Not Rng Is Nothing Then
Exit For
End If
Next SH

If Not Rng Is Nothing Then
MsgBox "The value " & sStr _
& " has found at " _
& Rng.Address(External:=True)
Else
MsgBox "The value " & sStr _
& " has not been found"
End If

End Sub
'<<============