0

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
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Start with a search, this has been addressed many times. – user2140261 Jun 19 '13 at 14:15
  • What do you mean how avoiding selecting the sheet itself? – ashareef Jun 19 '13 at 14:17
  • I did search and wasn't able to find my particular issue with having to jump back from Sheet 1 to Sheet 2. Perhaps it was mentioned within them, but not explicitly. –  Jun 19 '13 at 14:33

3 Answers3

5
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
Abe Gold
  • 2,307
  • 17
  • 29
1
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
user2140261
  • 7,855
  • 7
  • 32
  • 45
1

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
artis_meditari
  • 68
  • 3
  • This gives me a Run Time Error 1004 – user2140261 Jun 19 '13 at 16:13
  • This answer is exactly, what you need to do. Using "copy", "paste" and "select" is evil in most cases. You can try to add a "value" to the end, so that it's: `Sheets("Sheet2").Columns("B:B").Value = Sheets("Sheet1").Columns("X:X").Value` – MiVoth Jun 21 '13 at 07:28