A
Andyjim
Hi-
I am stumped
I have a file (File 1) with a marcro that activates File2 and turns control
over to File 2 macro. File2 macro does copying and pasting from File1 to
File2, then causes File1 to close and File2 to saveas File1. Problem occurs
when the File2 macros causes File1 to close. File2 macro stops and never
gets saved as File1 name.
The uncanny thing is that when I just run the File2 macro from File2, it
works fine. But if it gets called from FIle1 is stops as soon as File1 is
closed.
Note: File1 --we don't know the name of....user launches from File1 and code
defines the name and path. We do know the name of File2 (fxRM_Update.xls)
Code for FIle 1:
Sub RunUpdate()
'Update button in user file Account Summary Sheet runs this macro
Unprotect
Dim updfile As String
Dim usrfile As Workbook
Dim backname As String
Dim p As String
p = ActiveWorkbook.Path
updfile = p & "\" & "fxRM_Update.xls"
Set usrfile = ActiveWorkbook
backname = p & "\" & "Backup" & usrfile.Name
currentuserfile = p & "\" & usrfile.Name
'Save backup copy of user's file, in same folder
If InStr(1, p, "\xlstart", vbTextCompare) = 0 Then 'If XLSTART is not in
the path
usrfile.SaveCopyAs filename:=backname 'do the save
Else 'Else if XLSTART is in the
path
'Do nothing, i.e. don't save
End If
'Insert user filename in "filename" cell
Sheets("Lookup").Select
Range("filename") = ActiveWorkbook.Name
'Insert user file path in "path1" cell
Range("path1") = ActiveWorkbook.Path
'Find & open fxRM_Update.xls
If Dir(updfile) = "" Then 'If fxRM_Update not there, error message
MsgBox ("fxRM_Update.xls not found. Move fxRM_Update.xls to this
folder.")
Exit Sub
End If 'On Error Resume Next
If IsFileOpen(updfile) Then 'If fxRM_Update.xls is open
'Do nothing, No error
Else 'If not open,
Workbooks.Open filename:=updfile 'Open it
End If
'Activate & Unprotect fxRM_Update.xls
Windows("fxRM_Update.xls").Activate
Unprotect
'Copy user file name to fxRM_Update.xls
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="Filename"
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="UserFilename"
ActiveSheet.Paste
'Copy user file path to fxRM_Update.xls
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="path1"
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="path2"
ActiveSheet.Paste
'Copy user file version to Update file, Old Version cell
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="CurrentVersion"
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="OldVersion"
ActiveSheet.Paste
Range("K7").Select
'Run Update macro
Application.Run ("fxRM_Update.xls!Update")
End Sub
Code from File2:
Sub Update() '1/2008 - this adds data from the user file to
fxRM_Update_v1.32.2.xls
'Requires Update filename: "fxRM_Update.xls"
'Runs from Update file
'Copies data from bk1 (User file) to "fxRM_Update.xls" (Update file)
'"fxRM_Update.xls" becomes new user file, saved as user's filename
'Update2 defines user file in fxRM_Update.xls & activates it
'Copy AccountSummary data from User file to Update file
Update2 'every time you run Update2, you are reactivating Userfile
Sheets("AccountSummary").Select
Range("C6:C8").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("AccountSummary").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("AccountSummary").Select
Range("K7:M506").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("AccountSummary").Select
Range("K7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Update2
Sheets("AccountSummary").Select
Application.CutCopyMode = False
Range("A1").Select
'Copy TradeHistory data from User file to Update file
Sheets("TradeHistory").Select
Range("A6:Av15000").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("TradeHistory").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Update2
Sheets("Analysis").Select
Range("A10").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
'Copy AnalysisSheet data from User file to Update file
Update2
Sheets("Analysis").Select
Range("A17:F56").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("K17:M56").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("K17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("O17:S56").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("O17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("A16:AV16").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
ActiveWorkbook.Close (savechanges = True) 'MACRO Stops here when you
start with RUNUPDATE from user file.
'runs fine when you run UPDATE from
fxRM_Update file
Windows("fxRM_Update.xls").Activate
'Protect
'Save Update file as User file name
' Dim bk As Workbook
Dim saveString As String
Dim sstr2 As String
Dim path2 As String
path2 = ActiveWorkbook.Path
sstr2 = Worksheets("lookup").Range("d42").Value
saveString = path2 & "\" & sstr2
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=saveString
Protect
'ActiveWorkbook.savechanges = False
ActiveWorkbook.Close
End Sub
Sub Update2()
'Defines user file in fxRM_Update.xls
'Update macro calls this macro each time it must return to user file
Dim bk As Workbook, bk1 As Workbook
Dim sstr As String
Set bk = Workbooks("fxRM_Update.xls")
sstr = bk.Worksheets("lookup").Range("userfilename").Value
Set bk1 = Workbooks(sstr)
bk1.Activate
End Sub
I am stumped
I have a file (File 1) with a marcro that activates File2 and turns control
over to File 2 macro. File2 macro does copying and pasting from File1 to
File2, then causes File1 to close and File2 to saveas File1. Problem occurs
when the File2 macros causes File1 to close. File2 macro stops and never
gets saved as File1 name.
The uncanny thing is that when I just run the File2 macro from File2, it
works fine. But if it gets called from FIle1 is stops as soon as File1 is
closed.
Note: File1 --we don't know the name of....user launches from File1 and code
defines the name and path. We do know the name of File2 (fxRM_Update.xls)
Code for FIle 1:
Sub RunUpdate()
'Update button in user file Account Summary Sheet runs this macro
Unprotect
Dim updfile As String
Dim usrfile As Workbook
Dim backname As String
Dim p As String
p = ActiveWorkbook.Path
updfile = p & "\" & "fxRM_Update.xls"
Set usrfile = ActiveWorkbook
backname = p & "\" & "Backup" & usrfile.Name
currentuserfile = p & "\" & usrfile.Name
'Save backup copy of user's file, in same folder
If InStr(1, p, "\xlstart", vbTextCompare) = 0 Then 'If XLSTART is not in
the path
usrfile.SaveCopyAs filename:=backname 'do the save
Else 'Else if XLSTART is in the
path
'Do nothing, i.e. don't save
End If
'Insert user filename in "filename" cell
Sheets("Lookup").Select
Range("filename") = ActiveWorkbook.Name
'Insert user file path in "path1" cell
Range("path1") = ActiveWorkbook.Path
'Find & open fxRM_Update.xls
If Dir(updfile) = "" Then 'If fxRM_Update not there, error message
MsgBox ("fxRM_Update.xls not found. Move fxRM_Update.xls to this
folder.")
Exit Sub
End If 'On Error Resume Next
If IsFileOpen(updfile) Then 'If fxRM_Update.xls is open
'Do nothing, No error
Else 'If not open,
Workbooks.Open filename:=updfile 'Open it
End If
'Activate & Unprotect fxRM_Update.xls
Windows("fxRM_Update.xls").Activate
Unprotect
'Copy user file name to fxRM_Update.xls
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="Filename"
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="UserFilename"
ActiveSheet.Paste
'Copy user file path to fxRM_Update.xls
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="path1"
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="path2"
ActiveSheet.Paste
'Copy user file version to Update file, Old Version cell
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="CurrentVersion"
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="OldVersion"
ActiveSheet.Paste
Range("K7").Select
'Run Update macro
Application.Run ("fxRM_Update.xls!Update")
End Sub
Code from File2:
Sub Update() '1/2008 - this adds data from the user file to
fxRM_Update_v1.32.2.xls
'Requires Update filename: "fxRM_Update.xls"
'Runs from Update file
'Copies data from bk1 (User file) to "fxRM_Update.xls" (Update file)
'"fxRM_Update.xls" becomes new user file, saved as user's filename
'Update2 defines user file in fxRM_Update.xls & activates it
'Copy AccountSummary data from User file to Update file
Update2 'every time you run Update2, you are reactivating Userfile
Sheets("AccountSummary").Select
Range("C6:C8").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("AccountSummary").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("AccountSummary").Select
Range("K7:M506").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("AccountSummary").Select
Range("K7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Update2
Sheets("AccountSummary").Select
Application.CutCopyMode = False
Range("A1").Select
'Copy TradeHistory data from User file to Update file
Sheets("TradeHistory").Select
Range("A6:Av15000").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("TradeHistory").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Update2
Sheets("Analysis").Select
Range("A10").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
'Copy AnalysisSheet data from User file to Update file
Update2
Sheets("Analysis").Select
Range("A17:F56").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("K17:M56").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("K17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("O17:S56").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("O17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("A16:AV16").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
ActiveWorkbook.Close (savechanges = True) 'MACRO Stops here when you
start with RUNUPDATE from user file.
'runs fine when you run UPDATE from
fxRM_Update file
Windows("fxRM_Update.xls").Activate
'Protect
'Save Update file as User file name
' Dim bk As Workbook
Dim saveString As String
Dim sstr2 As String
Dim path2 As String
path2 = ActiveWorkbook.Path
sstr2 = Worksheets("lookup").Range("d42").Value
saveString = path2 & "\" & sstr2
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=saveString
Protect
'ActiveWorkbook.savechanges = False
ActiveWorkbook.Close
End Sub
Sub Update2()
'Defines user file in fxRM_Update.xls
'Update macro calls this macro each time it must return to user file
Dim bk As Workbook, bk1 As Workbook
Dim sstr As String
Set bk = Workbooks("fxRM_Update.xls")
sstr = bk.Worksheets("lookup").Range("userfilename").Value
Set bk1 = Workbooks(sstr)
bk1.Activate
End Sub