Copying selected Columns from one worksheet to another using VBA

Monty9

New Member
Joined
Feb 26, 2016
Messages
4
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;

Code:
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
I didn't test it.
Try this:
change
Code:
.Rows(LCnt).Copy Destination:=CopyToSht.Rows(LCopyToRow)

to

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

Monty9

New Member
Joined
Feb 26, 2016
Messages
4
I didn't test it.
Try this:
change
Code:
.Rows(LCnt).Copy Destination:=CopyToSht.Rows(LCopyToRow)

to

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top