copy columns from sheet to another by array

alhagag · Jun 17th 2020

hello

i have this code works copy specific columns from sheet to another but is very slow i would do that by array without loops

Code

Sub copycol()
Dim lastrow, erow As Long
lastrow = sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
sheet1.Cells(i, 2).Copy

erow = sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 1)
sheet1.Cells(i, 4).Copy
sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 2)

Next
End Sub

Display More

**royUK** · Jun 17th 2020

Replace your code with this

Code

Sub copycol()


sheet1.Range("A1").CurrentRegion.Copy sheet2.Cells(1, 1)
sheet2.Range("A:A,C:C").EntireColumn.Delete
sheet2.Columns.AutoFit
End Sub

alhagag · Jun 17th 2020

wow ! amazing code despite it is very short but is very quick well done Royuk

**royUK** · Jun 18th 2020

Pleased to help

HopFrog · Jun 18th 2020

Are you copying individual cells from a specified row/column to the end of the data set on the second sheet, or copying entire columns?

Are you copying the formula or just pasting the values? is formatting important?

For copy/pasting values, you could save the call to the UI by writing the value from one cell directly to another. For example:
Worksheets("sheet2").Cells(erow, 2).value = sheet1.cells(i,4).value

**royUK** · Jun 19th 2020

If you look at the sample data then you can see there are no formulas.

It's much faster to copy all the data then remove unwanted columns than copying individual cells

copy columns from sheet to another by array

Participate now!

Share