Copying selected Columns from one worksheet to another using VBA

Monty9

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,787
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
19
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)

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,559
Messages
5,832,474
Members
430,136
Latest member
Asir Jefferson

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