Pressing a button in another workbook

KlayColgrove

New Member
Joined
Oct 19, 2018
Messages
12
I am trying to use a macro that will open another workbook and press the button that is on the sheet "Instructions" then close the workbook after the task has been done. I have a list of name in column B so the action can be done on multiple worksheets. When I run my code is get the error: 400

Code:
Sub AllITMFTM()

Dim B As Range

For Each B In Range("B3", Cells(Rows.Count, "B").End(xlUp))
Workbooks.Open "D:\Documents\RM\Projects\New ITMFTM\" & B.Value & " ITMFTM Tool V1.3.xlsm"
Application.Run "'" & B.Value & "ITMFTM Tool V1.3.xlsm'!Instructions.CopyDataOver"
Workbooks(B.Value & "ITMFTM Tool V1.3.xlsm").Close SaveChanges = False
Next B

End Sub
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
Put the "AllITMFTM" macro inside a module.
Check the name of the file, before " ITMFTM" there is a space.


Try this:


Code:
Sub AllITMFTM()
    Dim B As Range, wPath As String
    
    wPath = "D:\Documents\RM\Projects\New ITMFTM\"
    For Each B In Range("B3", Cells(Rows.Count, "B").End(xlUp))
        Workbooks.Open wPath & B.Value & [COLOR=#ff0000]" ITMFTM[/COLOR] Tool V1.3.xlsm"
        Application.Run "'" & B.Value & [COLOR=#ff0000]" ITMFTM[/COLOR] Tool V1.3.xlsm'![COLOR=#0000ff]CopyDataOver[/COLOR]"
        Workbooks(B.Value & [COLOR=#ff0000]" ITMFTM[/COLOR] Tool V1.3.xlsm").Close SaveChanges = False
    Next B
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,452
Messages
5,414,595
Members
403,536
Latest member
JEduardo

This Week's Hot Topics

Top