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

acemanhattan

New Member
Joined
May 11, 2016
Messages
45
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
Try this:
Code:
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

New Member
Joined
May 11, 2016
Messages
45
Try this:
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
Try these tweaks:
Code:
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

New Member
Joined
May 11, 2016
Messages
45
Try these tweaks:
Code:
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

Code:
[COLOR=#333333]Sheets("Model").deActivate[/COLOR]
that I can bookend the code with?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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

New Member
Joined
May 11, 2016
Messages
45
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

New Member
Joined
May 11, 2016
Messages
45
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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

New Member
Joined
May 11, 2016
Messages
45
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.
 

Forum statistics

Threads
1,085,164
Messages
5,382,078
Members
401,768
Latest member
katana_flyer

Some videos you may like

This Week's Hot Topics

Top