1

I have a big problem and its driving me insane. I have a very simple piece of code that is supposed to copy a row and add it in below the active row plus a validation at the start of the code to check that you are allowed to add the row on that particular line.

The macro works perfectly when you first go in to the sheet. However, as soon as i enter anything in on any of the cells on the sheet the code bombs out with an automation error. Please say someone has found this before and has a fix for it?

The line it doesn't like is as shown here. Selection.Insert Shift:=xlDown

Sub Staffing_AddRow()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    ActiveCell.Select
    Cells(ActiveCell.Row, 223).Select
    If ActiveCell.Value = "Y" Then
        ActiveSheet.Unprotect Password:="PasswordGoesHere"
        '------------------------------------
        ActiveCell.Rows("1:1").EntireRow.Select
        Selection.Copy
        Selection.Insert Shift:=xlDown
        '------------------------------------
        Cells(ActiveCell.Row, 13).Select
        ActiveSheet.Protect Password:="PasswordGoesHere"
    Else
        If Response = MsgBox("You can't insert a row here!", _
            vbCritical, "Warning") Then
        Cells(ActiveCell.Row, 13).Select
        End If
        Cells(ActiveCell.Row, 13).Select
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

when it tries to paste that specific row in the worksheet I get Run-time error '-2147417848 (80010108)': Automation error the object invoked has disconnected from its clients.

L42
  • 19,427
  • 11
  • 44
  • 68
Ian Gough
  • 43
  • 1
  • 2
  • 8
  • Is the code in a normal module or a worksheet module? Also, what happens if you do it manually in the exact same place? – Cool Blue Jan 13 '15 at 23:58
  • The code is in a normal module and it works with absolutely no problems at all when its done manually. – Ian Gough Jan 15 '15 at 08:29

1 Answers1

2

Try this: Using With ActiveSheet

Sub Staffing_AddRow()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    ActiveCell.Select

    'CHANGES BEGIN HERE
    With ActiveSheet
        If .Cells(ActiveCell.row, 223).Value = "Y" Then
            ActiveSheet.Unprotect Password:="PasswordGoesHere"
            '------------------------------------
            ActiveCell.Rows("1:1").EntireRow.Select
            Selection.Copy
            Selection.Insert Shift:=xlDown
            '------------------------------------
            .Cells(ActiveCell.row, 13).Select
            ActiveSheet.Protect Password:="PasswordGoesHere"
        Else
            If Response = MsgBox("You can't insert a row here!", _
                vbCritical, "Warning") Then
            .Cells(ActiveCell.row, 13).Select
            End If
            .Cells(ActiveCell.row, 13).Select
        End If
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End With
End Sub

See also: How to avoid using select statements in macros

Community
  • 1
  • 1
peege
  • 2,467
  • 1
  • 10
  • 24
  • Sadly it didn't work, it gave the exact same error message. I will have a look at removing the select statements. Thanks for your effort though. – Ian Gough Jan 15 '15 at 08:30
  • Try taking out the sheet protection for a pass to narrow down the cause – peege Jan 15 '15 at 08:36
  • That didn't work either,i have also tried turning off the data validations and freeze panes on the sheet in question to see if they carry any quirks but no joy so far. – Ian Gough Jan 15 '15 at 08:53
  • This might sound strange, but you could try copying all the data on the sheet to a new sheet, rename it to the name you are using and try again – peege Jan 15 '15 at 08:55
  • No joy unfortunatley, i even cut the code down to just 3 lines and it worked the first time then crashed ActiveCell.Rows("1:1").EntireRow.Select Selection.Copy Selection.Insert Shift:=xlDown – Ian Gough Jan 15 '15 at 09:32