if then else statement not working

Hi, colin,

maybe just set a With-Statement with the sheet name and refer to that? If nothing is indicated ranges refer to the active sheet.

Private Sub CommandButton1_Click()
Dim x As Long, NR As Long, CR As Long, SC As Long
Dim c As Range
With Sheets("mfr_list")
  For Each c In .Range("Q3:Q100")
    If c.Value = "Yes" Then
      NR = 46
      CR = 3
      SC = 3
      For x = 3 To .UsedRange.Rows.Count
        If Application.CountBlank(.Range(.Cells(x, 1), .Cells(x, 6))) = 0 Then
          .Range(.Cells(x, 1), .Cells(x, 6)).Copy Sheets("Completed").Cells(CR, "A")
          .Range(.Cells(x, 1), .Cells(x, 6)).Copy Sheets("invoice").Cells(NR, "A")
          .Range(.Cells(x, 1), .Cells(x, 6)).Copy Sheets("stored contracts").Cells(SC, "A")
          .Range(.Cells(x, 1), .Cells(x, 6)).Copy Sheets("Completed").Cells(CR, "A")
          NR = NR + 1
          CR = CR + 1
          SC = SC + 1
        Else
          MsgBox "no copy"
        End If
      Next x
    End If
  Next c
End With
End Sub

Why not apply an Autofilter to narrow down the number of cells to be checked?

Ciao,
Holger