vba 从变量引用另一个工作簿中的工作表时下标超出范围

Subscript out of range when referencing a worksheet in another workbook from a variable

提问by Tanzir Rahman

See picture: http://s12.postimg.org/ov8djtuh9/Capture.jpg

看图：http: //s12.postimg.org/ov8djtuh9/Capture.jpg

Context:Trying to activate a sheet (variable: cSheet) in another workbook and paste data there from copied data from a different workbook. I'm getting a subscript out of range error whenever I try to activate directly using the variable (i.e. Worksheets(Name).Activate) or try to define a worksheet using the variable and then activate it. I've also tried other coding styles, using "With Worksheet" etc. and my code was a lot longer but I started over because every time I fix something, something else goes wrong. So, sticking to the basics. Any help would be greatly appreciated.

上下文：尝试激活另一个工作簿中的工作表（变量：cSheet）并将数据粘贴到其他工作簿中复制的数据中。每当我尝试使用变量（即 Worksheets(Name).Activate）直接激活或尝试使用变量定义工作表然后激活它时，都会出现下标超出范围错误。我还尝试了其他编码风格，使用“With Worksheet”等，我的代码要长得多，但我重新开始，因为每次我修复某些东西时，都会出现其他问题。所以，坚持基础。任何帮助将不胜感激。

Sub GenSumRep()

Dim AutoSR As Workbook
Dim asrSheet As Worksheet
Dim tempWB As Workbook
Dim dataWB As Workbook
Dim SecName As String
Dim oldcell As String
Dim nsName As String
Dim cSheet As Worksheet

Set AutoSR = ActiveWorkbook
Set asrSheet = AutoSR.ActiveSheet

For a = 3 To 10

    SecName = asrSheet.Range("D" & a).Value

    If SecName <> "" Then

    Workbooks.Open Range("B" & a).Value
    Set tempWB = ActiveWorkbook
    'tempWB.Windows(1).Visible = False

    AutoSR.Activate

    Workbooks.Open Range("C" & a).Value
    Set dataWB = ActiveWorkbook
    'dataWB.Windows(1).Visible = False

    AutoSR.Activate

        'Copy paste data
        For b = 24 To 29
        oldcell = Range("C" & b).Value
            If b = 24 Then
            nsName = Trim(SecName) & " Data"
            Set cSheet = tempWB.Sheets(nsName)
            Else
            nsName = asrSheet.Range("B" & b).Value
            Set cSheet = tempWB.Sheets(nsName)
            End If

        'Copy
        dataWB.Activate
        Range(oldcell).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy

        'Paste
        tempWB.Activate
        cSheet.Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False

        b = b + 1
        Next b

    End If

a = a + 1

Next a

End Sub

回答by Byron Wall

You only get that error for one reason: the name your provided does not exist in the collection!

您只会因为一个原因收到该错误：您提供的名称在集合中不存在！

There are a couple of likely reasons for this based on your code:

根据您的代码，有几个可能的原因：

• Your nsNamevariable contains hidden characters that make it different even though it appears correct.
• You are looking for the sheet in the wrong workbook.

• 您的nsName变量包含隐藏字符，即使它看起来是正确的，也会使其不同。
• 您正在错误的工作簿中查找工作表。

Based on your comments, it seems that you are looking in the wrong workbook. A good way to check out these subscript errors is to iterate the collection and print out the Namesincluded therein.

根据您的评论，您似乎在查看错误的工作簿。检查这些下标错误的一个好方法是迭代集合并打印出其中Names包含的内容。

Dim sht as Worksheet    
For Each sht In tempWB.Sheets
    Debug.Print sht.Name
Next sht

In general, it is desirable to get rid of calls to Selectand Activateso that you are not relying on the interface in order to get objects. See this post about avoiding Selectand Activatefor more info.

通常，最好摆脱对Selectand的调用，Activate这样您就不必依赖接口来获取对象。请参阅有关避免Select和Activate更多信息的帖子。

One idea applied to your code is to assign the Workbooks directly without ActiveWorkbook:

应用于您的代码的一个想法是直接分配工作簿，而无需ActiveWorkbook：

Set tempWB = Workbooks.Open(asrSheet.Range("B" & a).Value)
Set dataWB = Workbooks.Open(asrSheet.Range("C" & a).Value)

instead of:

代替：

    Workbooks.Open Range("B" & a).Value
    Set tempWB = ActiveWorkbook
    'tempWB.Windows(1).Visible = False

    AutoSR.Activate

    Workbooks.Open Range("C" & a).Value
    Set dataWB = ActiveWorkbook
    'dataWB.Windows(1).Visible = False