C
Corey
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter the Quote Number to Find", "Splice
Tech Pty Ltd Unanderra NSW..")
SaveDriveDir = CurDir
MyPath = "Z:\Costing Sheets"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer
mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("J59").Value = input1 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
Loop
The above code searches the designated folder through ALL .xls(Excel files)
and looks for a matching value in cell(J59) from what was placed into the
input box.
Just recently it stopped working.
It seems to search throught he designated folder, but fails to find anything
to match, ALTHOUGH there is a match.
Any ideas how to rectify this?
Corey....
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter the Quote Number to Find", "Splice
Tech Pty Ltd Unanderra NSW..")
SaveDriveDir = CurDir
MyPath = "Z:\Costing Sheets"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer
mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("J59").Value = input1 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
Loop
The above code searches the designated folder through ALL .xls(Excel files)
and looks for a matching value in cell(J59) from what was placed into the
input box.
Just recently it stopped working.
It seems to search throught he designated folder, but fails to find anything
to match, ALTHOUGH there is a match.
Any ideas how to rectify this?
Corey....