Help me optimize VBA Excel code for copying certain columns of every row on a sheet to another.

Question

Hi all,

I need to copy certain columns of every row in sheet A into sheet B.

I have created a sub that creates 2 arrays (variants) of the matching column numbers, so I can map column 3 in sheet A to be equal to column 8 in sheet B, etc.

Everything works fine, thing is it's quite slow, here it is:

Sub insertIntoSelectedOpps(opCols As Variant, siebelCols As Variant, ByVal length As Integer)

Dim insertRange As Range
Dim siebelRange As Range
Dim rowCount As Integer

Set insertRange = shSelected.Range("a3", "bb4") 'very wide table!'
Set siebelRange = shDatabase.UsedRange.Rows

rowCount = siebelRange.Rows.count

MsgBox "siebel row count: " & rowCount

For i = 2 To rowCount

    Set insertRange = shSelected.Range("a3", "bb4")
    insertRange.Insert

    For x = 1 To length - 1
        If opCols(x) <> -1 Then 'unequal to -1'
            insertRange.Cells(1, opCols(x)).value = siebelRange.Cells(i, siebelCols(x)).value
        End If
    Next x

Next i
End Sub

Don't worry bout the IF case, it's business logic in case a column name in the mapping wasn't found.

Answer 1

Usually, stopping screen updates and calculation before a large treatment is a good idea:

After the msgbox:

Application.ScreenUpdating = False
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual

After the next

Application.Calculation = xlCalc
Application.ScreenUpdating = True

This should speed things. Also, take a look at this page for some more speed tweaks