Call a Macro from another workbook

[wizard94]

Hi there - just wondered whether someone could help me as my brain is currently fried ..

I have a workbook named KPI_DASH. This collates data collected from various other workbooks i.e BookA. The KPI_DASH workbook is hidden to the user and they wouldn't open it.

Within KPI_DASH is this macro:

Sub AromaHours()


Worksheets("HoursAroma").Activate


Dim cell As Range
For Each cell In Range("B2:AA9")
If cell.Value > 0 Then
cell.Copy
cell.PasteSpecial xlPasteValues
End If
Next
End Sub

From the BookA workbook, on a button press, I need it to open the workbook KPI_DASH in the background and run the above macro. This is what I have so far:

Sub SubmitHours()


Application.DisplayAlerts = False
Application.ScreenUpdating = False
Workbooks.Open Filename:="\root\KPI_DASH.xlsm"
Application.Run "KPI_DASH.xlsm!AromaHours"


End Sub

However I am getting a Run-time error '1004'. Can someone help please?

Thank you!

 

[mumps]

Try:

Application.Run ("'KPI_DASH.xlsm'!AromaHours")

 

[wizard94]

Try:

Application.Run ("'KPI_DASH.xlsm'!AromaHours")

Thanks Mumps - unfortunately I am still getting the run-time error

 

[wizard94]

Thanks Mumps - unfortunately I am still getting the run-time error

Managed to get this working. Put the code in the module and amended the code to:

Application.Run "root\KPI_DASH.xlsm!AromaHours"

 

[mumps]

Glad it worked out.