Copying selected Columns from one worksheet to another using VBA

Monty9

New Member
Joined
Feb 26, 2016
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,889
Messages
6,122,097
Members
449,065
Latest member
albertocarrillom

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
Back
Top