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?
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!
i was crucial. Thank you, gentlemen.i. That's just a good idea. It was trying to Select a Sheet. Happy coding!