I have a template that requires new data from 4 files. I want to open each
in turn and copy the contents into specified sheets in the template. Each
file is created by another program that always uses the same file name. The
users add an extension to the name for each company they are working on to
keep htem unique.

I have the following code that opens the first file and copies the new data
okay. Is there a way to modify this to use the one user input for each file
and copy the data in turn to each worksheet?

Sub OpenFile()

Dim myFileName As Variant
Dim wkbk As Workbook
Dim MyPath As String
Dim sFilename As String
Dim fExitDo As Boolean
Dim sFileType As String
Dim sFileOpen As String
Dim sFileBudget As String
Dim sFileJobList As String
Dim sFileOrders As String
Dim sFileLedger As String

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Select ""YES"" to proceed to Open WIP Data Files, ""NO"" to view
Current File only"
Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Open New WIP Data Files " ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then

sFileBudget = "jobba1-" '--Job budget file to copy to sheet "Budget"
sFileJobList = "jobbl1-" '--Job Job List file to copy to sheet WIP"
sFileOrders = "salcustd1-" '--Job Customer Sales Orders file to copy to
sheet "Orders"
sFileLedger = "genjrld1-" '--Job General Ledger Detail file to copy to
sheet "Ledger4900"

MyPath = "S:\MYOB Data Files\WIPData\"
ChDrive "S:\"
ChDir MyPath

'--users are to add a file name extension to the standard reports and save
as .XLS files. It will be same for all 4 data files
sFilename = InputBox("Please Provide ONLY the Name you saved the file as.
EG: DEMO")

FilesInPath = Dir(MyPath & "*.xl*") 'Check to see if files exist (Ron
DeBruin)
If sFilename = "" Then
Exit Sub 'user hit cancel
If FilesInPath = "" Then
MsgBox "No files found-make sure you have saved your
files in the correct location"
Exit Sub
End If
End If

sFileOpen = MyPath & sFileBudget & sFilename & ".xls"
fExitDo = False


Set wkbk = Workbooks.Open(Filename:=sFileOpen)

Else
Exit Sub
End If

ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
Windows("WIP Template V1.xls").Activate
Sheets("Budget").Select
Cells.Select
ActiveSheet.Paste

wkbk.Close Savechanges = False

Application.DisplayAlerts = True

End Sub




--
Jim