Although I typed this in the VBE it's totally untested, just for ideas -

Sub test()
Dim rng As Range, cel As Range, rDest As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim vaFiles
Dim wbCnt As Long

vaFiles = Array("1st Destination.xls", "Other Name.xls", _
"Another DifferentName.xls") ' 25 files


Set rng = Workbooks("Source File.xls") _
.Worksheets("SourceSheet").Range("B4:Z4") ' 25 cells
wbCnt = LBound(vaFiles)

If UBound(vaFiles) - wbCnt + 1 < rng.Count Then
MsgBox "cells count < workbooks count)"
Exit Sub
End If

For Each cel In rng
Set wb = Workbooks(vaFiles(wbCnt))
Set rDest = wb.Workbooks(vaFiles(wbCnt)).Worksheets(1).Range(" C19")
'' if want to paste on what would be its activesheet, not sheets(1) or
'' a known sheet-name would need to do something like this

' wb.activate
' set rDest = activesheet.range("C19")

Range(cel, cel.Range("E4").End(xlDown)).Copy
rDest.PasteSpecial xlPasteValues
wb.Close
wbCnt = wbCnt + 1
wb.Save
Next

End Sub

Regards,
Peter T


"markx" wrote in message
...
Hi guys,

I have a code to copy a (varying) range from one file to 25 other files.
For
the time being I use this (all the necessary files are already opened):

-----------------------
Sub Repetitive_macro()

'1st loop
Windows("Source File.xls").Activate = this is always the same
Sheets("SourceSheet").Select = this is always the same
Range("B4").Select = this part is varying, starting with
B4, then at next loop C4, then D4,... until Z4
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Windows("1st Destination.xls").Activate = this part is also
varying, taking 25 different open files one after another and pasting what
was copied before... (file names follow no special pattern)
Range("C19").Select = this is always the same
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close

'2nd loop
Windows("Source File.xls").Activate
Sheets("SourceSheet").Select
Range("C4").Select

... etc, following 25 times the scheme explained above

End Sub()
------------------------

So, my question is: Can you specify all the varying parts somewhere at the
beginning of the code (in array or whatsoever) and then put it as a
variable
in the code, changing at every loop?

I would imagine something like this (pseudo-code)
--------------
Sub More_efficient_macro()

x = (B4,D4,E4,F4,G4,H4,...,Z4)
y = ("1st Destination.xls","Other Name.xls","Another Different
Name.xls",...,"Name of the last file.xls")

For every x and y

Loop
Windows("Source File.xls").Activate
Sheets("SourceSheet").Select
Range("x").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Windows("y").Activate
Range("C19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Loop

End Sub()
---------------

Thanks for any help on this,
Cheers,

Mark