I forgot that you had uploaded the master WorkBoo, so I've just tested and it works OK.
I've made a slight change though, just switched off screen updating to hide the other workbook opening.
You will find that Column Q in the Master WorkBook is formatted as Currency and needs changing to a Number Format.
Code Display MoreSub CopyOrderNum() Dim oWb As Workbook Dim FndCell As Range Dim sFilePath As String, strFindWhat As String, strReplace As String Dim oFD As FileDialog Application.ScreenUpdating = False If IsEmpty(Range("F7")) Then MsgBox "Please enter a Segment" Exit Sub Else: strFindWhat = Range("F7").Value End If If IsEmpty(Range("I7")) Then MsgBox "Please enter Order Number" Exit Sub Else: strReplace = Range("I7").Value End If Set oFD = Application.FileDialog(msoFileDialogFilePicker) On Error Resume Next With oFD .AllowMultiSelect = False .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm; *.xlsb", 1 .Show If .SelectedItems.Count > 0 Then sFilePath = .SelectedItems.Item(1) Else Exit Sub End If End With On Error GoTo 0 ''/// open the selected file, annd set it the variable oWb Set oWb = Workbooks.Open(sFilePath) Set FndCell = oWb.Sheets(1).Cells.Find(strFindWhat) If Not FndCell Is Nothing Then FndCell.Offset(, 16).Value = strReplace Else: MsgBox "The data you are searching for does not exist" End If Application.ScreenUpdating = True oWb.Close True End Sub
Hi Roy,
please find below the your original VBA with amendments that reflect my needs. Collum Q of the master workbook just has standard cells in it.
After populating the relevent cells and the search and find workbook, when i run the macro it does not open up the master workbook automatically. Instead it open up the standard explorer box and I have got to navigate to the master workbook file and open it manually. After that point everything is automated. VBA Below
Sub CopySRNum()
Dim oWb As Workbook
Dim FndCell As Range
Dim sFilePath As String, strFindWhat As String, strReplace As String
Dim oFD As FileDialog
If IsEmpty(Range("F7")) Then
MsgBox "Please enter DPR number"
Exit Sub
Else: strFindWhat = Range("F7").Value
End If
If IsEmpty(Range("I7")) Then
MsgBox "Please enter SR number"
Exit Sub
Else: strReplace = Range("I7").Value
End If
Set oFD = Application.FileDialog(msoFileDialogFilePicker)
On Error Resume Next
With oFD
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm; *.xlsb", 1
.Show
If .SelectedItems.Count > 0 Then
sFilePath = .SelectedItems.Item(1)
Else
Exit Sub
End If
End With
''/// open the selected file, annd set it the variable oWb
Set oWb = Workbooks.Open("\\DFW03018\Data_DFZ70069\199711009\workgroup\DATA_MAN\shared\RAR Launcher\Master Tracker.xlsm")
Set FndCell = oWb.Sheets(1).Cells.Find(strFindWhat)
If Not FndCell Is Nothing Then
FndCell.Offset(, 16).Value = strReplace
Else: MsgBox "The data you are searching for does not exist"
End If
oWb.Close True
End Sub
Display More