Excel with slow macro

Asked

Viewed 551 times

0

Good afternoon

After a simple copy macro, to update a planning sheet, the excel file becomes progressively slower, both in the execution of the macro and in the normal workability (write, drag content etc). The macro works with many cells but when I did it didn’t take so long.

I already tried to eliminate the screenupdating and keep going. Some suggestions?

Sheets("F 002 PLAN-NAVE1").Select
Range("B5:WH372").Select
Selection.Copy
Sheets("Cálculos").Select
Range("C8").Select
ActiveSheet.Paste
Sheets("F 002 PLAN-NAVE2").Select
Range("B5:PY372").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cálculos").Select
Range("WJ8").Select
ActiveSheet.Paste
Sheets("Parcial").Select
Application.CutCopyMode = False

Thanks in advance

excel vba
  • 1

    Try to do less SELECT, this usually consume a lot of processing, something else, when you finish the action in a "Sheets", finish it, can reduce consumption as well.

2 answers

1

Jose good afternoon, follow the following code, replying to the comment of Bruno Fonseca, do not use Select. I hope it helps, it is a simple code that references directly to your data source, independent of the active spreadsheet. Run Sub Copia_e_cola()

Sub Copia_E_Cola()
    copiaECola planDeOrigen:="F 002 PLAN-NAVE1", intervalorDeOrigen:="B5:WH372", _
        planDeDestino:="Cálculos", celulaDeDestino:="C8"

    copiaECola planDeOrigen:="F 002 PLAN-NAVE2", intervalorDeOrigen:="B5:PY372", _
        planDeDestino:="Cálculos", celulaDeDestino:="WJ8"
End Sub


Private Function copiaECola(planDeOrigen As String, intervalorDeOrigen As String, planDeDestino As String, celulaDeDestino As String)
    Worksheets(planDeOrigen).Range(intervalorDeOrigen).Copy Destination:=Sheets(planDeDestino).Range(celulaDeDestino)
End Function

0

If your spreadsheet has many calculations add at the beginning of the code:

Application.Calculation = xlCalculationManual

And in the end:

Application.Calculation = xlCalculationAutomatic

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.