Type mis match & is there a quicker way?

[paulsolar]

Hi All

When the code below gets to the end I get a type mismatch error, have i missed something off the end.

Also is there a quicker way of doing this?

Many thanks in advance

Paul

Sub Copy_Darren_To_his_Sheet()

   Dim LastRow As Long

   'Find last used row in a Column B of MiReg Installations
   With Worksheets("MiReg Installations")
      LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
   End With

   'Find first row where values should be posted in Darren Jones
   With Worksheets("Darren Jones")
      j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
   End With
   
  
   For i = 1 To LastRow
       With Worksheets("MiReg Installations")
           If .Cells(i, 2).Value = "Darren Jones" Then
               .Rows(i).Copy Destination:=Worksheets("Darren Jones").Range("A" & j)
               j = j + 1
           End If
       End With
   Next i
   
End Sub

 

[mumps]

Try:

Sub Copy_Darren_To_his_Sheet()
    Application.ScreenUpdating = False
    With Sheets("MiReg Installations")
        .Range("A1").AutoFilter 2, "Darren Jones"
        .AutoFilter.Range.Offset(1).Copy Sheets("Darren Jones").Cells(Sheets("Darren Jones").Rows.Count, "A").End(xlUp).Offset(1)
    End With
    Sheets("MiReg Installations").Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[rlv01]

I test ran your code and did not get any errors.

 

[paulsolar]

Sub Copy_Darren_To_his_Sheet() Application.ScreenUpdating = False With Sheets("MiReg Installations") .Range("A1").AutoFilter 2, "Darren Jones" .AutoFilter.Range.Offset(1).Copy Sheets("Darren Jones").Cells(Sheets("Darren Jones").Rows.Count, "A").End(xlUp).Offset(1) End With Sheets("MiReg Installations").Range("A1").AutoFilter Application.ScreenUpdating = True End Sub

Hi Mumps, that is fast, never thought to use a filter, fantastic!!! Cheers

 

[mumps]

Glad it worked out.