simplifying repetitive routines in VBA
 

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

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

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

simplifying repetitive routines in VBA
 

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

simplifying repetitive routines in VBA
 

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