title: Error handling, copying rows
body: <p>Following my post here: <a href="https://stackoverflow.com/questions/33733182/error-handling-for-if-sheets-exists-when-copying-rows/33733872?noredirect=1#comment55238208_33733872">Error handling for if sheets exists when copying rows</a></p>
  
  <p>I am in dire need of assistance. I need help with getting the error handling to work. </p>
  
  <ol>
  <li>I need it to display a msgbox with the lookupVal from the Global sheet if it cannot find it in ComboBox2 list.</li>
  <li>Along with this piece of error handling, you i also ask for assistance in getting it to display a MsgBox once the code has finished running to show how many rows have been copied to each sheet and and total number of rows copied.</li>
  </ol>
  
  <p>Something similar to below, and if it copied more rows to other sheets then it would state after 2555, and then tells me the sheets were nothing was copied to.</p>
  
  <p><a href="https://i.stack.imgur.com/qtGIt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qtGIt.png" alt="enter image description here"></a></p>
  
  <pre><code>Private Sub CommandButton2_Click()
  Dim i As Long, j As Long, lastG As Long, strWS As String, rngCPY As Range
  
  With Application
      .ScreenUpdating = False
      .EnableEvents = False
      .CutCopyMode = False
  End With
  
  If Range("L9") = "" Then
      MsgBox "You can't Split the Jobs at this stage. " &amp; vbLf &amp; vbLf &amp; "Please create the form for the Sub-Contractor First." &amp; vbLf &amp; vbLf &amp; "Please press Display Utilities to create form.", vbExclamation, "Invalid Operation" '&amp; vbLf &amp; vbLf &amp; "This operation is invalid."
      Exit Sub
  End If
      On Error GoTo bm_Close_Out
  
      ' find last row
      lastG = sheets("Global").Cells(Rows.Count, "Q").End(xlUp).row
  
      For i = 3 To lastG
          lookupVal = sheets("Global").Cells(i, "Q") ' value to find
          ' loop over values in "details"
          For j = 0 To UserForm2.ComboBox2.ListCount - 1
              currVal = UserForm2.ComboBox2.List(j, 0) ' value to match
              If lookupVal = currVal Then
                  Set rngCPY = sheets("Global").Cells(i, "Q").EntireRow
                  strWS = UserForm2.ComboBox2.List(j, 1)
                  On Error GoTo bm_Need_Worksheet  '&lt;~~ if the worksheet in the next line does not exist, go make one
                  With Worksheets(strWS)
                      rngCPY.Copy
                      .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Insert shift:=xlDown
                  End With
                  '''' 
                    Code here for msgbox to say, lookupVal ie(ISLDAMAGE) is not in the 
                    list, please create the sheet manually then copy the rows accordingly
                  ''''
              End If
          Next j
      Next i
  
  GoTo bm_Close_Out
  
  bm_Need_Worksheet:
      On Error GoTo 0
      With Worksheet
      Dim wb As Workbook: Set wb = ThisWorkbook
      Dim wsTemplate As Worksheet: Set wsTemplate = wb.sheets("Template")
      Dim wsPayment As Worksheet: Set wsPayment = wb.sheets("Payment Form")
      Dim wsNew As Worksheet
      Dim lastRow2 As Long
      Dim Contract As String: Contract = sheets("Payment Form").Range("C9").value
      Dim SpacePos As Integer: SpacePos = InStr(Contract, "- ")
      Dim Name As String: Name = Left(Contract, SpacePos)
      Dim Name2 As String: Name2 = Right(Contract, Len(Contract) - Len(Name))
  
      Dim NewName As String: NewName = strWS
      Dim CCName As Variant: CCName = UserForm2.ComboBox2.List(j, 2)
  
      Dim lastRow As Long: lastRow = wsPayment.Range("U36:U53").End(xlDown).row
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      lastRow2 = wsPayment.Range("A23:A39").End(xlDown).row
  Else
      lastRow2 = wsPayment.Range("A18:A34").End(xlDown).row
  End If
  
      wsTemplate.Visible = True
      wsTemplate.Copy before:=sheets("Details"): Set wsNew = ActiveSheet
      wsTemplate.Visible = False
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      With wsPayment
          For Each cell In .Range("A23:A39")
              If Len(cell) = 0 Then
                  If sheets("Payment Form").Range("C9").value = "Network" Then
                      cell.value = NewName &amp; " - " &amp; Name2 &amp; ": " &amp; CCName
                  Else
                      cell.value = NewName &amp; " -" &amp; Name2 &amp; ": " &amp; CCName
                  End If
                  Exit For
              End If
          Next cell
      End With
  Else
      With wsPayment
          For Each cell In .Range("A18:A34")
              If Len(cell) = 0 Then
                  If sheets("Payment Form").Range("C9").value = "Network" Then
                      cell.value = NewName &amp; " - " &amp; Name2 &amp; ": " &amp; CCName
                  Else
                      cell.value = NewName &amp; " -" &amp; Name2 &amp; ": " &amp; CCName
                  End If
                  Exit For
              End If
          Next cell
      End With
  End If
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      With wsNew
          .Name = NewName
          .Range("D4").value = wsPayment.Range("A23:A39").End(xlDown).value
          .Range("D6").value = wsPayment.Range("L11").value
          .Range("D8").value = wsPayment.Range("C9").value
          .Range("D10").value = wsPayment.Range("C11").value
      End With
  Else
      With wsNew
          .Name = NewName
          .Range("D4").value = wsPayment.Range("A18:A34").End(xlDown).value
          .Range("D6").value = wsPayment.Range("L11").value
          .Range("D8").value = wsPayment.Range("C9").value
          .Range("D10").value = wsPayment.Range("C11").value
      End With
  End If
  
  wsPayment.Activate
  
      With wsPayment
          .Range("J" &amp; lastRow2 + 1).value = 0
          .Range("L" &amp; lastRow2 + 1).Formula = "=N" &amp; lastRow2 + 1 &amp; "-J" &amp; lastRow2 + 1 &amp; ""
          .Range("N" &amp; lastRow2 + 1).Formula = "='" &amp; NewName &amp; "'!L20"
          .Range("U" &amp; lastRow + 1).value = NewName &amp; ": "
          .Range("V" &amp; lastRow + 1).Formula = "='" &amp; NewName &amp; "'!I21"
          .Range("W" &amp; lastRow + 1).Formula = "='" &amp; NewName &amp; "'!I23"
          .Range("X" &amp; lastRow + 1).Formula = "='" &amp; NewName &amp; "'!K21"
      End With
      End With
  
      On Error GoTo bm_Close_Out
      Resume
  
  bm_Close_Out:
  
  With Application
      .ScreenUpdating = True
      .EnableEvents = True
      .CutCopyMode = True
  End With
  End Sub 
  </code></pre>
  
  <p>I have got this far with some help, but an failing at this last stage! All help is appreciated.</p>