locked
Object Variable or With Block Variable Not Set (Error 91) RRS feed

  • Question

  • Dear Sir:

    I made the following program for excel:

    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
        
        On Error Resume Next
        
        Set Range1 = Application.Workbooks(MyPath).Sheets(1).Range(Range("A1"), Range("A1").SpecialCells(xlCellTypeLastCell))
        Set range2 = sht.Range("B1")
        
        Range1.Copy range2
        
        If Err.Number <> 0 Then
            MsgBox "Error " & Err & vbNewLine & Error(Err.Number)
        End If
        WB.Save
        WB.Close

    End Sub

    When I run this program, it shows "Object variable or with block variable not set (Error 91). When I debugged it, it highlighted the following line:"Set Range1 = Application.Workbooks(MyPath).Sheets(1).Range(Range("A1"), Range("A1").SpecialCells(xlCellTypeLastCell))". Would you please help see where the problem is in my program and how to fix it?

    Regards,

    Shell Hu

    Thursday, November 10, 2016 2:06 AM

Answers

  • Hi Shell Hu,

    you did not open the file and trying to set the range from that.

    because of that you are getting an error.

    I correct and modify your code. I posted it below.

    Option Explicit
    
    
    Sub CreateNewWork()
         Dim WB, wb1 As Workbook
         Dim sht As Worksheet
         Dim MyPath As String
         Dim Range1 As Range
         Dim range2 As Range
    
         MyPath = "C:\Users\v-padee\Desktop\dt1.xlsx"
    
         Set WB = Workbooks.Add
         With WB
             .SaveAs Filename:="Summary1"
             .Sheets(1).Name = "total"
         End With
         Set sht = WB.Worksheets.Add
    
         With sht
             .Name = "Shell"
         End With
         
         On Error Resume Next
         Set wb1 = Application.Workbooks.Open(MyPath)
         Set Range1 = wb1.Sheets(1).Range(Range("A1"), Range("A1").SpecialCells(xlCellTypeLastCell))
         Set range2 = sht.Range("B1")
         
         Range1.Copy range2
         
         If Err.Number <> 0 Then
             MsgBox "Error " & Err & vbNewLine & Error(Err.Number)
         End If
         WB.Save
         WB.Close
    
     End Sub
    
    

    now it is working without any error. and copy the range data to new file.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 11, 2016 2:34 AM