Thanks to you all guys,

For the time being, i've only tested the solution proposed by Bob, it works
perfectly (thanks!).
For two other codes, I'll try to test it too and give a feedback in the
forthcoming days...

Cheers,
Mark


"Martin Fishlock" <martin_fishlock@yahoodotcodotuk wrote in message
...
Try this and you need a worksheet in the workbook where the macro is
called
datafilenames and you put the workbook names in the cells b2:b26 such as
book2.xls.

Const startrow As Long = 4
Sub Repetitive_macro()
Dim ws_src As Worksheet
Dim col As Long
Dim lastrow As Long
Dim copytoworkbook As String
Set ws_src = Workbooks("Source File.xls").Sheets("SourceSheet")

For col = 2 To 26
lastrow = ws_src.Cells(startrow, col).CurrentRegion.Rows.Count +
startrow - 1
ws_src.Range(ws_src.Cells(startrow, col), ws_src.Cells(lastrow,
col)).Copy
copytoworkbook = ThisWorkbook.Worksheets("datafilenames").Range( _
"B" & col)
Workbooks(copytoworkbook).Worksheets(1).Range("C19 ").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Workbooks(copytoworkbook).Close saveChanges:=True

Next col
Set ws_src = Nothing
End Sub

--
HTHs Martin Fishlock


"markx" wrote:

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