Run time err 9

hushell

你好：
我做了下面的程序：
Sub CreateNewWork()
    Dim WB As Workbook
    Dim sht As Worksheet
    Dim MyPath As String
    Dim Range1 As Range
    Dim range2 As Range
   
    MyPath = "C:\Users\hushe\Desktop\PG06\PG06-0924-01_2016-09-24_00-01-08.csv"
   
    Set WB = Workbooks.Add
    With WB
        .SaveAs Filename:="Summary"
        .Sheets(1).Name = "total"
    End With
    Set sht = WB.Worksheets.Add
   
   
    With sht
        .Name = "Shell"
    End With
    Range1 = Application.Workbooks(MyPath).Sheets(1).Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    range2 = sht.Range("B1")
    Range1.Copy range2
   
    WB.Save
    WB.Close
        
End Sub
运行时出现：Run time err 9 subscript out of range
debug时显示Range1 = Application.Workbooks(MyPath).Sheets(1).Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))有问题。请帮我看一下问题出现在什么地方。

谢谢！

liwen

Sub CreateNewWork()
    Dim WB As Workbook
    Dim sht As Worksheet
    Dim MyPath As String
    Dim Range1 As Range
    Dim range2 As Range
   
    MyPath = "F:\KM.xls"
   
    Set WB = Workbooks.Add
    With WB
        .SaveAs Filename:="Summary"
        .Sheets(1).Name = "total"
    End With
    Set sht = WB.Worksheets.Add
   
   
    With sht
        .Name = "Shell"
    End With
Set range2 = sht.Range("B1")
   With Application.Workbooks(MyPath).Sheets(1)
'   .Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell)).Copy sht.Range("B1")
Set Range1 = .Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell))
    End With
  Range1.Copy range2
   
    WB.Save
    WB.Close
        
End Sub

'加个Set 即可正常运行

roych

对于组件对象（例如，单元格，工作簿，工作表等等，甚至ADO记录集），必须使用set。
对于其他变量对象则不能使用set。例如，set xx="123"则报错