View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 1,726
Default simplifying repetitive routines in VBA

Break it down

Sub Repetitive_macro()
Call ProcessFile(Range("B4"), "1st Destination.xls")
Call ProcessFile(Range("C4"), "2nd Destination.xls")
'etc.
End Sub

Sub ProcessFile(cell As Range, wb As String)
Windows("Source File.xls").Activate
Sheets("SourceSheet").Select
cell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

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


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"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