范围类的 Excel VBA 选择方法失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24344607/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA select method of range class failed
提问by user3757861
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?
我在第 4 行收到运行时错误“1004”Range 类的 Select 方法失败。如何修复？
回答by Doug Glancy
You don't Selecta sheet you Activateit. But actually you shouldn't do eitherin most cases.
你不Select表你Activate吧。但实际上在大多数情况下你不应该这样做。
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 Explicitto make sure you're using the variable you think you are in all cases.
请注意，我还声明了i. 我建议声明所有变量，并使用Option Explicit来确保您在所有情况下都使用您认为自己使用的变量。
EDIT: Simoco's edit is good: Here's what I came up with:
编辑：Simoco 的编辑很好：这是我想出的：
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!
请注意，我声明了一个 Workbook 变量并对其进行了限定。给你的又一个好习惯！
