VBA copying entire rows and over writing destination formatting

[Dazjlbb]

Good morning,

I am completely new to VBA but what I require is in the code below which does work as intended.

One workbook = Three sheets

If the value in sheet3 column "T" is found in sheet2 column "T" and doesn't already exist in sheet3 then add the entire row to the bottom.

But my issue is I don't need the entire row only columns A:V and paste only values.

Any help on this would be amazing.

Sub Refresh()
    Dim ws2 As Worksheet, ws3 As Worksheet
    Dim cell As Range, Found As Range
    Dim FirstFound As String
    Dim bCopyInv   As Boolean
    Dim counter    As Long
   
    Set ws2 = Sheets("XP")
    Set ws3 = Sheets("Disposal")
   
    For Each cell In ws2.Range("T4", ws2.Range("T" & Rows.Count).End(xlUp))
        bCopyInv = True
        Set Found = ws3.Columns("T").Find(what:=cell.Value, _
                                          LookIn:=xlValues, _
                                          LookAt:=xlWhole, _
                                          SearchOrder:=xlByRows, _
                                          SearchDirection:=xlNext, _
                                          MatchCase:=False)
        If Not Found Is Nothing Then
            FirstFound = Found.Address
            Do
                If Found.Offset(0, 2).Value = cell.Offset(0, 2).Value Then
                    bCopyInv = False
                    Exit Do
                End If
                Set Found = ws2.Columns("T").FindNext(after:=Found)
            Loop Until Found.Address = FirstFound
        End If
       
        If bCopyInv Then
            cell.EntireRow.Copy Destination:=ws3.Range("A" & Rows.Count).End(xlUp).Offset(1)
            counter = counter + 1
        End If
       
    Next cell
   
    MsgBox counter & " new lines transfered", vbInformation, "Refresh Completed"
End Sub

 

[Alex Blakenburg]

Welcome to the Forum.

If you are just trying to limit the columns to copy either of these should work for you:

    ' Option 1
    cell.EntireRow.Resize(1, 22).Copy Destination:=ws3.Range("A" & Rows.Count).End(xlUp).Offset(1)

    ' Option 2
    With ws2
        .Range(.Cells(cell.Row, "A"), .Cells(cell.Row, "V")).Copy Destination:=ws3.Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With

 

[Dazjlbb]

Welcome to the Forum.

If you are just trying to limit the columns to copy either of these should work for you:

    ' Option 1
    cell.EntireRow.Resize(1, 22).Copy Destination:=ws3.Range("A" & Rows.Count).End(xlUp).Offset(1)

    ' Option 2
    With ws2
        .Range(.Cells(cell.Row, "A"), .Cells(cell.Row, "V")).Copy Destination:=ws3.Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With

First option worked like a charm. Thank you so much.

 

[Alex Blakenburg]

You're welcome. Thanks for letting me know.