Sorry Joel, I mentioned that I would post the other macros. They are
identical to this except for the message prompt and destination sheets which
I should be able to sort out myself.

Cheers
--
Jim


"Jim G" wrote:

Thanks Joel, I was hoping to have the data files close after the data was
pasted. However, I was being a bit opportunistic asking if the user could be
prompted for the file name (job No) to save them scolling through dozens in
the list-nice but not essential.

The following macro is activated on file open to select the first data file.
For clarity I'll post the others separtely. They are activated via button.

Sub OpenLedger()

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

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

If Response = vbYes Then
Dim myFileName As Variant
Dim wkbk As Workbook
Dim MyPath As String

MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji"
MsgBox "Select a Job GL File to use"
ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji"
'ChDir "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji"
ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well
myFileName = Application.GetOpenFilename("Excel Files, *.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

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

Else
Exit Sub
End If
ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
'wkbk.Close SaveChanges:=False '---attempts to make it work
'ActveWindow.Close '---Attempts to make it work
Windows("JobCost Template V3.3.xls").Activate
Sheets("Ledger").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("A16").Select
Application.DisplayAlerts = True


End Sub
--
Jim


"Joel" wrote:

I'm not sure from your description if you are just asking to close the 3
files, or also asking about a better way of opening the files.

Everything you are asking is possible. I think upgrading your poresent
macros rather than add ing new macros is the best way of going. It would be
easier if you posted the old macros so they can be modified.

"Jim G" wrote:

I have a template that collates and summaries data from three data sheets
created by our database.

Each file is saved as a job number followed by GL, L or J. For example,
General Ledger data is exported to a file called 1234GL.xls, Labour data is
exported to a file called 1234L.xls, etc. Each file has only one sheet with
the same name as the file name without the xls extension.

I have destination sheets in a Job Summary Template called "Ledger",
"Labour" and 'JobCard". Macros are triggered when the destination sheet is
updated.

Currently users are taken via a macro to the file directory and prompted to
select a data file. The entire contents of the ActiveSheet is copied to the
sheet allocated to that macro (€œLedger€�, €œLabour€� etc). Unfortunately, the
user must close the three open data sheets before continuing to the next job
summary.

Would it be possible for the user to be prompted for the job number, have
this added to the €œGL, €œL or €œJ€� and the file selected for them, and the
contents of the ActiveSheet copied to the named target sheet and the data
file closed.

If the prompt is too difficult I would be happy with being able to close the
data file after the data is copied to the named target sheet.
--
Jim