Clean up code using WITHs
 

Is there a more efficient way to write this code? For example could I use another with (ActiveSheet)?

With Sheet2
.Cells(varRow, 1).Value = ActiveSheet.Range("G3").Value
.Cells(varRow, 2).Value = ActiveSheet.Range("G4").Value
.Cells(varRow, 3).Value = ActiveSheet.Range("Total").Value
.Cells(varRow, 4).Value = ActiveSheet.Range("B11").Value
.Cells(varRow, 5).Value = ActiveSheet.Range("B12").Value
.Cells(varRow, 6).Value = ActiveSheet.Range("B13").Value
.Cells(varRow, 7).Value = ActiveSheet.Range("B14").Value
.Cells(varRow, 8).Value = ActiveSheet.Range("B15").Value
.Cells(varRow, 9).Value = ActiveSheet.Range("F11").Value
.Cells(varRow, 10).Value = ActiveSheet.Range("F12").Value
.Cells(varRow, 11).Value = ActiveSheet.Range("F13").Value
.Cells(varRow, 12).Value = ActiveSheet.Range("F14").Value
.Cells(varRow, 13).Value = ActiveSheet.Range("F15").Value
end with

Thank you all,

Rick

Clean up code using WITHs
 

Could try calling it

callme 1, "G3"
callme 2, "G4"
callme 3, "Total"
callme 4, "B11"
etc, etc...

private sub callme(x,y)
sheet2.cells(varrow, x).value = activesheet.range(y).value
end sub

----- Richard wrote: -----

Is there a more efficient way to write this code? For example could I use another with (ActiveSheet)?

With Sheet2
.Cells(varRow, 1).Value = ActiveSheet.Range("G3").Value
.Cells(varRow, 2).Value = ActiveSheet.Range("G4").Value
.Cells(varRow, 3).Value = ActiveSheet.Range("Total").Value
.Cells(varRow, 4).Value = ActiveSheet.Range("B11").Value
.Cells(varRow, 5).Value = ActiveSheet.Range("B12").Value
.Cells(varRow, 6).Value = ActiveSheet.Range("B13").Value
.Cells(varRow, 7).Value = ActiveSheet.Range("B14").Value
.Cells(varRow, 8).Value = ActiveSheet.Range("B15").Value
.Cells(varRow, 9).Value = ActiveSheet.Range("F11").Value
.Cells(varRow, 10).Value = ActiveSheet.Range("F12").Value
.Cells(varRow, 11).Value = ActiveSheet.Range("F13").Value
.Cells(varRow, 12).Value = ActiveSheet.Range("F14").Value
.Cells(varRow, 13).Value = ActiveSheet.Range("F15").Value
end with

Thank you all,

Rick

Clean up code using WITHs
 

Another way



Dim i As Integer
With Sheet2
.Cells(varRow, 1).Value = ActiveSheet.Range("G3").Value
.Cells(varRow, 2).Value = ActiveSheet.Range("G4").Value
.Cells(varRow, 3).Value = ActiveSheet.Range("Total").Value
For i = 4 To 8 Step 1
.Cells(varRow, i).Value = ActiveSheet.Cells(i + 7, "b").Value
Next i
For i = 9 To 13 Step 1
.Cells(varRow, i).Value = ActiveSheet.Cells(i + 2, "f").Value
Next i
End Wit

--
Message posted from http://www.ExcelForum.com