Macro to open 9 workbooks and run anpther macro

[tonywatsonhelp]

Hi everyone,
I need a macro to help me automate getting data from one workbook to another .
I have an excellent macro that can copy all the data i want from a workbook to my master workbook once its been opened,
The problem i have isd opening then one at a time and running the macro.

so here what I need.

In Thisworkbook sheet "Run" Range A2:A10 I have a list of the names of the excel workbooks I want to open and copy from
The name is just in the cell like this "Tony Doc" all of them are .xlsm

so all I need is amacro that when run will start at A2 open the workbook, call macro "Copy Data" close workbook go to A3 do the same etc until all 9 have been opened and copied.

Please help if you can

Thanks

Tony

 

[Yongle]

Adapt to your needs
Amend fPath to the path where the 9 workbooks reside

Sub OpenWorkBooksAndRunMacro()
    Const fPath = "[COLOR=#ff0000]c:\Folder\Subfolder[/COLOR]"
    Dim wb As Workbook, fName As Range

    For Each fName In ThisWorkbook.Sheets("Run").Range("A2:A10")
        Set wb = Workbooks.Open(fPath & Chr(92) & fName & ".xlsm")
        On Error Resume Next
        Call Copy_Data
        wb.Close False      'amend to TRUE if the workbook should be saved when closed
    Next fName
End Sub

 

[tonywatsonhelp]

Thanks Yongle, this is great
Tony