I have the following code that opens to a predefined dir for the user to
select a file (the Subject), the contents of the only sheet are copied to a
specified sheet ("Ledger") in the Template file. I need to close the Subject
sheet after the contents are copied. Otherwise, I have several very large
sheets that remain open until closed manually. My code is an adaption of
other macros used elsewhere (thanks to the many contibutors here) and fails
at Selection.PasteSpecial. Sorry if it's a bit messy.

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 - this didn't work
myFileName = Application.GetOpenFilename("Excel Files, *.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

'MsgBox myFileName & " has been selected" 'just to prove that you got it

'to open the file now that you have it:
Set wkbk = Workbooks.Open(Filename:=myFileName)
Else
Exit Sub
End If
ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("JobCost Template V3.3.xls").Activate
Sheets("Ledger").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("Summary").Select
Application.DisplayAlerts = True

End Sub
--
Jim

After copying your data from the source workbook, add this line of code to
close it w/o saving:

wkbk .Close SaveChanges:=False

--
Kevin Backmann


"Jim G" wrote:

I have the following code that opens to a predefined dir for the user to
select a file (the Subject), the contents of the only sheet are copied to a
specified sheet ("Ledger") in the Template file. I need to close the Subject
sheet after the contents are copied. Otherwise, I have several very large
sheets that remain open until closed manually. My code is an adaption of
other macros used elsewhere (thanks to the many contibutors here) and fails
at Selection.PasteSpecial. Sorry if it's a bit messy.

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 - this didn't work
myFileName = Application.GetOpenFilename("Excel Files, *.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

'MsgBox myFileName & " has been selected" 'just to prove that you got it

'to open the file now that you have it:
Set wkbk = Workbooks.Open(Filename:=myFileName)
Else
Exit Sub
End If
ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("JobCost Template V3.3.xls").Activate
Sheets("Ledger").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("Summary").Select
Application.DisplayAlerts = True

End Sub
--
Jim

I amended the code to that below and still received an error on
"Selection.PastSpecial...".

It appears to me that the data copied to the clipboard is being lost on
close or I need to respond "yes" to the retain clipboard data prompt.

ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
'ActveWindow.Close
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
--
Jim


"Kevin B" wrote:

After copying your data from the source workbook, add this line of code to
close it w/o saving:

wkbk .Close SaveChanges:=False

--
Kevin Backmann


"Jim G" wrote:

I have the following code that opens to a predefined dir for the user to
select a file (the Subject), the contents of the only sheet are copied to a
specified sheet ("Ledger") in the Template file. I need to close the Subject
sheet after the contents are copied. Otherwise, I have several very large
sheets that remain open until closed manually. My code is an adaption of
other macros used elsewhere (thanks to the many contibutors here) and fails
at Selection.PasteSpecial. Sorry if it's a bit messy.

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 - this didn't work
myFileName = Application.GetOpenFilename("Excel Files, *.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

'MsgBox myFileName & " has been selected" 'just to prove that you got it

'to open the file now that you have it:
Set wkbk = Workbooks.Open(Filename:=myFileName)
Else
Exit Sub
End If
ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("JobCost Template V3.3.xls").Activate
Sheets("Ledger").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("Summary").Select
Application.DisplayAlerts = True

End Sub
--
Jim