Speeding up code: takes 200 seconds to copy paste from one table to another using VBA!

[Celticfc]

Hi, I have a code which filters a table, copies the filtered data and puts it into another table as values but this process takes too long (170-200 seconds to be precise!). If I convert the table to a range then paste, it takes about 15 seconds but destroys all my formulas with reference to headers i.e. employee[location] to sheetname/column name etc.

Is there anything that I can do to speed up the process?

I would appreciate any support.

Thank you.

Code:

Sub ReceiveData()
Dim wb As Workbook
Dim t As Single
  
t = timer
Set wb = Workbooks("master.xlsb")
Set datatable = wb.Worksheets("alltemps").ListObjects("datatable")
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
   
 datatable.Range.AutoFilter Field:=2, Criteria:="temps"
    
 datatable.Range("A2:k3000").SpecialCells(xlCellTypeVisible).Copy
    
Calculate
    ThisWorkbook.Sheets("local").Range("A2:k3000").Clear
    ThisWorkbook.Sheets("local").Range("A2:k3000").PasteSpecial xlPasteValues
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic


MsgBox (Format(timer - t, "0.000 secs"))
End Sub

 

[PhilS2520]

Hi Celticfc,


You have covered all the good stuff, so I will ask:

Do you have any volatility in your formulas? The more you have of these the more time it will need to calculate at each itineration. Even though you do have calculation set to manual, it may be taking the time to calculate before the code gets to that line. I have a 63,000 record table, and it does take some time, but for 3,000 it might be something else.

Also, it maybe that the workbook may be corrupted, this used to happen in older version of Excel, but you don’t know until you investigate.

 

[footoo]

Why do you have Calculate in the macro?

Have you tried stepping through to see what is taking a long time?