Macro launched from another file only partially works

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
 
D

Dave Peterson

How do you start your macro?

Do you use a shortcut key? Does that shortcut key include the shiftkey?

Does the problem go away if you drop the shift from the shortcut key
combination?

If you have a workbook that has a workbook_open event or an auto_open procedure,
then if you open that file with the shift key held down, you stop those
procedures from running.

The shift-key in the shortcut combination confuses excel/vba to stop after you
open a file using one of those shortcut keys.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Kill a Macro? 3
Running macro in one workbook that refers to variable in another 3
conso macro 0
Reactivating file based on text value producees subscript error 1
Run-time error 1004 paste method of worksheet class failed 3
Run macro from active workbook whose name keeps changing 20
Help again!!! 1
Macro Related to Paste Values 2

Top