On Friday, February 14, 2014 5:50:18 AM UTC-8, GS wrote:
You might want to think about adding code in case your files don't

exist in the hard path on your sheet. Optionally, you might want to use

a FileDialog to multi-select the files if they're in the same folder.



I'm not in favor of Claus' suggestion to use Workbook_Open in case you

need to work on the file and test macros. Otherwise, you'd have to open

with macros disabled.



Suggestion...



Sub OpenAndRun()

Dim vFilesToOpen, n&, sFilename, wkbTmp As Workbook

vFilesToOpen = ActiveSheet.Range("FilesToOpen") '//named dynamic

range



If IsArray(vFilesToOpen) Then

For n = LBound(vFilesToOpen) To UBound(vFilesToOpen)

If Dir(vFilesToOpen(n)) < "" Then

Set wkbTmp = Workbooks.Open(vFilesToOpen(n))

Call RunMacro_CloseFile(wkbTmp)

End If

End If

Next 'n

Else '//single file or none listed

If Dir(vFilesToOpen) < "" Then

Set wkbTmp = Workbooks.Open(vFilesToOpen)

Call RunMacro_CloseFile(wkbTmp)

End If

End If

End Sub



Sub RunMacro_CloseFile(Wkb As Workbook)

With Wkb

Application.Run ("'" & .Name & "'!MyMacro")

.Close SaveChanges:=True

End With

End Sub



..where the actual running of the macro is done in a separate sub so

the file will close before opening the next file. (Otherwise, the file

doesn't close until the sub ends)



--

Garry



Hi Garry,

I created a dynamic named range TO Wit:

Name: FilesToOpen

Refers To: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

I get an error on this line:

If Dir(vFilesToOpen(n)) < "" Then

If I hover the cursor around a bit I fet the little alert boxes "subscript out of range"

Howard