Copying Columns from 1 worksheet to another

[KW]

Does anybody know any better code than the following to copy certain columns of data from one worksheet to another, without the need to repeat the code for every column seperately?

Sub copyColumns()

Worksheets("Sheet1").Activate
Range("A1", Range("A1").End(xlDown)).Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlValues

Range("C1", Range("C1").End(xlDown)).Copy
Worksheets("Sheet2").Range("B1").PasteSpecial Paste:=xlValues

Range("G1", Range("G1").End(xlDown)).Copy
Worksheets("Sheet2").Range("C1").PasteSpecial Paste:=xlValues

End Sub

 

[Norie]

How about this?

Sub copyColumns() 
Dim LastRow As Long

      LastRow=Worksheets("Sheet1").Range("A65536").End(xlUp).Row
      Worksheets("Sheet1").Range("A1:A" & LastRow).Copy
      Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlValues 

      LastRow=Worksheets("Sheet1").Range("C65536").End(xlUp).Row
      Worksheets("Sheet1").Range("C1:C" & LastRow).Copy
      Worksheets("Sheet2").Range("B1").PasteSpecial Paste:=xlValues 

      LastRow=Worksheets("Sheet1").Range("G65536").End(xlUp).Row
      Worksheets("Sheet1").Range("G1:G" & LastRow).Copy
      Worksheets("Sheet2").Range("C1").PasteSpecial Paste:=xlValues 
   
End Sub

 

[Nimrod]

Public Sub CopyColumnsV2()
Dim BotRow As Integer
Worksheets("Sheet1").Activate
With Sheets("Sheet2")
For Each Col In Array(1, 3, 7)
TargCol = TargCol + 1
BotRow = Cells(65536, Col).End(xlUp).Row
.Range(.Cells(1, TargCol), .Cells(BotRow, TargCol)).Value = _
Range(Cells(1, Col), Cells(BotRow, Col)).Value
Next Col
End With
End Sub

 

[Nimrod]

Public Sub CopyColumnsV3()
Worksheets("Sheet1").Activate
With Sheets("Sheet2")
For Each Col In Array(1, 3, 7)
TargCol = TargCol + 1
.Range(.Cells(1, TargCol), .Cells(Cells(65536, Col).End(xlUp).Row, TargCol)).Value = _
Range(Cells(1, Col), Cells(Cells(65536, Col).End(xlUp).Row, Col)).Value
Next Col
End With
End Sub

Note: to change the source columns used just add or change Array(1, 3, 7)
example ... if the source was cols A,B,F,G then change to Array(1,2,6,7) :wink:

 

[KW]

Thanks Nimrod, v3 is just the ticket. many thanks for your help.

KW

 

[Nimrod]

My pleasure