Hi,


Try this:

Dim FileNo as integer

For FileNo = 23 to 74

Workbooks.Open(Filename:= _
"D:\Downloads\Tomahawk\2003\Tomahawk Invoice
(Marty" & FileNo & ").xls").RunAutoMacros _
Which:=xlAutoOpen
Range("E18:J27").Select
Selection.Copy
Windows("Summary.xls").Activate
ActiveSheet.Paste
Range("A21").Select
Windows("Tomahawk Invoice (Marty" & FileNo & ").xls").Activate
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
ActiveWorkbook.Close

Next Fileno


HTH

"Penflex" wrote:

I have 52 files that I want to open and copy 10 cells from each file and
paste them into a new spreadsheet. I plan to use the code below but I want
to add a For Next loop. The only part of the filename that changes is the
number after Marty. Example Marty23, Marty24, Marty25..... What syntax do I
use to make the number after Marty a variable?


Workbooks.Open(Filename:= _
"D:\Downloads\Tomahawk\2003\Tomahawk Invoice
(Marty23).xls").RunAutoMacros _
Which:=xlAutoOpen
Range("E18:J27").Select
Selection.Copy
Windows("Summary.xls").Activate
ActiveSheet.Paste
Range("A21").Select
Windows("Tomahawk Invoice (Marty23).xls").Activate
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
ActiveWorkbook.Close