Replace the windows with the wb variable

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
sfilename = "Competitor Pricing Monitor Update.*.xls"
If Len(Dir(sPath & "\" & sfilename)) = 0 Then
MsgBox "No file"
Else
sfilename = sPath & "\" & Dir(sPath & "\" & sfilename)
Set wb = Workbooks.Open(sfilename)
End If
wb.Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
wb.Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
wb.Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
Getting closer. I have now got:

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
sfilename = "Competitor Pricing Monitor Update.*.xls"
If Len(Dir(sPath & "\" & sfilename)) = 0 Then
MsgBox "No file"
Else
sfilename = sPath & "\" & Dir(sPath & "\" & sfilename)
Set wb = Workbooks.Open(sfilename)
End If
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

It opens up the file a treat (Also thanks to Bob - your code worked a
treat
as well)

But - the problem I have now is:

The code doesnt recognise the file once its opened. I even tried putting
in:
Windows("sfilename").Activate

John

"Ivan Raiminius" wrote:

Hi John,

sorry, one ommition, should be as follows:
set wb = workbooks.open (sfilename)

Regards,
Ivan