VBA Copy

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
Is there a way to set the copy range in the correct sequence? In this code, I want the data to copy in the same order as I have set the copy range from left to right. It seems to always revert back to the same order that it was in the worksheet. In this case, I want the Company Code in Column A in the Destination sheet, but it keeps ending up last.

Thanks
Mike

Code:
With Worksheets("Data1")
            Set rngH = .Range("T4")                                     'Company Code
            Set rngA = .Range("B4")                                     'Parcel
            Set rngCopy = Union(rngH, rngA.Resize(, 2))                 'Lot
            Set rngCopy = Union(rngCopy, rngA.Offset(, 17))             'TaskCode
            Set rngCopy = Union(rngCopy, rngA.Offset(, 10).Resize(, 2)) 'Task,Date,ActualDate
            rngCopy.Copy
            Worksheets("Report1").Range("A" & ToRow).PasteSpecial Paste:=xlPasteValues
        
    End With
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

how about
with worksheets("Dat11")
dim arr(5)
arr(0) = range("T4")
arr(1) = range("B4")
arr(2) = range("D4")
arr(3) = range("S4")
arr(4) = range("L4")
arr(5) = range("M4")
worksheets("report1").range("a" & torow).resize(1,6) = arr

Not sure about the copy ranges....


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,113,986
Messages
5,545,347
Members
410,679
Latest member
rolandbianco
Top