Smaller VBA? Loop or Offset?

[Qaspec]

I'm using the following code to import values from one worksheet to another...

Private Sub CB1_Click()

Dim From As Worksheet
Dim Result As Worksheet

Set From = Worksheets("October")
Set Result = Worksheets("OctVariance")

'From is next 4 columns for each Result plus 1 row

Result.Range("C5:F5").Value = From.Range("C3:F3").Value
Result.Range("C6:F6").Value = From.Range("G3:J3").Value
Result.Range("C7:F7").Value = From.Range("K3:N3").Value
Result.Range("C8:F8").Value = From.Range("O3:R3").Value
Result.Range("C9:F9").Value = From.Range("S3:V3").Value
Result.Range("C10:F10").Value = From.Range("W3:Z3").Value
Result.Range("C11:F11").Value = From.Range("AA3:AD3").Value
Result.Range("C12:F12").Value = From.Range("AE3:AH3").Value
Result.Range("C13:F13").Value = From.Range("AI3:AL3").Value
Result.Range("C14:F14").Value = From.Range("AM3:AP3").Value
Result.Range("C15:F15").Value = From.Range("AQ3:AT3").Value
Result.Range("C16:F16").Value = From.Range("AU3:AX3").Value
Result.Range("C17:F17").Value = From.Range("AY3:BB3").Value
Result.Range("C18:F18").Value = From.Range("BC3:BF3").Value
Result.Range("C19:F19").Value = From.Range("BG3:BJ3").Value
Result.Range("C20:F20").Value = From.Range("BK3:BN3").Value
Result.Range("C21:F21").Value = From.Range("BO3:BR3").Value



End Sub

Can this be done with a leaner/smaller sub? Thanks for your help.

 

[Don Guillett]

withOUT testing something like

sub copyem()

Dim From As Worksheet
Dim Result As Worksheet

Set From = Worksheets("October")
Set Result = Worksheets("OctVariance")

for i =5 to 21
J=3
Result.cells(i,"c").resize(,4).Value = From.cells(3,J).resize(,4).Value
J=J+4
next i
end sub

 

[Jacob Skaria]

The value assigned to J = 3 should be outside the loop as below.....

sub copyem()

Dim From As Worksheet
Dim Result As Worksheet

Set From = Worksheets("October")
Set Result = Worksheets("OctVariance")

J=3
for i =5 to 21
Result.cells(i,"c").resize(,4).Value = From.cells(3,J).resize(,4).Value
J=J+4
next i
end sub


If this post helps click Yes

 

[Don Guillett]

Thanks for the catch.