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

1 Answers

0
Praveen NJ On Best Solutions

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