Copying selected Columns from one worksheet to another using VBA

[Monty9]

Hello,

I have an Excel workbook with two worksheets - MASTER and DELIVERY. The structure is provided below and have same structure;

**Doc_number** **Doc_version** **Unit**
43449 01 D013-LAG R
43450 02 D013-LAG R
43451 01 D013-DAMP
43452 02 D013-DAMP

I have a code which searches and copies data to DELIVERY file against the 'Unit' value we provide. Code is as follow;

Sub SearchnPaste()

Dim LSearchRow As Long
Dim LCopyToRow As Long
Dim CopyFromSht As Worksheet
Dim CopyToSht As Worksheet
Dim LCnt As Long


On Error GoTo Err_Execute
Set CopyFromSht = Workbooks("TestRow.xlsm").Sheets("MASTER")
Set CopyToSht = Workbooks("TestRow.xlsm").Sheets("DELIVERY")

With CopyFromSht
    'Start search in row 4
    LSearchRow = .Range("A" & Rows.Count).End(xlUp).Row

    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2

    For LCnt = 2 To LSearchRow

    'If value in column Z = "Unit as needed", copy entire row to Sheet2
        If .Range("Z" & LCnt).Value = "D013-LAG R" Then

        'Select row in Sheet1 to copy
            .Rows(LCnt).Copy Destination:=CopyToSht.Rows(LCopyToRow)

        'Move counter to next row
            LCopyToRow = LCopyToRow + 1

        End If
   Next LCnt
End With

Code does what it is supposed to do but I need your help in modifying it so that I can paste only the selective columns to the DELIVERY file even though I search and copy all the records from Rows of the MASTER file.

 

[Akuini]

I didn't test it.
Try this:
change

.Rows(LCnt).Copy Destination:=CopyToSht.Rows(LCopyToRow)

to

Intersect(.Rows(LCnt), Selection.EntireColumn).Copy Destination:=CopyToSht.Rows(LCopyToRow)

 

[Monty9]

I didn't test it.
Try this:
change

.Rows(LCnt).Copy Destination:=CopyToSht.Rows(LCopyToRow)

to

Intersect(.Rows(LCnt), Selection.EntireColumn).Copy Destination:=CopyToSht.Rows(LCopyToRow)

Hi @Akuini; Sorry it doesn't works. I want to copy the complete row from MASTER file and then match the column with DELIVERY file to fill them up. Can you please advice on the same.