Copy paste in certain column order

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
135
Office Version
  1. 2013
Platform
  1. Windows
Hi All

Is there a shorter/better way than the below to copy/paste a range in a different column order? I work with much larger ranges that the below so a shorter version would be great.

Thanks!

VBA Code:
LR4 = Workbooks("GATE").Sheets(1) .Range("D" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Sheets("admin").Range("F3:F" & LR4).Value = GATE.Sheets(1).Range("J3:J" & LR4).Value
    ThisWorkbook.Sheets("admin").Range("G3:G" & LR4).Value = GATE.Sheets(1).Range("F3:F" & LR4).Value
    ThisWorkbook.Sheets("admin").Range("H3:H" & LR4).Value = GATE.Sheets(1).Range("G3:G" & LR4).Value
    ThisWorkbook.Sheets("admin").Range("I3:I" & LR4).Value = GATE.Sheets(1).Range("C3:C" & LR4).Value
    ThisWorkbook.Sheets("admin").Range("J3:J" & LR4).Value = GATE.Sheets(1).Range("D3:D" & LR4).Value
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub cortexnotion()
   Dim Ary As Variant
   Dim LR4 As Long
   
   LR4 = Workbooks("GATE").Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
   Ary = Workbooks("GATE").Sheets(1).Range("C3:J" & LR4).Value2
   
    ThisWorkbook.Sheets("admin").Range("F3:J" & LR4).Value = Application.Index(Ary, Evaluate("row(1:" & LR4 - 2 & ")"), Array(8, 4, 5, 1, 2))
End Sub
 

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
135
Office Version
  1. 2013
Platform
  1. Windows
@Fluff, nifty thank you. I can see Value2 stops the date format shifting to US format which is useful to know!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,302
Messages
5,571,432
Members
412,392
Latest member
Carterland
Top