Excel 2003 macro working very slow in Excel 2007 (xlsm format)

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

Even I could not find the reason. It looks like to me that macro in 2007 is not overwriting the previously copied content and is creating a seperate space for the newly copied data on RAM / Cache memory.

I have come to this conclusion since the macro starts very fast; however, when it comes to row 100 it becomes little slow and at rows 800-900 it works painfully slow.

Have you ever heard of this situation or come across this situtation in office 2007 where Macro does not overwrite the previous content? if yes, what is the solution for this. Thanks.

Hi,

I am unable to resolve the slowness issue with the above mentioned suggestions.

I just checked that my system RAM is 512 MB. Is this a reason for slowness? I heard that Office 2007 applications requires more memory.

What should be my ideal RAM configuration to run these types of macros efficiently? Thanks.

Thanks Roy,

Increasing the memory of my system wil take some time. I will check the code again once my system RAM is increased.

I still have one question. In my above code I have found that the below step is taking very long then expected. System stucks for 10-20 secods while executing this step. Can I made some changes to it to increase its speed? Thanks again for all your valuable suggestions.

Set D = Sht2Rng.Find(B.Value, LookIn:=xlValues)

Hi,

Sorry for bothering you all again.

Note that I have checked that Macro on a different system as well which has RAM of 2GB. however there is no improvement in the speed.

Initially the Macro starts very fast (while copying upto 200 rows)... however, once the row count reaches 500+ the speed slows down... and during 1,000+ rows the speed slows down dramatically...

My spreadsheet has over 2,000 rows. Dont know how to improve the speed. It used to work fine in Office 2003.