|
Smaller VBA? Loop or Offset?
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. |
Smaller VBA? Loop or Offset?
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 --------------- Jacob Skaria "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Qaspec" wrote in message ... 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. |
Smaller VBA? Loop or Offset?
Thanks for the catch.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Qaspec" wrote in message ... 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. |
| All times are GMT +1. The time now is 11:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com