copy columns in different sequence to new sheet

opislak

Board Regular
Joined
Feb 28, 2017
Messages
68
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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
 
Upvote 0
Solution
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

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