Hello Everyone,

I have a macro in Excel 2003 and I saved that file in Excel 2007 (xlsm) version; however, the code is working very very slow. In excel 2003 it used to take me 1-2 minutes but now its taking over 20 mins.

Below is the code that I have in excel 2003. it copies the data from another tab of the same spreadsheet. Please suggest if I need to make any changes in the code to make its speed better.

Thanks!
Kapil

Sub CopyColC()

Dim Sht1Rng As Range
Dim Sht2Rng As Range
Dim B As Range
Dim D As Range
Dim mainSht As Worksheet
Dim backUpSht As Worksheet
Dim CellToCopyTo As Range

'Compares the ID cell in both worksheets to each other

Set mainSht = Worksheets("Main")
Set backUpSht = Worksheets("Backup")

Set Sht1Rng = Worksheets("Main").Range("B6", Worksheets("Main").Range("B65536").End(xlUp))

Set Sht2Rng = Worksheets("Backup").Range("B6", Worksheets("Backup").Range("B65536").End(xlUp))

For Each B In Sht1Rng
Set D = Sht2Rng.Find(B.Value, LookIn:=xlValues)

'If same value found in col B of "Backup" sheet then copy col C
If Not D Is Nothing Then
            
            backUpSht.Cells(D.Row, 3).Copy
            
            Set CellToCopyTo = mainSht.Cells(B.Row, 3)
            CellToCopyTo.PasteSpecial xlPasteValues
                     
End If

Set D = Nothing

Next B

Application.CutCopyMode = False

End Sub