范围类的Excel VBA选择方法失败 [英] Excel VBA select method of range class failed

问题描述

我正在尝试将各种工作表中的数据范围复制到一个工作表中。我写了这段代码：

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

我得到一个运行时错误'1004'选择方法的Range类失败在行4.如何修复？

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.

您可以将代码缩短为：

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

请注意，我还声明了 i 。我建议声明所有变量，并使用选项显式确保您使用你认为你在所有情况下的变量。

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.

编辑：Simoco的编辑很好：这是我想出的：

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

请注意，我声明了一个Workbook变量并具有资格。一个更好的做法给你！

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

这篇关于范围类的Excel VBA选择方法失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！