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

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

Const sFile as string = _
"D:\Downloads\Tomahawk\2003\Tomahawk Invoice (Marty"
Dim oWb as workbook

For i 1 To 52
Workbooks.Open(Filename:= sFile & i & ").xls").RunAutoMacros _
Which:=xlAutoOpen
Set oWb = Activeworkbook
Range("E18:J27").Copy
Windows("Summary.xls").Activate
ActiveSheet.Paste
Range("A21").Select
oWb.Activate
oWb.RunAutoMacros Which:=xlAutoClose
oWb.Close
Next i



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Penflex" wrote in message
...
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