从变量引用另一个工作簿中的工作表时,下标超出范围 [英] Subscript out of range when referencing a worksheet in another workbook from a variable
问题描述
查看图片: http://s12.postimg.org/ov8djtuh9/Capture.jpg
上下文:尝试激活另一个工作簿中的工作表(变量:cSheet),并从另一个工作簿中复制的数据粘贴到该工作表中.每当我尝试使用变量直接激活(即Worksheets(Name).Activate)或尝试使用变量定义工作表然后激活它时,都会收到下标超出范围的错误.我还尝试了其他编码样式,例如使用"With Worksheet"等,我的代码虽然更长了,但我还是重新开始了,因为每次我修复某些内容时,都会出现其他问题.因此,坚持基本原则.任何帮助将不胜感激.
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.
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
推荐答案
您只会由于以下原因而收到该错误:您提供的名称在集合中不存在!
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:
- 您的
nsName
变量包含隐藏的字符,即使它看起来正确,也会使其与众不同. - 您要在错误的工作簿中查找工作表.
- Your
nsName
variable contains hidden characters that make it different even though it appears correct. - You are looking for the sheet in the wrong workbook.
根据您的评论,您似乎在寻找错误的工作簿.检查这些下标错误的一种好方法是对集合进行迭代并打印出其中包含的Names
.
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 Names
included therein.
Dim sht as Worksheet
For Each sht In tempWB.Sheets
Debug.Print sht.Name
Next sht
通常,希望摆脱对Select
和Activate
的调用,以便您不依赖于接口来获取对象.请参阅这篇关于避免Select
和Activate
的文章有关更多信息.
In general, it is desirable to get rid of calls to Select
and Activate
so that you are not relying on the interface in order to get objects. See this post about avoiding Select
and Activate
for more info.
适用于您代码的一个想法是直接分配工作簿,而无需ActiveWorkbook
:
One idea applied to your code is to assign the Workbooks directly without ActiveWorkbook
:
Set tempWB = Workbooks.Open(asrSheet.Range("B" & a).Value)
Set dataWB = Workbooks.Open(asrSheet.Range("C" & a).Value)
代替:
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
这篇关于从变量引用另一个工作簿中的工作表时,下标超出范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!