Calling a Macro from another workbook

[Ricky Morris]

What's the syntax to call a macro in another workbook?

Thanks,
Ricardo

 

[ZVI]

I used to have the code Range("B14:H & Lastrow").Select but it seemed to not work sometimes due to copying data from a table.

It should be Range("B14:H" & Lastrow).Select

 

[Johnny Thunder]

It should be Range("B14:H" & Lastrow).Select

Sorry, that's what I meant.

I don't have the coding in there anymore though.

Note: Although I am getting that error when pasting, the data is actually being pasted but then the Code stops and I get the error pop up. Not sure if that makes a difference?

 

[ZVI]

Not sure if it'll work without understanding of the data layout and the functionality of the called macro,
but try this version of your code in which multiple Selects were replaced by the explicit references:

Sub ODM_Transfer()
 
  Dim Destbook As Workbook
  Dim varCellvalue As Variant
  Dim sNameFound As String
 
  Const sPath As String = "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\"
 
  varCellvalue = ThisWorkbook.Sheets("Main").Range("B24").Value
 
  On Error Resume Next
  Set Destbook = Workbooks(varCellvalue & ".xls")
  If Err.Number <> 0 Then _
     Set Destbook = Workbooks(varCellvalue & ".xlsm")
  On Error GoTo 0
 
  If Destbook Is Nothing Then
    sNameFound = Dir(sPath & varCellvalue & ".xls*")
    If sNameFound = vbNullString Then
      MsgBox "No files found matching: " & sPath & varCellvalue & ".xls*"
      Exit Sub
    Else
      Set Destbook = Workbooks.Open(sPath & sNameFound)
    End If
  End If
 
  Destbook.Activate
  Sheets("ODM").Select
 
  On Error Resume Next
  Application.Run "'" & Destbook.Name & "'!Macro1"
  If Err.Number = 0 Then
    Destbook.Sheets("ODM").Range("H11").Hyperlinks(1).Follow NewWindow:=False
  End If
  On Error GoTo 0
 
  With ThisWorkbook.Sheets("ODM")
    .Range("B14:H14", .Cells(.Rows.Count, "B").End(xlUp)).Copy
  End With
 
  Destbook.Sheets("ODM").Range("C14").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                   SkipBlanks:=False, Transpose:=False
 
  Application.CutCopyMode = False
 
  With ThisWorkbook.Sheets("ODM")
    .Range("N14:O14", .Cells(.Rows.Count, "N").End(xlUp)).Copy
  End With
 
  Destbook.Sheets("ODM").Range("J14").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                   SkipBlanks:=False, Transpose:=False
 
  Application.CutCopyMode = False
 
End Sub

]

 

[Johnny Thunder]

This is so weird,

The code is now error-ing out here

Destbook.Sheets("ODM").Range("C14").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                   SkipBlanks:=False, Transpose:=False

 

[ZVI]

Just guessing, the merged cells in destination will cause the such error - check it.
If you want I will PM my e-mail to receive from you the workbooks for debugging.

 

[Jorge Flores]

""""

If the name of the workbook is dynamically assigned as a variable you can use this code

Application.Run ("'" & workbookname & "'!macroname")

"""

Workbookname = Book.xlsm (need to be XLSM)
macroname = Macro (add macro name, the one after "SUB" no module name)

Correct form in case workbook and macro name are variables = Application.Run "" & workbookname & "" & "!" & "" & macroname & ""

 

[cvzorrilla]

Found the solution

if you try to run a macro from a workbook that contains spaces you must enclose them with apostrophe, such as

Application.Run ("'Analytics macro S17.xlsm'!getdata")

worked for me! thanks!!

 

[NirajPatel]

I have tried this over and over again and everytime it tells me

"Can not run the macro 'Weekly Shrinkage Tool.xlsm!NewWeek'. The macro may not be available is this workbook or all macros may be disabled."

I have tried all sorts of versions of the line

Application.Run "MacroBook!MacroName"

including using a variable for the file name which I can't get to work at all

but here are some ways I have typed it

Application.Run "Weekly Shrinkage Tool.xlsm!NewWeek"
Application.Run "Weekly Shrinkage Tool.xlsm!NewWeek()"
Application.Run "Weekly Shrinkage Tool.xlsm!Public Sub NewWeek()"
Application.Run "Weekly Shrinkage Tool!NewWeek"

Am I missing something like a simple sintax error?

:// Try renaming your workbook from "Weekly Shrinkage Tool.xlsm" to WeeklyShrinkageTool.xlsm and try the same (eg. Application.Run "WeeklyShrinkageTool.xlsm!NewWeek" )

 

[krunk]

I am still getting an error message with this code below saying that they "cannot run the macro "Top 95 data Update.xlsb'ConditionalFormatting'. the Macro may not be available in this workbook or all macros may be disabled.

All of my code is in the same workbook and worksheet. Should the actualy worksheet be in the message instead of the workbook? Here is my exciting code. The highlighted part is what i am trying to address.

Sub ConditionalFormatting()


End Sub




Set a = Sheets("Pivot")


i = 6 'row
j = 6 'column
Do Until a.Cells(4, j) = "Grand Total" 'tells code to continue the code until it finds Grand Total
j = j + 1
Loop
j = j - 1
vArr = Split(Cells(1, j).Address(True, False), "$")
a.Cells(1, 15) = vArr
Do Until a.Cells(i, 5) = ""
a.Range("F" & i & ":" & a.Cells(1, 15).Value & i).Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
i = i + 1
Loop
a.Cells(1, 15) = ""






Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Application.Run ("'Top 95 Data Update.xlsb'!ConditionalFormatting")




End Sub

If the name of the workbook is dynamically assigned as a variable you can use this code

Application.Run ("'" & workbookname & "'!macroname")

 

[mark hansen]

Sorry to bring up an old thread... but I'm trying to do this too, but the macro is contained on a worksheet, within a CommandButton_Click event. (sort of automatically clicking the button when I get to that part of the code.

I have one workbook, loading another and accessing a specific sheet. I would like to "automatically" click that button by running the code in the button_click event. Can I do that, or do the macros need to be in a module?