1

I am trying to copy ranges of data from various worksheets into one worksheet. I have written this code:

Sub sub1()
For i = 1 To 3
Sheets(i).Select
Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Select 'line4
Selection.Copy
Sheets(6).Select
Cells(1, i).Select
Selection.PasteSpecial xlPasteValues
Next i
End sub

I get a Run-time error '1004' Select method of Range class failed on the line 4. How can it be fixed?

Community
  • 1
  • 1
konstantintre
  • 37
  • 1
  • 1
  • 5
  • 1
    1) [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) 2) [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) – Dmitry Pavliv Jun 21 '14 at 18:41

1 Answers1

10

You don't Select a sheet you Activate it. But actually you shouldn't do either in most cases.

You can shorten your code to:

Sub sub1()
Dim i As Long

For i = 1 To 3
    With Sheets(i)
       .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Copy
    End With
    Sheets(6).Cells(1, i).PasteSpecial xlPasteValues
Next i
End Sub

Note that I also declared i. I recommend declaring all variables, and using Option Explicit to make sure you're using the variable you think you are in all cases.

EDIT: Simoco's edit is good: Here's what I came up with:

Sub sub1()
Dim i As Long
Dim wb As Excel.Workbook

Set wb = ActiveWorkbook
For i = 1 To 3
    With wb.Sheets(i)
        .Range("A1:A" & .Range("A1").End(xlDown).Row).Copy
        wb.Sheets(6).Cells(1, i).PasteSpecial xlPasteValues
    End With
Next i
End Sub

Note that I declared a Workbook variable and qualified to it. One more good practice for you!

Community
  • 1
  • 1
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115