simplifying repetitive routines in VBA
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
|