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