Excel VBA Object Invoked has Disconnected from its Clients

Question

So I'm working on a user form to pull data from different excel sheets and build a summary sheet based on user inputs. All of this is within one workbook without external links, and most of the solutions I have seen for this error are the result of trying to connect/open an outside source. The code works until it reaches the tenth entry, then it gives me the Object Invoked has Disconnected from its Clients error and restarts excel. I have tried commenting out the tenth entry and the same error occurs at another interval.

Private Sub Submit_Click()


If TextBox_1.Value > 0 Then

   Worksheets("FirstSheet").UsedRange.Offset(3).Resize(Worksheets("FirstSheet").UsedRange.Rows.Count - 3).Copy
   Worksheets("Template").Rows("4").Insert shift:=xlDown

End If

If TextBox_2.Value > 0 Then

   Worksheets("SecondSheet").UsedRange.Offset(3).Resize(Worksheets("SecondSheet").UsedRange.Rows.Count - 3).Copy
   Worksheets("Template").Rows("4").Insert shift:=xlDown

End If

If TextBox_3.Value > 0 Then

   Worksheets("ThirdSheet").UsedRange.Offset(3).Resize(Worksheets("ThirdSheet").UsedRange.Rows.Count - 3).Copy
   Worksheets("Template").Rows("4").Insert shift:=xlDown

End If

...

If TextBox_9.Value > 0 Then

   Worksheets("NinthSheet").UsedRange.Offset(3).Resize(Worksheets("NinthSheet").UsedRange.Rows.Count - 3).Copy
   Worksheets("Template").Rows("4").Insert shift:=xlDown

End If

**If TextBox_10.Value > 0 Then
   Worksheets("TenthSheet").UsedRange.Offset(3).Resize(Worksheets("TenthSheet").UsedRange.Rows.Count - 3).Copy
   Worksheets("Template").Rows("4").Insert shift:=xlDown
End if**

Is the issue stemming from the number of repetitions within the code? Is there a specific item within the worksheet itself that I should be looking for that would be causing this issue?

Answer 1

You don't need to specify each sheet separately, you can use a loop like this

Option Explicit
Private Sub Submit_Click()

    Dim wb As Workbook, wsSource As Worksheet, wsTarget As Worksheet
    Set wb = ThisWorkbook
    Set wsTarget = wb.Sheets("Template")

    Dim sheetnames As Variant
    sheetnames = Array("", "FirstSheet", "SecondSheet", "ThirdSheet", "ForthSheet", _
    "FifthSheet", "SixthSheet", "SeventhSheet", "EighthSheet", "NinthSheet", "TenthSheet")

    Dim n As Integer, sName As String, sValue As String
    Dim rngSource As Range, rngTarget As Range

    Application.ScreenUpdating = False
    For n = 1 To UBound(sheetnames)
        sName = "TextBox_" & CStr(n)
        sValue = Me.Controls(sName)
        If Len(sValue) > 0 Then

            ' define ranges
            Set wsSource = wb.Sheets(sheetnames(n))
            Set rngSource = wsSource.UsedRange.Offset(3).Resize(wsSource.UsedRange.Rows.Count - 3)
            Set rngTarget = wsTarget.Rows(4)

            ' copy to Template
            rngSource.Copy
            rngTarget.Insert shift:=xlDown
            Application.CutCopyMode = False

        End If
    Next
    Application.ScreenUpdating = True
    MsgBox "Finished", vbInformation
End Sub

Answer 2

So I was able to get it running last night by splitting the code up and using variables to execute the commands. Not sure why it worked, but it worked.

Private Sub Submit_Click()

Dim Template As Range
Dim FirstSheet As Range

Set Template = Worksheets(2).Range("$A$4")
Set FirstSheet = Worksheets(3).UsedRange.Offset(3).Resize(Worksheets(3).UsedRange.Rows.Count - 3)

If TextBox_1.Value > 0 Then

   FirstSheet.Copy
   Template.Insert shift:=xlDown

End If