Greetings, Gurus.
I am sending this workbook out for several buyers to use. Part of the macro opens a workbook on their desktop named "Backorder List.xls" and copied data to it. The problem is, the way it is written, I have to go around to each buyers computer and edit it so the vba is looking in the right directory for the file. Is there any way to modify this so that it will open the file "Backorder List.xls" no matter who's desktop it is on?
Sub Copy_To_List()
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Dim wbMyWb As Workbook, wbTemp As Workbook
Dim filess
Set wbMyWb = ActiveWorkbook
'Open the workbook Backorder List.xls and set as wbTemp
If CreateObject("Scripting.FileSystemObject").FileExists("C:\Documents and Settings\jerry.hutcheson\Desktop\Backorder List.xls") = True Then GoTo Exist
GoTo fin
Exist:
ChDrive "C:"
ChDir "C:\Documents and Settings\jerry.hutcheson\Desktop\"
filess = Dir("Backorder List.xls")
Workbooks.Open Filename:=filess
Set wbTemp = ActiveWorkbook
Sheets("Backorders").Select
ActiveSheet.AutoFilterMode = False
'Swap back to wbMyWb to select the Range to Copy
wbMyWb.Activate
Range("A7:N" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
'Return to wbTemp, paste the data at bottom of existing data.
wbTemp.Activate
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
wbMyWb.Activate
'Copy Vendor # and PO # to Backorder List
Sheets("Changes").Range("B2:B4").Copy
wbTemp.Activate
Range("O65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Selection.Copy
Range(Selection, "Q" & Cells(Rows.Count, "A").End(xlUp).Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False
wbTemp.Close SaveChanges:=True
fin:
End Sub
Thanks in advance for any help you can offer.
Bookmarks