Neater syntax

[Paul Sansom]

Hi
I have quite a few repetitions of the scrip below with similar changing values.
IS there a neater (For each) loop that i could simplify this code rather than just repeating it?

Private Sub Worksheet_Activate()


    MyRng = 4 + Sheet12.Cells(3, 5)
    MyRng = "E5:" & "E" & MyRng
    Sheet12.Range(MyRng).Copy
    Sheet5.Range("I4").PasteSpecial xlPasteValues, , , True


    MyRng = 4 + Sheet12.Cells(3, 7)
    MyRng = "G5:" & "G" & MyRng
    Sheet12.Range(MyRng).Copy
    Sheet5.Range("M4").PasteSpecial xlPasteValues, , , True



End Sub

All/Any suggestions gratefully received..... Paul

 

[Fluff]

How about

Private Sub Worksheet_Activate()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(5, "I4", 7, "M4")
   For i = 0 To UBound(Ary) Step 2
      Cells(4, Ary(i)).Resize(Cells(3, Ary(i))).Copy
      Sheet5.Range(Ary(i + 1)).PasteSpecial xlPasteValues, , , True
   Next i
End Sub

 

[Eric W]

Or:

Sub test1()
Dim i As Long, x As Long

    For i = 0 To 1
        x = Sheet12.Cells(3, i * 2 + 5)
        Sheet5.Cells(4, 9 + i * 4).Resize(, x) = WorksheetFunction.Transpose(Sheet12.Cells(5, i * 2 + 5).Resize(x).Value)
    Next i
    
End Sub

But I'm not a huge fan of using sheet names in that fashion, and the calculations for figuring out the columns can be confusing, so you could do something like this:

Sub test1()
Dim Source As Variant, Target As Variant, i As Long, x As Long
    
    Source = Array(Sheets("Sheet12").Range("E5"), Sheets("Sheet12").Range("G5"))
    Target = Array(Sheets("Sheet5").Range("I4"), Sheets("Sheet5").Range("M4"))
    
    For i = 0 To UBound(Source)
        x = Source(i).Offset(-2).Value
        Target(i).Resize(, x).Value = WorksheetFunction.Transpose(Source(i).Resize(x).Value)
    Next i
    
End Sub