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
Improve this question
3
  • 1
    Start with a search, this has been addressed many times. Commented Jun 19, 2013 at 14:15
  • What do you mean how avoiding selecting the sheet itself? Commented Jun 19, 2013 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. Commented Jun 19, 2013 at 14:33

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

Comments

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
Improve this answer

2 Comments

This gives me a Run Time Error 1004
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