Need to select different columns from one sheet and paste them into another sheet

M

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
82
Hi Experts,

I need to Select certain columns from one worksheet and copy them into a different worksheet, since the number of records may vary from one day to the other, I'd like to make my selection dynamic, so I can copy and paste all the records, no less no more, I've tried with this code, but I am getting the error "Type Mismatch", does anyone of you have an idea on how can I achieve this?

Sub CopyValues()

Dim ws As Worksheet
Dim copyFrom() As Variant
Dim copyTo() As Variant
Dim i As Integer

copyFrom() = Array("C2:C", "D2:D", "F2:F", "H2:H", "I2:I", "J2:J", "K2:K", "O2:O" _
, "Q2:Q", "R2:R", "X2:X", "Y2:Y", "Z2:Z", "AA2:AA", "AG2:AG", "AH2:AH", "AU2:AU" _
, "AV2:AV", "AW2:AW", "AX2:AX")

copyTo() = Array("A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2" _
, "N2", "O2", "P2", "Q2", "R2", "S2", "T2")


With ws


Worksheets("All Asset Report no format").Range(copyFrom & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range(copyTo).PasteSpecial Paste:=xlPasteValues

i = i + 1

End With

End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi mpatino,

Try this:

Code: 
Option Explicit
Sub CopyValues()
    
    Dim copyFrom() As Variant
    Dim copyTo() As Variant
    Dim i As Integer
    
    Application.ScreenUpdating = False
        
    copyFrom() = Array("C2:C", "D2:D", "F2:F", "H2:H", "I2:I", "J2:J", "K2:K", "O2:O" _
    , "Q2:Q", "R2:R", "X2:X", "Y2:Y", "Z2:Z", "AA2:AA", "AG2:AG", "AH2:AH", "AU2:AU" _
    , "AV2:AV", "AW2:AW", "AX2:AX")
    
    copyTo() = Array("A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2" _
    , "N2", "O2", "P2", "Q2", "R2", "S2", "T2")

    For i = LBound(copyFrom) To UBound(copyTo) 'As long as there's the EXACT number of elements in each array this is OK
        'Debug.Print copyFrom(i) & copyTo(i)
        Worksheets("All Asset Report no format").Range(copyFrom(i) & Cells(Rows.Count, "D").End(xlUp).Row).Copy '<- Always Col. D from the active sheet??
        Worksheets("All Asset").Range(copyTo(i)).PasteSpecial Paste:=xlPasteValues
    Next i
    
    Application.ScreenUpdating = True

End Sub

Just not sure why you're using Col.D on the active sheet to determine the last row to copy on each occasion?

Regards,

Robert
 
Last edited:
Upvote 0
Hi Trebor,

Thanks a lot for taking the time to check out my question and for replying back to my message. I have tried your code and it only iterates one time, so it only copies the first row. The following code actually does what I need to do but in several lines, so that is why I wanted to simplify the code: (Regarding why D, it is becuase there will always be a value in all cells in column D)

Worksheets("All Asset Report no format").Range("C2:C" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("A2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("B2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("F2:F" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("C2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("H2:H" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("D2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("I2:I" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("E2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("J2:J" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("F2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("K2:K" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("G2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("O2:O" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("H2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("Q2:Q" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("I2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("R2:R" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("J2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("X2:X" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("K2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("Y2:Y" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("L2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("Z2:Z" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("M2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("AA2:AA" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("N2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("AG2:AG" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("O2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("AH2:AH" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("P2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("AU2:AU" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("Q2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("AV2:AV" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("R2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("AW2:AW" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("S2").PasteSpecial Paste:=xlPasteValues
Worksheets("All Asset Report no format").Range("AX2:AX" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
Worksheets("All Asset").Range("T2").PasteSpecial Paste:=xlPasteValues

Appreciate if you have any other suggestion?
 
Upvote 0
I have tried your code and it only iterates one time, so it only copies the first row.
Click to expand...

Not sure what you mean as it worked for me :confused:

The last row to be copied is being set on the last row in column D of the active sheet i.e. the sheet you're on when you run the code so maybe that's issue?
 
Upvote 0
Here's an alternative method that uses the last row in the column being copied from the "All Asset Report no format" tab each time:

Code: 
Option Explicit
Sub CopyVals2()
    
    Dim varMyRanges() As Variant
    Dim strMyCols() As String
    Dim lngArrayIndex As Long
    
    Application.ScreenUpdating = False
    
    'These represent the columns to be copied from and to.  For example, the contents of column C will be copied to column A.
    varMyRanges = Array("C-A", "D-B", "F-C", "H-D", "I-E", "J-F", "K-G", "O-H", "Q-I", "R-J", "X-K", "Y-L", "Z-M", "AA-N", "AG-O", "AH-P", "AU-Q", "AV-R", "AW-S", "AX-T")
    
    For lngArrayIndex = LBound(varMyRanges) To UBound(varMyRanges)
        strMyCols = Split(varMyRanges(lngArrayIndex), "-") 'Columns are split via a dash
        'strMyCols(0) is the column the data is being copied from and strMyCols(1) is the column the data is being copied to.
        Worksheets("All Asset Report no format").Range(strMyCols(0) & "2:" & strMyCols(0) & Worksheets("All Asset Report no format").Cells(Rows.Count, strMyCols(0)).End(xlUp).Row).Copy
        Worksheets("All Asset").Range(strMyCols(1) & "2").PasteSpecial Paste:=xlPasteValues
    Next lngArrayIndex
    
    Application.ScreenUpdating = True
    
    MsgBox "Ranges have now been copied.", vbInformation

End Sub

Robert
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
725
Joe4
Joe4
S
  • Solved
Copy row 2 and paste it after last row of another worksheet
Replies
9
Views
867
rollis13
rollis13
T
  • Question
Copy row values from one worksheet in one workbook to multiple workbooks based on file name
Replies
0
Views
159
thechad
T
S
  • Question
VBA to copy data from one worksheet to another (only specific columns)
Replies
2
Views
750
SpireiteSteve
S
S
  • Solved
Copying Filtered Columns (non-adjacent) from one Table to end of another, but need to also add information in adjacent column of Destination Table
Replies
1
Views
350
sspatriots
S

Forum statistics

Threads
1,214,864
Messages
6,121,986
Members
449,058
Latest member
oculus

Share this page

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
Back
Top