Optimizing code

[BritsBlitz]

Hi. This question is purely for my own learning experience. I have the code below which works perfectly for what I want. I’m just interested in knowing if there’s a way to optimize the code even further and reduce the number of lines.

The code checks which of the three checkboxes I have listed on the “Setup” sheet are checked. It then copies the corresponding range for the checked boxes from the “TestPro” sheet and pastes it to the “TestPlan” sheet. After the paste, it moves to the cell where the next range will be pasted.

Private Sub Generate_Report()
Dim nbr As Long
Dim Rng(1 To 3) As String
Dim MvTo(1 To 3) As String


'Grounding Inspection
Rng(1) = ("A4:I14")
MvTo(1) = 11

'Equipment Inspection
Rng(2) = ("A15:I26")
MvTo(2) = 12

'Antenna System Test
Rng(3) = ("A29:I38")
MvTo(3) = 10


'CODE STARTS
Sheets("TestPlan").Activate
ActiveSheet.Cells(1, 1).Select

For nbr = 1 To 3
If Sheets("Setup").Cells(nbr, 3).Value = True Then
Sheets("TestPro").Range(Rng(nbr)).Copy
PasteValues
MoveToLine (MvTo(nbr)), 0
End If
Next nbr

End Sub

Sub PasteValues()
Sheets("TestPlan").Select
ActiveCell.PasteSpecial xlPasteColumnWidths
ActiveCell.PasteSpecial xlValues
ActiveCell.PasteSpecial xlFormats
Application.CutCopyMode = False
End Sub

Sub MoveToLine(line As Single, column As Single)
ActiveCell = ActiveCell.Offset(line, column).Select
End Sub

 

[shg]

Maybe ...

Private Sub Generate_Report()
    Dim i           As Long
    Dim avsRng      As Variant
    Dim aviRow      As Variant

    avsRng = Array("A4:I14", "A15:I26", "A29:I38")
    aviRow = Array(11, 12, 10)

    For i = 0 To UBound(avsRng)
        If Worksheets("Setup").Cells(i, "C").Value = True Then
            Worksheets("TestPro").Range(avsRng(i)).Copy
            With Worksheets("TestPlan").Cells(aviRow(i), "A")
                .PasteSpecial Paste:=xlPasteColumnWidths
                .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            End With
        End If
    Next i

    Application.CutCopyMode = False
End Sub

 

[Chris Macro]

Here's how I would have coded it (Note: untested):

CODE:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Generate_Report()<br><SPAN style="color:#00007F">Dim</SPAN> nbr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MvTo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>Rng = Array("A4:I14", "A15:I26", "A29:I38")<br>MvTo = Array(11, 12, 10)<br><br><SPAN style="color:#007F00">'CODE STARTS</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> cell = Sheets("TestPlan").Range("A1")<br><br>    <SPAN style="color:#00007F">For</SPAN> nbr = <SPAN style="color:#00007F">LBound</SPAN>(Rng) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Range)<br>        <SPAN style="color:#00007F">If</SPAN> Sheets("Setup").Cells(nbr, 3).Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">'Copy</SPAN><br>                Sheets("TestPro").Range(Rng(nbr)).Copy<br>            <br>            <SPAN style="color:#007F00">'Paste</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> cell<br>                    .PasteSpecial xlPasteColumnWidths<br>                    .PasteSpecial xlValues<br>                    .PasteSpecial xlFormats<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <br>            <SPAN style="color:#007F00">'Move Cell range down</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> cell = cell.Offset(MvTo(nbr))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> nbr<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[BritsBlitz]

I found in both these instances that by using either nbr = LBound(Rng) or using i=o, it will set the value to 0. But then when I look for my first check box status (If Sheets("Setup").Cells(nbr, 3).Value = True Then), the code points to cell (0,3) which does not exists and returns an error.

 

[BritsBlitz]

And I guess I answered my own question. I changed the cell to ((nbr + 1), 3) and it's working now. Thanks for both answers.

 

[Chris Macro]

No problem. Glad to help out!