VBA for open a different workbook.

Hi jevee

This code should get youi what you want, just change ranges and workbook names to suit.

Sub OpenandFind()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim Wsht As Worksheet
Dim SLookfor As String
Dim SFoundIt As String
Application.ScreenUpdating = False
'Value to look for
SLookfor = Sheets("Sheet1").Range("A1")
'Open workbook to look in
Workbooks.Open Filename:="C:\My Documents\Doodlings\Book1.xls"

'Look through each sheet until found
For Each Wsht In ActiveWorkbook.Worksheets
On Error Resume Next
SFoundIt = Wsht.Cells.Find _
(What:=SLookfor, after:=Wsht.Cells(1, 1)).Address
'Value found show sheet name and cell address
If SFoundIt <> "" Then
Application.ScreenUpdating = True
MsgBox "Found " & SLookfor & " on " & Wsht.Name _
& " cell " & SFoundIt, vbInformation
End
End If
Next Wsht
Application.ScreenUpdating = True
'Value not found
MsgBox "Cannot find it", vbInformation
End Sub

Dave

OzGrid Business Applications

Many tks Dave one more question i want the coursor to stay in the right cell where it found the data? Otherwise everything worked perfectly
Regards
jevee Hi jevee Sub OpenandFind() 'Written by OzGrid Business Applications 'www.ozgrid.com Dim Wsht As Worksheet Dim SLookfor As String Dim SFoundIt As String Application.ScreenUpdating = False 'Value to look for SLookfor = Sheets("Sheet1").Range("A1") 'Open workbook to look in Workbooks.Open Filename:="C:\My Documents\Doodlings\Book1.xls" 'Look through each sheet until found For Each Wsht In ActiveWorkbook.Worksheets On Error Resume Next SFoundIt = Wsht.Cells.Find _ (What:=SLookfor, after:=Wsht.Cells(1, 1)).Address 'Value found show sheet name and cell address If SFoundIt <> "" Then Application.ScreenUpdating = True MsgBox "Found " & SLookfor & " on " & Wsht.Name _ & " cell " & SFoundIt, vbInformation End End If Next Wsht Application.ScreenUpdating = True 'Value not found MsgBox "Cannot find it", vbInformation End Sub

Dave

jevee : : Hi jevee : This code should get youi what you want, just change ranges and workbook names to suit. : : Sub OpenandFind() : 'Written by OzGrid Business Applications : 'www.ozgrid.com : Dim Wsht As Worksheet : Dim SLookfor As String : Dim SFoundIt As String : Application.ScreenUpdating = False : 'Value to look for : SLookfor = Sheets("Sheet1").Range("A1") : 'Open workbook to look in : Workbooks.Open Filename:="C:\My Documents\Doodlings\Book1.xls" : 'Look through each sheet until found : For Each Wsht In ActiveWorkbook.Worksheets : On Error Resume Next : SFoundIt = Wsht.Cells.Find _ : (What:=SLookfor, after:=Wsht.Cells(1, 1)).Address : 'Value found show sheet name and cell address : If SFoundIt <> "" Then : Application.ScreenUpdating = True : MsgBox "Found " & SLookfor & " on " & Wsht.Name _ : & " cell " & SFoundIt, vbInformation : End : End If : Next Wsht : Application.ScreenUpdating = True : 'Value not found : MsgBox "Cannot find it", vbInformation : End Sub : Dave

No problem Jevee, we can use the Application.GoTo for this. The "True" argument will place the found cell in the top left hand corner of your screen.

Sub OpenandFind()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim Wsht As Worksheet
Dim SLookfor As String
Dim SFoundIt As String
Application.ScreenUpdating = False
'Value to look for
SLookfor = Sheets("Sheet1").Range("A1")
'Open workbook to look in
Workbooks.Open FileName:="C:\My Documents\Doodlings\Book1.xls"

'Look through each sheet until found
For Each Wsht In ActiveWorkbook.Worksheets
On Error Resume Next
SFoundIt = Wsht.Cells.Find _
(What:=SLookfor, after:=Wsht.Cells(1, 1)).Address
'Value found show sheet name and cell address
If SFoundIt <> "" Then
Application.ScreenUpdating = True
MsgBox "Found " & SLookfor & " on " & Wsht.Name _
& " cell " & SFoundIt, vbInformation
Application.Goto Range(SFoundIt), True
End
End If
Next Wsht
Application.ScreenUpdating = True
'Value not found
MsgBox "Cannot find it", vbInformation
End Sub

Dave

OzGrid Business Applications

Many thanks Dave it worked perfect.
But i also want a similar function I´ve tried to change som of the function but had some runtime errors.

I´ve changed the line:
SLookfor = Sheets("Sheet1").Range("A1")
to:
SLookfor = Sheets("Sheet1").Range(ActiveCell)

But get runtime error on this. Is it somthing else i have to change to have it search the value from the active cell??

Regards
Jevee

Sub OpenandFind() 'Written by OzGrid Business Applications 'www.ozgrid.com Dim Wsht As Worksheet Dim SLookfor As String Dim SFoundIt As String Application.ScreenUpdating = False 'Value to look for SLookfor = Sheets("Sheet1").Range("A1") 'Open workbook to look in Workbooks.Open FileName:="C:\My Documents\Doodlings\Book1.xls" 'Look through each sheet until found For Each Wsht In ActiveWorkbook.Worksheets On Error Resume Next SFoundIt = Wsht.Cells.Find _ (What:=SLookfor, after:=Wsht.Cells(1, 1)).Address 'Value found show sheet name and cell address If SFoundIt <> "" Then Application.ScreenUpdating = True MsgBox "Found " & SLookfor & " on " & Wsht.Name _ & " cell " & SFoundIt, vbInformation Application.Goto Range(SFoundIt), True End End If Next Wsht Application.ScreenUpdating = True 'Value not found MsgBox "Cannot find it", vbInformation End Sub

Dave

Hi Jevee

Try this:

SLookfor = ActiveCell

Dave
OzGrid Business Applications