gcservi
07-24-2012, 07:09 PM
Hi, all. I'm new to VBA but I've got one Excel file opening another and passing data, but can't get it to run a macro in the target file. The macro runs find when triggered with a button in the target file. What am I missing?
Source Excel macro
Sub Send_Info()
Dim dir As String
dir = ActiveWorkbook.Path
On Error Resume Next
Workbooks.Open Filename:=dir & "\macro test file 2.xlsm"
Workbooks("macro test file 1.xlsm").Activate
file = "macro test file 2.xlsm"
SheetData = "Sheet1"
Workbooks(file).Worksheets(SheetData).Range("A1") = Cells(ActiveCell.Row, 1)
Workbooks(file).Worksheets(SheetData).Range("B1") = Cells(ActiveCell.Row, 2)
Workbooks(file).Worksheets(SheetData).Range("C1") = Cells(ActiveCell.Row, 3)
Workbooks(file).Activate
Workbooks(file).Run "macro test file 2.xlsm!SaveToDesktop"
End Sub
Target Excel macro
Sub SaveToDesktop()
ThisFile = Range("B6").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
Source Excel macro
Sub Send_Info()
Dim dir As String
dir = ActiveWorkbook.Path
On Error Resume Next
Workbooks.Open Filename:=dir & "\macro test file 2.xlsm"
Workbooks("macro test file 1.xlsm").Activate
file = "macro test file 2.xlsm"
SheetData = "Sheet1"
Workbooks(file).Worksheets(SheetData).Range("A1") = Cells(ActiveCell.Row, 1)
Workbooks(file).Worksheets(SheetData).Range("B1") = Cells(ActiveCell.Row, 2)
Workbooks(file).Worksheets(SheetData).Range("C1") = Cells(ActiveCell.Row, 3)
Workbooks(file).Activate
Workbooks(file).Run "macro test file 2.xlsm!SaveToDesktop"
End Sub
Target Excel macro
Sub SaveToDesktop()
ThisFile = Range("B6").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub