VBA Copy Row to Sheet, Keep Destination Formatting

[knscha2]

The module successfully copies all rows from the source sheet to the destination sheet that have 'Fletcher Bone' in row B. How can I edit my code to copy the values from the source, yet keep the formatting in the destination? Any help you can provide is greatly appreciated!

    Set wsSource = Sheets("Overview")
    Set wsDestin = Sheets("Fletcher")
   
    With wsSource
        Set rngSource = .Range(.Cells(10, "B"), .Cells(.Rows.Count, "B").End(xlUp))
    End With
  
    For Each rngCel In rngSource
        If rngCel.Value = "Fletcher Bone" Then
            With wsDestin
                lngDestinRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
                .Cells(lngDestinRow, "B").EntireRow = rngCel.EntireRow.Value
            End With
        End If
    Next rngCel

 

[DanteAmor]

Hi and welcome to MrExcel.

Try this:

Sub copyrange()
  With Sheets("Overview")
    .Range("B9", .Range("B" & Rows.Count).End(3)).AutoFilter 1, "Fletcher Bone"
    .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Fletcher").Range("A" & Sheets("Fletcher").Range("B" & Rows.Count).End(3).Row + 1)
    .ShowAllData
  End With
End Sub