CODEWITHSUNDEEP
excelvba
David C.
David C.

Reputation: 23

VBA code inconsistently crashes Excel (run-time error 80010108)

I'm using the following VBA code to automate moving a row on one sheet to another. About 1/3 of the time, it gives a "run-time error '-2147417848 (80010108)" and then crashes Excel. I cannot find a common reason why. After a crash, I can execute the same code on the same row, and it may or may not work fine the next time.

Can anyone tell why this code below should be unstable?

Sub Move_to_Sheet2 ()
'
' Move_to_Sheet2 Macro
'
' Keyboard Shortcut: Ctrl+r
'
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Set Rng = Nothing
    Sheets("Sheet2").Select
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet1").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.save
End Sub

Upvotes: 2

Views: 5953

Answers (2)

Mayukh Rakshit
Mayukh Rakshit

Reputation: 1

Sub Test()

' Test Macro

    Range("A24:C30").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 10
    Range("A31").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy

    Sheets("Save Sales").Select
    Range("B6").Select
    Selection.Insert Shift:=xlDown

    Sheets("Invoice").Select
    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = ""
    Range("B9").Select
    MsgBox "Print Now"

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149295

You need to fully qualify your Rows. See this example.

Sub Move_to_Sheet2()
    Dim ws As Worksheet

    '~~> Change this to the relevant sheet name
    Set ws = Sheets("Sheet1")

    With ws
        .Rows(ActiveCell.Row).Copy
        Sheets("Sheet2").Rows("4:4").Insert Shift:=xlDown
        .Rows(ActiveCell.Row).Delete
    End With
    ActiveWorkbook.Save
End Sub

Upvotes: 2

Related Questions