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.

 

[Joe4]

Try this:

Sub MyCopy()

    Dim myCols As Variant
    Dim lastRow As Long
    Dim c As Long
    
'   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)
    Next c
            
End Sub

 

[acemanhattan]

Try this:

Sub MyCopy()

    Dim myCols As Variant
    Dim lastRow As Long
    Dim c As Long
    
'   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)
    Next c
            
End Sub

Thanks for the response; I think this is nearly all the way there.

The two minor issues I have are the following

1. I seem to be retaining the format of the copied cells, when all I'd like to retain are the values.
2. I seem to be unable to run the macro from my summary tab(but am able to run it from model tab); I'll probably have the user prompt the macro from the summary tab, so I'd have to modify the code in some way. It seems like I'd need to "activate" the summary tab, somehow. No?

 

[Joe4]

Try these tweaks:

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
            
End Sub

 

[acemanhattan]

Try these tweaks:

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
            
End Sub

This is nearly perfect! The only remaining issue is that the macro terminates by going to the model tab. Is there something like

[COLOR=#333333]Sheets("Model").deActivate[/COLOR]

that I can bookend the code with?

 

[Joe4]

Where have you placed this code?
Did you place it in the Model or Summary worksheet module?
It should NOT be placed there. It should be placed in a Standard Module.

 

[acemanhattan]

Where have you placed this code?
Did you place it in the Model or Summary worksheet module?
It should NOT be placed there. It should be placed in a Standard Module.

I'm afraid I do not know. I proceeded by clicking on Macros from the Developer tab, creating a new macro, and pasting the code in.

I'm very new at VBA, but this is how I typically proceed. It sounds like that's a bit haphazard?

As far as modules go: when I go to Visual Basic I have a list of about 6 modules that show up below (e.g. Module1, Module2, Module4 etc).

 

[acemanhattan]

From the bit of reading I just did, I don't think I placed it in either the Model or Summary module; as I understand it, this would require me to go into Visual Basic, view code on the sheet of interest, and then paste the code in there; this I did not do. I think I placed it in the standard module.

 

[Joe4]

You want to place it in any of the Module that start with the word "Module...".
If you did, and you are still having issues, please explain exactly what is happening.

 

[acemanhattan]

You want to place it in any of the Module that start with the word "Module...".
If you did, and you are still having issues, please explain exactly what is happening.

Okay, yes, it's in module3.

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.