Simple Macro was running fine, and then got super slow

[Abha23]

Hi everyone,

I'm hoping I can get some help in optimizing my macro. It essentially:

1. Copies a row from one sheet ("Update")
2. Inserts a row into another sheet ("Overall") which has the master table.
3. Copies the row from the Update sheet into the inserted row of the master table.

It doesn't have any logic beyond that, so I'm confused as to why it's moving so slowly - it takes around 5-6 seconds to run now, compared to the almost-instant speed at first.

Sub UpdateWithoutSort()
'
' UpdateWithoutSort Macro
''
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Sheets(“Overall”).Select
    Range(“B6:O6").Select
    Selection.ListObject.ListRows.Add (1)
    Sheets(“Update”).Select
    Range(“B15:M15").Select
    Selection.Copy
    Sheets(“Overall”).Select
    Range(“B6").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
End Sub

 

[VBE313]

How about

Sub UpdateWithoutSort()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
With Sheets("Overall")
    .Range("B6:O6").ListObject.ListRows.Add (1)
    With Sheets("Update")
        .Range("B15:M15").Copy
    End With
    .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
End Sub

 

[Abha23]

Thanks for helping @VBE313 - this gives me an error (run-time error '1004' )at the piece below:

.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Is there anything I'm missing?

 

[VBE313]

Thanks for helping @VBE313 - this gives me an error (run-time error '1004' )at the piece below:

.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Is there anything I'm missing?

Sorry,

Sub UpdateWithoutSort()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
With Sheets("Overall")
    .Range("B6:O6").ListObject.ListRows.Add (1)
    With Sheets("Update")
        .Range("B15:M15").Copy
    End With
    .PasteSpecial
End With
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
End Sub

 

[Abha23]

This helped quite a bit, thanks so much! It's not instantaneous, do you know if the reason is because the main table ("Overall" tab) getting larger as more and more rows are inserted into it?

 

[VBE313]

This helped quite a bit, thanks so much! It's not instantaneous, do you know if the reason is because the main table ("Overall" tab) getting larger as more and more rows are inserted into it?

It could mean many things. I would have to seethe document to help you further.