VBA to Copy Specific Column on One Worksheet and Paste Values to Specific Column on Other Worksheet

[acemanhattan]

Hello all.

I have a "Model" tab with data starting on row 101 and running down to a row which depends on what data remains after a previous Macro deletes out superfluous data. On a "Summary" tab I have a template that needs specific columns value pasted out of the Model tab into columns on the Summary tab.

For example I need:

Range W101:Wxx...xx on the model tab value pasted into A101:Axx...xx on the summary tab
Range J101:Jxx...xx on the model tab value pasted into B101:Bxx...xx on the summary tab
Range D101:Dxx...xx on the model tab value pasted into C101:Cxx..xx on the summary tab

Any possible solutions are appreciated.

Thanks in advance.

 

[My Aswer Is This]

Try this:

Sub MyCopy()
    Dim myCols As Variant
    Dim lastRow As Long
    Dim c As Long
    
    Sheets("Model").Activate
    
'   Set columns you want to loop through in an array
    myCols = Array("W", "J", "D")
    
'   Loop through columns array
    For c = LBound(myCols) To UBound(myCols)
'   Find last row in column W with data
        lastRow = Sheets("Model").Cells(Rows.Count, myCols(c)).End(xlUp).Row
'       Copy data from Model sheet to summary sheet
        Sheets("Model").Range(Cells(101, myCols(c)), Cells(lastRow, myCols(c))).Copy
        Sheets("Summary").Cells(101, c + 1).PasteSpecial Paste:=xlValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Next c
     Sheets("Summary").Activate
End Sub

 

[Joe4]

The macro runs successfully and produces the results I'd like it to, with the slight defect that its final step is to take the user away from the Summary tab and into the Model tab. In other words, I run the macro from Summary, it goes according to plan, and then when it completes it opens up the Model tab. I'd like the user to remain on the Summary tab.

Is that it? That is an easy fix.
Just add this line to the end of the VBA code, just before the "End Sub" line:

Sheets("Summary").Activate

 

[acemanhattan]

Is that it? That is an easy fix.
Just add this line to the end of the VBA code, just before the "End Sub" line:

Sheets("Summary").Activate

Yup, that's it! I figured it had to be something simple like that.

Thanks a lot for your help.

 

[Babalola]

if i want the colums to be cipy to a new workbook

 

[Joe4]

if i want the colums to be cipy to a new workbook

Please post your question to its own thread, and be sure to give a detailed explanation.