VBA across multiple worksheets

Question

I perform this code 11 times on my macro

Windows("LOFORM.xls").Activate
Sheets("Becke").Select
Range("A6:J25").Select
Selection.Copy
Windows("Comp Reform LO.xls").Activate
Sheets("Becke").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

for all different sheets. Instead of having this entire code 11 separate time with different sheet names can I condense it?

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Nov 25 '13 at 23:50

score 4 · Answer 1 · answered Nov 25 '13 at 23:45

This is much mroe compact, but also as it avoids all selects, activates and working with the clip board it will also be much faster.

Windows("Comp Reform LO.xls").Sheets("Becke").Range("A6:J25") = _
Windows("LOFORM.xls").Sheets("Becke").Range("A6:J25")

If you have this code 11 times through out your code you should turn it into its own sub, and simply call it as CopyValues() or something.

Or if the code 11 times is similar but different locations you could also pass in the Workbook and worksheet params:

    Sub SampleCopyValues(DestinationWorkSheet As Worksheet)
        DestinationWorkSheet.Range("A6:J25") = _
        Windows("LOFORM.xls").Sheets("Becke").Range("A6:J25")
    End Sub

Then you could call it as SampleCopyValues(Windows("Comp Reform LO.xls").Sheets("Becke"))

VBA across multiple worksheets

1 Answers1