CODEWITHSUNDEEP
vbaexcel
user2191477
user2191477

Reputation:

Copying columns from one sheet to another

This Macro works fine, but I know it's not efficient and there has to be a better way to do it. How I can avoid having to .Select the sheet?

Sub ReportFormatter()

Columns("BS:BS").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Columns("X:X").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B1").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Columns("Y:Y").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C1").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("D1").Select
ActiveSheet.Paste

Upvotes: 0

Views: 854

Answers (3)

artis_meditari
artis_meditari

Reputation: 68

Or Simply:

Sub CopyCols()
    Sheets("Sheet2").Columns("B:B") = Sheets("Sheet1").Columns("X:X")
    Sheets("Sheet2").Columns("C:C") = Sheets("Sheet1").Columns("Y:Y")
    Sheets("Sheet2").Columns("D:D") = Sheets("Sheet1").Columns("H:H")
End Sub

Upvotes: 1

user2140261
user2140261

Reputation: 7993

Sub ReportFormatter()        
With Sheets("Sheet1")    
    .Columns("BS").Copy Sheets("Sheet2").Range("A1")   
    .Columns("X").Copy Sheets("Sheet2").Range("B1")   
    .Columns("Y").Copy Sheets("Sheet2").Range("C1")   
    .Columns("H").Copy Sheets("Sheet2").Range("D1")    
End With    
End Sub

Upvotes: 1

Abe Gold
Abe Gold

Reputation: 2347

Sub CopyCols()
    Sheets("Sheet1").Columns("X:X").Copy Destination:=Sheets("Sheet2").Range("B1")
    Sheets("Sheet1").Columns("Y:Y").Copy Destination:=Sheets("Sheet2").Range("C1")
    Sheets("Sheet1").Columns("H:H").Copy Destination:=Sheets("Sheet2").Range("D1")
End Sub

Upvotes: 5

Related Questions