copy columns in different sequence to new sheet

opislak

New Member
Joined
Feb 28, 2017
Messages
45
Hello all,

I have a sheet with 50 columns. I need to copy 4 of them and paste them on a newly created sheet, but in a different order. The new order should be: C-B-D-E
This is my code so far. The copying works OK, but the result is that all data stays in the original order B-C-D-E.
Thanks for any help!
VBA Code:
Sub copy()

Dim ws As Worksheet
Dim lastRow, lastCol As Long

    Set ws = Sheets("Sheet1")

    ws.Select
    lastRow = Range("A:A").Find("*", , , , xlByRows, xlPrevious, , , False).Row
    lastCol = Range("1:1").Find("*", , , , xlByColumns, xlPrevious, , , False).Column

    Set rng2copy = Application.Union(Range("C1:C" & lastRow), Range("B1:B" & lastRow), Range("D1:D" & lastRow), Range("E1:E" & lastRow))
    rng2copy.Copy
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Selection"
    ActiveSheet.Paste Cells(1, 1)
    Application.CutCopyMode = False

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
Why not just copy all four columns once, as-is, and then insert a column and move column C in front of column B, and then delete the blank column?
You can get all this VBA code pretty easily simply by turning on the Macro Recordered and recording yourself performing these steps manually.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub copy()

Dim ws As Worksheet
Dim lastRow, lastCol As Long
Dim Ary As Variant
    Set ws = Sheets("Sheet1")

    ws.Select
    lastRow = Range("A:A").Find("*", , , , xlByRows, xlPrevious, , , False).Row
    lastCol = Range("1:1").Find("*", , , , xlByColumns, xlPrevious, , , False).Column
    
    Ary = Range("B1:E" & lastRow).Value2
    Ary = Application.Index(Ary, Evaluate("row(1:" & lastRow & ")"), Array(2, 1, 3, 4))
    
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Selection"
   Cells(1, 1).Resize(lastRow, 4).Value = Ary
End Sub
 
Solution

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows
You said:
I have a sheet with 50 columns. I need to copy 4 of them

1. What is the name of this sheet?
2. What 4 columns do you want to copy?
 

opislak

New Member
Joined
Feb 28, 2017
Messages
45

ADVERTISEMENT

Thanks Joe, I already did that successfully, but I thought that was not an elegant solution :)
This is the code I used for that part (works fine):

VBA Code:
            Range("A3", Cells(Range("A2"), 4)).ClearContents
            RClist.Copy
            ws.Range("A3").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
                Team = "100"
                Range("A1").AutoFilter Field:=1, Criteria1:="<>" & Team & "*"
                Range("A3:A" & LastRow).SpecialCells(xlCellTypeVisible).Delete
                ws.AutoFilterMode = False
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
Thanks Joe, I already did that successfully, but I thought that was not an elegant solution
Elegance is often overrated! ;)

Sure, there may be more elegant or efficient ways of doing it, but for just 4 columns, I don't think you will see much of a performance difference, especially if you disable screen updating until the end.

That being said, I often like to learn new and more elegant ways of doing things, so I can appreciate where you are coming from.
Check out fluff's solution for a more elegant way.
 

opislak

New Member
Joined
Feb 28, 2017
Messages
45

ADVERTISEMENT

How about
VBA Code:
Sub copy()

Dim ws As Worksheet
Dim lastRow, lastCol As Long
Dim Ary As Variant
    Set ws = Sheets("Sheet1")

    ws.Select
    lastRow = Range("A:A").Find("*", , , , xlByRows, xlPrevious, , , False).Row
    lastCol = Range("1:1").Find("*", , , , xlByColumns, xlPrevious, , , False).Column
   
    Ary = Range("B1:E" & lastRow).Value2
    Ary = Application.Index(Ary, Evaluate("row(1:" & lastRow & ")"), Array(2, 1, 3, 4))
   
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Selection"
   Cells(1, 1).Resize(lastRow, 4).Value = Ary
End Sub
Dear Fluff,
This works perfect. Many thanks !!
But for my information (I like to learn and I am not an IT'er): why do you put "value2" after the line? What does that do?
Thanks in advance.
Patrick.
VBA Code:
Ary = Range("B1:E" & lastRow).Value2
 

opislak

New Member
Joined
Feb 28, 2017
Messages
45
Elegance is often overrated! ;)

Sure, there may be more elegant or efficient ways of doing it, but for just 4 columns, I don't think you will see much of a performance difference, especially if you disable screen updating until the end.

That being said, I often like to learn new and more elegant ways of doing things, so I can appreciate where you are coming from.
Check out fluff's solution for a more elegant way.
Thank you Joe. You are right with your first statement :) I like to learn, even at my age (I'm pushing 60 and never learned ICT, so I'm just trying).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Value2 takes the underlying value of the cells, & ignores formats. So if you had today's date in one of the cells it will get 44287 rather than 01/04/2021 (UK dates)
This can speed up the loading of the array.
 

opislak

New Member
Joined
Feb 28, 2017
Messages
45
Value2 takes the underlying value of the cells, & ignores formats. So if you had today's date in one of the cells it will get 44287 rather than 01/04/2021 (UK dates)
This can speed up the loading of the array.
Never too old to learn. Thank you, much obliged.
Patrick.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,381
Messages
5,635,924
Members
416,887
Latest member
SheriE

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
Top