Excel animations running very slow after running Macro

Question

As per the title, I am recording some very simple macros that should copy data from one worksheet, paste into another, and perform some easy manipulations on the rows of the second worksheet.

However, after running the macro (code below) excel becomes extremely unresponsive. Even scrolling through the file becomes very slow.

Moreover, the CPU and Memory usage spikes up after using this macro

I have already recorded macros to automate some tasks and I have never experienced this kind of slowing down.

This part of the code copies and pastes data from one worksheet to another

Sub Banco_Creazione_Foglio()
'
' Creazione_Foglio_Banco Macro
'

'
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add(After:=ActiveSheet).Name = "Foglio Banco"
ActiveSheet.Paste
Range("A1").Select
End Sub

This part of the code deletes some irrelevant columns.

Range("O4").Select
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("G:G").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Range("G:G,P:P").Select
Range("P1").Activate
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
Columns("G:Q").Select
Selection.Delete Shift:=xlToLeft
Columns("H:I").Select
Selection.Delete Shift:=xlToLeft
Columns("K:L").Select
Selection.Delete Shift:=xlToLeft
Columns("M:Y").Select
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
End Sub

Hi nicola, start by deleting every single `ActiveWindow.ScrollColumn = 4` of your code (this only moves the screen) and read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to avoid the `.Select` or `.Activate` your code will be way cleaner and faster. Thought what you posted shouldn't slow your computer. — Damian, May 13 '19 at 08:34
Hi Damian, thanks for your reply. However, what I am saying is that the workbook itself is slow after running the macro. The macro itself runs smoothly and quickly, especially considering that the size of my worksheet is very small. — Nicola Bazinga Dragoni, May 13 '19 at 08:40
Could it be that you are copying heavy formulas and that makes Excel slower because autocalculation is on? Try turning off Calculation. Check [Application.Calculation property (Excel)](https://learn.microsoft.com/en-us/office/vba/api/excel.application.calculation). Also, can we see a data example? Is this all the code? Any events coded? Are you copying other heavy objects like charts or PivotTables? — Foxfire And Burns And Burns, May 13 '19 at 08:42
Hi, Foxfire. Yes, this is all the code that there is. I will try to turn of off Calculations even though I doubt this is the reason. There aren0t any events coded, nor I am pasting heavy data such as charts or PivotTables At the moment I don't feel confident with sharing data, since it is production data. — Nicola Bazinga Dragoni, May 13 '19 at 08:49

score 0 · Accepted Answer · answered May 13 '19 at 08:44

This is your code cleaned up with some stuff turned off, try if it helps:

Option Explicit
Sub Banco_Creazione_Foglio()
'
' Creazione_Foglio_Banco Macro
'

'
    Dim wsSource As Worksheet, wsPaste As Worksheet

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    Set wsSource = ActiveSheet
    Set wsPaste = ThisWorkbook.Sheets.Add(After:=wsSource)
    wsPaste.Name = "Foglio Banco"
    wsSource.UsedRange.Copy wsPaste.Range("A1") 'This is copying everything on your sheet, tell me if you just want to copy some delimited range.
    With wsPaste
        .Columns("G:Q").Delete Shift:=xlToLeft
        .Columns("H:I").Delete Shift:=xlToLeft
        .Columns("K:L").Delete Shift:=xlToLeft
        .Columns("M:Y").Delete Shift:=xlToLeft
        .Columns("N:N").Delete Shift:=xlToLeft
    End With

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

End Sub

Glad it helped. Research on how to avoid the use of `.Select` or `.Activate` like I told you on my comment, your code will be like this instead the one you posted and save you a lot of errors. — Damian, May 13 '19 at 09:09

Excel animations running very slow after running Macro

1 Answers1