asked on

Excel VBA: copy single column from one sheet to multiple columns of another sheet

Hello experts,
I have the following template.
I am looking for a procedure that covers the following requirement:
1)      Clear data of Update-spot-mstt-match sheet from Range A2 till last used range of F
2)      Delete rows of Update-spot-mstt-match sheet from last used range till row 2 (row 2 excluded)
3)      Copy from Mapping sheet Range A2 till last used range to Range C2 of Update-spot-mstt-match sheet
4)      Copy from Mapping Range B2 till last used range to Range A2 and B2 of Update-spot-mstt-match sheet
5)      Copy from Mapping sheet Range C2 till last used range to Range D2 and E2 of Update-spot-mstt-match sheet
6)      Copy from Mapping Range D2 till last used range to Range F2 of Update-spot-mstt-match sheet
I attached dummy file.
If you have questions please contact me.
Data-transferring-template.xlsm

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you very much.
I will test it and keep you informed.

Luis Diaz

ASKER

Subodh,
I tested your proposal and it works:
I just added the required delete line as you did in question: https://www.experts-exchange.com/questions/29157752/Excel-VBA-Clear-and-copy-data-from-one-sheet-to-another.html
To make sure that sheet which is going to receive information hasn’t data.
One question came across about break point VBA feature:
I tested the delete by putting a break point at:

 If dlr > 2 Then tbl.Range.Rows("3:" & dlr).Delete

Open in new window

And the delete is not performed.
Since I don’t want to continue with next line as I want just to check the delete I putted another line such as:

 Msgbox dlr

Open in new window

However I think this is not the proper way. How to properly manage break points in that case?
To perform all the procedure till:

 If dlr > 2 Then tbl.Range.Rows("3:" & dlr).Delete

Open in new window

Here is the last version

Sub Copy_From_Mapping_To_Update()
    Dim wsUpdate    As Worksheet
    Dim wsMapping   As Worksheet
    Dim lr          As Long
    Dim dlr         As Long

    Application.ScreenUpdating = False

    Set wsUpdate = Worksheets("Update-spot-mstt-match")
    Set wsMapping = Worksheets("Mapping")
    Set tbl = wsUpdate.ListObjects(1)

    dlr = wsUpdate.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lr = wsMapping.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    'Clearing data from Update-spot-mstt-match Sheet
    If dlr > 2 Then tbl.Range.Rows("3:" & dlr).Delete
    
    'Clearing Column A:F on Update Sheet
    wsUpdate.Range("A2:F" & dlr).ClearContents
    
    'Copying Mapping column A
    wsMapping.Range("A2:A" & lr).Copy wsUpdate.Range("C2")

    'Copying Mapping column B
    wsMapping.Range("B2:B" & lr).Copy wsUpdate.Range("A2:B2")

    'Copying Mapping column C
    wsMapping.Range("C2:C" & lr).Copy wsUpdate.Range("D2:E2")

    'Copying Mapping column D
    wsMapping.Range("D2:D" & lr).Copy wsUpdate.Range("F2")

    Application.ScreenUpdating = True
End Sub

Open in new window

Let me know if you have comment on this.
Any recommendation on break points and other debug feature will be helpful.
Bill don’t hesitate to comment on this.

Luis Diaz

ASKER

@Bill I tested your proposal and it works.
However I realized that one step was missing.
I modified question:
2) Delete rows of Update-spot-mstt-match sheet from last used range till row 2 (row 2 excluded)

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you very much Subodh for this explanation. After months that I was trying to understand how breakpoints work and finally I understand now!
I am going to put your last comment in my knowledge base to apply it on a regular basis.

Luis Diaz

ASKER

@Bill: I tested your last proposal and it works! Thank you for your help.

Subodh Tiwari (Neeraj)

You're welcome Luis! Glad you found the explanation helpful.