VBA code to run macro in certain file based on name of file in cel

[Carl]

I am trying to come up with code that will run a macro in a workbook where
the path and name of the workbook are in a cell reference. I have tried the
following with no luck. The path to the file is located in cell F36 of
worksheet, "Customize" and the name of the file is located in cell F42. The
name of the macro is "ClearTotalSavings". Any help from one of you gurus
would sure be appreciated.

Application.Run "'" & Workbooks(Sheets("Customize").Range("F36").Value) &
"\" & Sheets("Customize").Range("F42").Value & "'!" & "ClearTotalSavings"

I use the following code to open a particular workbook using entries in the
same cells and it works just fine, so I know I am close. Thanks in advance.

Workbooks.Open Filename:="" & Sheets("Customize").Range("F36").Value & "\" &
Sheets("Customize").Range("F42").Value & "", _

 

[OssieMac]

Hi Carl,

Try the following.

Application.Run "'" & Sheets("Customize").Range("F36").Value & _
"\" & Sheets("Customize").Range("F42").Value & "'!" & "ClearTotalSavings"

 

[OssieMac]

Hi again Carl,

The code I posted before will work if the workbook containing the worksheet
Customize is the active workbook. Therefore I suggest that you might be
better to do it the following way to ensure that the code is addressing the
correct workbook with the sheet Customize.

Create the strPathFile first and ensure it is addressing the correct
workbook before using it in the run command.

Sub test()
Dim wb As Workbook
Dim strPathFile

'If not the workbook with the sub then
'set to whatever workbook has sheet Customize.
Set wb = ThisWorkbook

With wb.Sheets("Customize")

strPathFile = .Range("F36").Value _
& "\" & .Range("F42").Value

End With

Application.Run "'" & strPathFile & _
"'" & "!ClearTotalSavings"

End Sub