VBA Code very slow. How can it be sped up?

[Aretradeser]

I use this code (CODE 1) to copy data from one Excel sheet to another; but it is very slow. How can I speed it up?
In the sheet where I paste the data (Sheet7), I have this other code (CODE 2), which I think is responsible for the slowness of CODE 1.
CODE 1

Sub CopyPaste()
    Application.ScreenUpdating = False
    Sheets("Hoja6").Select
    Range("A2:I4678").Select
    Selection.Copy
    Sheets("Hoja7").Select
    Range("B2").Select
    ActiveSheet.Paste
    Application.ScreenUpdating = True
End Sub

CODE 2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ultimaFila, filaID, ultimoCodigo As Integer
ultimaFila = Sheets("Hoja7").Range("B" & Rows.Count).End(xlUp).Row
filaID = Sheets("Hoja7").Range("A" & Rows.Count).End(xlUp).Row
    If (ultimaFila > filaID) Then
        If (filaID = 1) Then
            ultimoCodigo = 1
        Else
            ultimoCodigo = Sheets("Hoja7").Range("A" & filaID).Value + 1
        End If
        Sheets("Hoja7").Range("A" & filaID + 1).Value = ultimoCodigo
End If
End Sub

 

[My Aswer Is This]

Your first script can be written like this:

Sub Speed_Me_Up()
'Modified 10/11/2021  6:48:31 PM  EDT
Application.ScreenUpdating = False
Sheets("Hoja6").Range("A2:I4678").Copy Sheets("Hoja7").Range("B2")
Application.ScreenUpdating = True
End Sub

 

[rlv01]

Something like this should help:

Sub CopyPaste()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Sheets("Hoja6").Range("A2:I4678").Copy Sheets("Hoja7").Range("B2")
    
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

The second code you showed I'm not able to read.

 

[Aretradeser]

The second code you showed I'm not able to read.

On sheet7 there is a column titled "ID" that numbers the rows of data. When I copy and paste from sheet6, sometimes there are more rows on sheet6 than on sheet7. Since the data is copied to sheet7 from column "B", there are blank cells left in the "ID" column. That is why this code (CODE 2). If you can think of another way to complete this numbering, that would be great.

 

[My Aswer Is This]

When referring to columns it's always best to say column B or C saying column ID
May mean the column is named ID or maybe it has ID in row 1 of the column.
Either way it's harder for me to know what column your dealing with.
Now if we are referring to Table columns it best to say column 1 or column 3 of the Table named Table3

 

[Aretradeser]

On Sheet7 in column "A", entitled "ID", which numbers the rows of data. When I copy and paste from Sheet6, sometimes, there are more rows of data on sheet 6 than on sheet 7. As the data is copied to sheet7, starting from column "B", there are blank cells in column "A". That's why this code (CODE 2), to number those blank cells that are left empty, once the data has been copied. If you can think of another way to complete this numbering, that would be great.

When referring to columns it's always best to say column B or C saying column ID
May mean the column is named ID or maybe it has ID in row 1 of the column.
Either way it's harder for me to know what column your dealing with.
Now if we are referring to Table columns it best to say column 1 or column 3 of the Table named Table3

On Sheet7 in column "A", entitled "ID", which numbers the rows of data. When I copy and paste from Sheet6, sometimes, there are more rows of data on sheet 6 than on sheet 7. As the data is copied to sheet7, starting from column "B", there are blank cells in column "A". That's why this code (CODE 2), to number those blank cells that are left empty, once the data has been copied. If you can think of another way to complete this numbering, that would be great.

 

[Alex Blakenburg]

I have this other code (CODE 2), which I think is responsible for the slowness of CODE 1.
CODE 1

I agree that this is responsible for it running slow.
Firstly from a design perpective if you only want the ID (column A) to update after the copy why not call it a sub from the Copy procedure rather than have it as a change event which is called every time something changes on the worksheet.

If you have to have it as a worksheet change event, then at the moment you are updating the ID one cell at a time and each time you do that you are calling the event again.

Try wrapping your Code inside this:

Application.EnableEvents = False
Application.screenupdate = False

' ---- Your code goes here ----

Application.EnableEvents = True
Application.screenupdate = True

If it is still too slow you can try this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ultimaFila As Long, filaID As Long
    Dim rngID As Range 
    Application.EnableEvents = False
    Application.screenupdate = False
    
    ultimaFila = Sheets("Hoja7").Range("B" & Rows.Count).End(xlUp).Row
    filaID = Sheets("Hoja7").Range("A" & Rows.Count).End(xlUp).Row
    Set rngID = Range(Cells(filaID, "A"), Cells(ultimaFila, "B"))
    
        If (ultimaFila > filaID) Then
            If (filaID = 1) Then
                rngID.Cells(1, 1) = 1
            End If
            rngID.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                    Step:=1, Trend:=False
        End If
        
    Application.EnableEvents = True
    Application.screenupdate = True
End Sub

 

[Aretradeser]

I agree that this is responsible for it running slow.
Firstly from a design perpective if you only want the ID (column A) to update after the copy why not call it a sub from the Copy procedure rather than have it as a change event which is called every time something changes on the worksheet.

If you have to have it as a worksheet change event, then at the moment you are updating the ID one cell at a time and each time you do that you are calling the event again.

Try wrapping your Code inside this:

Application.EnableEvents = False
Application.screenupdate = False

' ---- Your code goes here ----

Application.EnableEvents = True
Application.screenupdate = True

If it is still too slow you can try this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ultimaFila As Long, filaID As Long
    Dim rngID As Range
    Application.EnableEvents = False
    Application.screenupdate = False
   
    ultimaFila = Sheets("Hoja7").Range("B" & Rows.Count).End(xlUp).Row
    filaID = Sheets("Hoja7").Range("A" & Rows.Count).End(xlUp).Row
    Set rngID = Range(Cells(filaID, "A"), Cells(ultimaFila, "B"))
   
        If (ultimaFila > filaID) Then
            If (filaID = 1) Then
                rngID.Cells(1, 1) = 1
            End If
            rngID.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                    Step:=1, Trend:=False
        End If
       
    Application.EnableEvents = True
    Application.screenupdate = True
End Sub

Thank you for your quick and very accurate response. Wrapping my code, as you recommend, speeds it up a lot.
Now I have another problem, and that is that CODE 2 does not run. Any solution?

 

[Alex Blakenburg]

Can you show me your current code2 code ?