Find value in a 2nd workbook

[Rafi]

I need to search for a value found in one workbook on a second workbook.
Unfortunately, the .Find function seems to find the value in the first
workbook (valuePointLicenses(0).XLS) rather than the one where I want it to
search (SDI_Sales_Alignments_ 022708.xls)

Thanks for the help

Option Explicit
Dim Temp As Long
Dim TempName As Range
Dim LastRow As Long

Sub Copy_Alignments()
Application.Windows("valuePointLicenses(0).XLS").Activate
MsgBox ActiveWorkbook.Name
Cells(2, 1).Select
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Temp = ActiveCell.Value
Application.Windows("SDI_Sales_Alignments_ 022708.xls").Activate
MsgBox ActiveWorkbook.Name
With ActiveSheet
MsgBox ActiveCell.Value
Set TempName = Cells.Find(What:=Temp, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
End With
If TempName Is Nothing Then
MsgBox "Not Found"
Else
MsgBox TempName.Address
End If
Windows("valuePointLicenses(0).XLS").Activate
End Sub

 

[OssieMac]

I tested this in Excel 2007 and it worked OK. However, in lieu of:-

With ActiveSheet

Try using:-

With ActiveWorkbook.ActiveSheet

 

[Rafi]

OssieMac - Thanks for the quick reply.

Any other thoughts? I have changed With ActiveSheet to With
ActiveWorkbook.ActiveSheet and it still results in a found value on the
original workbook rather than the one which is supposed to be active -
SDI_Sales_Alignments_ 022708.xls

Thanks

I tested this in Excel 2007 and it worked OK. However, in lieu of:-

With ActiveSheet

Try using:-

With ActiveWorkbook.ActiveSheet

--
Regards,

OssieMac

I need to search for a value found in one workbook on a second workbook.
Unfortunately, the .Find function seems to find the value in the first
workbook (valuePointLicenses(0).XLS) rather than the one where I want it to
search (SDI_Sales_Alignments_ 022708.xls)

Thanks for the help

Option Explicit
Dim Temp As Long
Dim TempName As Range
Dim LastRow As Long

Sub Copy_Alignments()
Application.Windows("valuePointLicenses(0).XLS").Activate
MsgBox ActiveWorkbook.Name
Cells(2, 1).Select
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Temp = ActiveCell.Value
Application.Windows("SDI_Sales_Alignments_ 022708.xls").Activate
MsgBox ActiveWorkbook.Name
With ActiveSheet
MsgBox ActiveCell.Value
Set TempName = Cells.Find(What:=Temp, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
End With
If TempName Is Nothing Then
MsgBox "Not Found"
Else
MsgBox TempName.Address
End If
Windows("valuePointLicenses(0).XLS").Activate
End Sub