Why can't I run this macro located in a different workbook?

I'm getting the "vba cannot run the macro the macro, may not be available in this workbook" error when I run this line of code below, even though I know the Pay-TV template.xlsm file is open. Any idea why?

    Application.Run "'Pay-TV template.xlsm'!OpenFileRoutine"
shampouyaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Post the actual code
0
shampouyaAuthor Commented:
In the workbook that's generating the error in line 35:

Public Sub Workbook_Open()
    
'    OpenFileTasks
    Dim originalFileName As String
    originalFileName = ThisWorkbook.Name
    Dim originalFilePath As String
    originalFilePath = ThisWorkbook.Path

    Dim PathToFile As String, NameOfFile As String, wbTarget As Workbook

    NameOfFile = "Pay-TV template.xlsm"
    PathToFile = "I:\resid\pouya"
     
     'Attempt to set the target workbook to a variable.  If an error is
     'generated, then the workbook is not open, so open it
    On Error Resume Next
    Set wbTarget = Workbooks(NameOfFile)
     
    If Err.Number <> 0 Then
        Err.Clear
        Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile) 'Open the workbook
        CloseIt = True
    End If
     
    If Err.Number = 1004 Then  ' make sure it was opened
        MsgBox "Sorry, but the file you specified does not exist!" _
        & vbNewLine & PathToFile & "\" & NameOfFile
        Exit Sub
    End If
    On Error GoTo 0

    Workbooks(originalFileName).Activate
    
    'Application.Run "'Pay-TV template.xlsm'!VC_Sheet_Deactivate"
    Application.Run "'Pay-TV template.xlsm'!OpenFileRoutine"

End Sub

Open in new window



The macro that my file cannot access:

Sub OpenFileRoutine()

    Application.ScreenUpdating = False
    
    UnlockAllSheets
    
    Application.EnableEvents = True
    ActiveWorkbook.Sheets("macro_calcs").Range("B2").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B3").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B4").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B5").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B6").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B7").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B8").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B9").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B10").Value = 0
    ActiveWorkbook.Sheets("macro_calcs").Range("B11").Value = 0
    
    Dim pension1stRow As Integer
    Dim pensionLastRow As Integer
    Dim pensionPrevious1stRow As Integer
    
    ActiveWorkbook.Sheets("Pension Tracking").Select
    'ActiveSheet.Range(Range("F500").End(xlUp).End(xlUp), Range("F500").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)).Interior.Color = xlNone
    
    pension1stRow = ActiveWorkbook.Sheets("Pension Tracking").Range("F500").End(xlUp).End(xlUp).Offset(1, 0).Row
    pensionLastRow = ActiveWorkbook.Sheets("Pension Tracking").Range("F500").End(xlUp).Offset(-1, 0).Row
    pensionPrevious1stRow = ActiveWorkbook.Sheets("Pension Tracking").Range("B11").Value
    
    'ActiveSheet.Range(Range("F500").End(xlUp).End(xlUp).Offset(0, 1), Range("F500").End(xlUp).End(xlUp).End(xlUp).Offset(1, 1)).ClearContents
    
    ActiveWorkbook.Sheets("Pension Tracking").Range("B12").Value = pensionLastRow
    
    If ActiveWorkbook.Sheets("Pension Tracking").Range("B13").Value < ActiveSheet.Range("B10").Value Then
        ActiveWorkbook.Sheets("Pension Tracking").Range("B11").Value = pensionPrevious1stRow
        ActiveWorkbook.Sheets("Pension Tracking").Range("B13").Formula = "=sum(F" & pensionPrevious1stRow & ":F" & pensionLastRow & ")"
    Else
        ActiveWorkbook.Sheets("Pension Tracking").Range("B11").Value = pension1stRow
        ActiveWorkbook.Sheets("Pension Tracking").Range("B13").Formula = "=sum(F" & pension1stRow & ":F" & pensionLastRow & ")"
    End If
    
    ' use the formula in cell G3 in case any row deletion in the pension tracking table screws up the column G formulas
    ActiveWorkbook.Sheets("Pension Tracking").Range("G3").Copy
    ActiveWorkbook.Sheets("Pension Tracking").Range("G3").AutoFill Destination:=Range("G3:" & "G" & pensionLastRow - 1)
    
    Sheets(1).Activate
    Application.ScreenUpdating = True

    LockAllSheets

End Sub

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
The Open Event is a Private Sub not Public. Move the code to a Standard Module and call that
0
shampouyaAuthor Commented:
The problem was that my module name was the same as the name of the routine. Apparently they must be named differently in order to call a routine located in another file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shampouyaAuthor Commented:
No one else pointed out this conflict that I discovered was causing the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.