G
Guest
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
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