Running macro from another workbook

[lox1985]

Hey all,

I think i'm missing something super simple here but yet can't spot it. I have a simple macro that opens another file and runs a macro within that file. It worked the very first time and then stopped running. It gives a Run-Time 1004 error on Application.Run line "Cannot run the macro "5 arm 2725 - Fairwinds.xlsm'!Import_Day_Price'. The macro may not be available in thisworkbook or all macros may be disabled." I checked my Macro Security settings to enable macros. All of the files where the macro is stored are saved with File Extension of .xlsm. Code is attached below. This happens on every workbook in Range("A2:A" & LastRow). And it also happens if I enter the actual workbook name in the code along with the macro name instead of using the variable. When I go into each workbook manually and run the macro, it runs fine.

Sub run_mid_day_update()

Dim wb As Workbooks
Dim LastRow As Long
Dim cell As Range


Application.ScreenUpdating = False
Application.DisplayAlerts = False


LastRow = Range("A" & Rows.Count).End(xlUp).Row


For Each cell In Range("A2:A" & LastRow)
Workbooks.Open Filename:=cell.Value & cell.Offset(0, 1).Value
Application.Run ("'" & cell.Offset(0, 1).Value & "'" & "!" & "Import_Day_Price")


ActiveWorkbook.Save
ActiveWorkbook.Close
Workbooks("Control File.xlsm").Activate


Next cell


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Let me know if you see anything.

Thanks,

Max

 

[lox1985]

I should add that I searched on the forums and my syntax seems to be accurate, so I am out of ideas what would cause such an error to generate. Any help is appreciated.

 

[lox1985]

bump. I'm on office 2010 by the way.

 

[lox1985]

SOLVED: My issue was the spaces in workbook names where i was referencing. I replaced spaces with underscores and it runs like a charm.