Sumproduct takes too long

[shahji]

Dear all,

I have the following macro and have tried a few things to make it run quicker but it still takes too long.
the below code only runs on one column but i have 40 such columns. so all in all macro takes about 30 mins to run.
Any idea how i can make it faster or other ways of achieving the same result.

Sub Sumproduct()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

Dim LastRow As Long


With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B3").Resize(LastRow - 1).Formula = "=SUMPRODUCT((S6OBData!A:A=A3)*(S6OBData!B:B=B$2),S6OBData!C:C)"

Range("B3:B2000").Value = Range("B3:B2000").Value

End With

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

many thanks for help,

Shah

[Salem]

> Range("B3:B2000").Value = Range("B3:B2000").Value
What does this do?
Not a lot as far as I can see.

[shahji]

Thanks Salem,

Range("B3:B2000").Value = Range("B3:B2000").Value

The Code puts the formula in each cell and formula gets the value from other sheet, at the end it changes all the cells to values rather than having the formula behind the scenes.

This is just one column and it takes about 40 seconds to complete this and I will be running this on 80 columns on two sheets. It is taking about 30mins on one sheet. Was wondering if there is another way to get the data.