Consulting

Results 1 to 8 of 8

Thread: Getting script Error 9 Run time error while declaring variable of Sheet

  1. #1

    Getting script Error 9 Run time error while declaring variable of Sheet

    Sub Rajeev()
    Dim temp As New Workbook, wkb As Workbook
    Dim sh, sh1, sh2, sh3, sh4, sh5, sh6 As Worksheet, w_dpr As Worksheet, w_cc As Worksheet, w_c1 As Worksheet, w_c2 As Worksheet, w_c3 As Worksheet, w_c4 As Worksheet, w_c5 As Worksheet, w_dpr1 As Worksheet, w_dpr2 As Worksheet, w_c9 As Worksheet
    Dim MyFolder As String
    Dim MyFile As String
    Dim lRow As Long
    Dim lrow1 As Long
    
    
    Dim j As Integer, k As Integer, L As Integer
    
    
    Set wkb = ThisWorkbook
    Set w_dpr = wkb.Sheets("INSTALL(WIP)")
    Set w_dpr1 = wkb.Sheets("Disconnect(WIP)")
    Set w_dpr2 = wkb.Sheets("CCD")
    Set w_cc = wkb.Sheets("Test & Accept Queue")
    Set w_c1 = wkb.Sheets("Cancel Orders")
    Set w_c2 = wkb.Sheets("Onshore Reassignment") ' HERE THE ERROR COMING'
    'Set w_c3 = wkb.Sheets("Billed_RTP-Orders")
    'Set w_c4 = wkb.Sheets("CCD")
    Set w_c5 = wkb.Sheets("ClickIT Tickets")
    
    
    
    
    w_dpr.Range("A2:Z1000000").ClearContents
    w_dpr1.Range("A2:Z1000000").ClearContents
    w_dpr2.Range("A2:Z1000000").ClearContents
        
        
    MyFolder = wkb.Sheets("Overall Snapshot").Range("AQ1").Value
    MyFile = Dir(MyFolder & "\*.xls*")
    Do While MyFile <> ""
    Set temp = Workbooks.Open(Filename:=MyFolder & "" & MyFile)
    
    
        On Error Resume Next
        temp.Activate
        Set sh = ActiveWorkbook.Sheets("INSTALL(WIP)")
        
        If Err.Number <> 0 Then
            Err.Clear
            On Error GoTo 0
        Else
            temp.Activate
            sh.Activate
                    If ActiveSheet.FilterMode = True Then
                    ActiveSheet.ShowAllData
                    End If
    
    
                lRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    
    
                sh.Range("A2:Z" & lRow).Copy
    
    
                wkb.Activate
                w_dpr.Activate
          
                lrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                lrow1 = lrow1 + 1
    
    
                w_dpr.Range("A" & lrow1).Activate
                w_dpr.Range("A" & lrow1).PasteSpecial
    
    
                Application.CutCopyMode = False
    
    
        End If
    
    
        On Error Resume Next
            temp.Activate
            Set sh1 = ActiveWorkbook.Sheets("Test & Accept Queue")
            If Err.Number <> 0 Then
                'MsgBox "The sheet doesn't exist"
                Err.Clear
                On Error GoTo 0
            Else
                temp.Activate
                sh1.Activate
    
    
                    If ActiveSheet.FilterMode = True Then
                    ActiveSheet.ShowAllData
                    End If
    
    
    
    
                lRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                sh1.Range("A2:Z" & lRow).Copy
    
    
                wkb.Activate
                w_cc.Activate
    
    
                lrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                lrow1 = lrow1 + 1
    
    
                w_cc.Range("A" & lrow1).Activate
                w_cc.Range("A" & lrow1).PasteSpecial
    
    
    
    
                Application.CutCopyMode = False
              
        End If
            
            
        On Error Resume Next
            temp.Activate
            Set sh2 = ActiveWorkbook.Sheets("Cancel Orders")
            If Err.Number <> 0 Then
                'MsgBox "The sheet doesn't exist"
                Err.Clear
                On Error GoTo 0
            Else
                temp.Activate
                sh2.Activate
    
    
                    If ActiveSheet.FilterMode = True Then
                    ActiveSheet.ShowAllData
                    End If
    
    
    
    
                lRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                sh2.Range("A2:Z" & lRow).Copy
    
    
                wkb.Activate
                w_c1.Activate
    
    
                lrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                lrow1 = lrow1 + 1
    
    
                w_c1.Range("A" & lrow1).Activate
                w_c1.Range("A" & lrow1).PasteSpecial
    
    
    
    
                Application.CutCopyMode = False
              
        End If
        
        
        
        On Error Resume Next
        Set sh3 = ActiveWorkbook.Sheets("Disconnect(WIP)")
        
        If Err.Number <> 0 Then
            Err.Clear
            On Error GoTo 0
        Else
            temp.Activate
            sh3.Activate
                    If ActiveSheet.FilterMode = True Then
                    ActiveSheet.ShowAllData
                    End If
    
    
                lRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    
    
                sh3.Range("A2:Z" & lRow).Copy
    
    
                wkb.Activate
                w_dpr1.Activate
          
                lrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                lrow1 = lrow1 + 1
    
    
                w_dpr1.Range("A" & lrow1).Activate
                w_dpr1.Range("A" & lrow1).PasteSpecial
    
    
                Application.CutCopyMode = False
    
    
        End If
        
        
        On Error Resume Next
        temp.Activate
        Set sh4 = ActiveWorkbook.Sheets("CCD")
        
        If Err.Number <> 0 Then
            Err.Clear
            On Error GoTo 0
        Else
            temp.Activate
            sh4.Activate
                    If ActiveSheet.FilterMode = True Then
                    ActiveSheet.ShowAllData
                    End If
    
    
                lRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    
    
                sh4.Range("A2:Z" & lRow).Copy
    
    
                wkb.Activate
                w_dpr2.Activate
          
                lrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                lrow1 = lrow1 + 1
    
    
                w_dpr2.Range("A" & lrow1).Activate
                w_dpr2.Range("A" & lrow1).PasteSpecial
    
    
                Application.CutCopyMode = False
    
    
        End If
    
    
    temp.Close savechanges:=False
    MyFile = Dir
    Loop
    
    
    wkb.Activate
    
    
    w_dpr.Activate
    w_dpr.Range("A1:A1000").Select
    On Error Resume Next
    w_dpr.Columns("A1:A1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Rows("2:1000").RowHeight = 15
    On Error GoTo 0
    
    
    w_cc.Activate
    w_cc.Range("A1:A1000").Select
    On Error Resume Next
    w_cc.Columns("A1:A1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    Rows("2:1000").RowHeight = 15
    
    
    w_c1.Activate
    w_c1.Range("A1:A1000").Select
    On Error Resume Next
    w_c1.Columns("A1:A1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Rows("2:1000").RowHeight = 15
    On Error GoTo 0
    
    
    
    
    End Sub
    Last edited by Zack Barresse; 04-07-2020 at 10:07 AM. Reason: added CODE tags

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    I don't get ant errors on the declarations.

    However, using

    Dim sh, sh1, sh2, sh3, sh4, sh5, sh6 As Worksheet
    will only dim sh6 As WorkSheet, all the others will be Variants.

    You have to declare them individually, ie

    Dim sh as WorkSheet, sh1 as WorkSheet, etc...
    to declare them as WorkSheets.
    Semper in excretia sumus; solum profundum variat.

  3. #3

  4. #4

  5. #5

    Error is coming in the Line

    Error is coming in the 12 or 13th Line where it says - Set W_c2 = wkb.sheets("Onshore Reassignment")

    Thank for your support




    Quote Originally Posted by paulked View Post
    I don't get ant errors on the declarations.

    However, using

    Dim sh, sh1, sh2, sh3, sh4, sh5, sh6 As Worksheet
    will only dim sh6 As WorkSheet, all the others will be Variants.

    You have to declare them individually, ie

    Dim sh as WorkSheet, sh1 as WorkSheet, etc...
    to declare them as WorkSheets.

  6. #6
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    1. check sheet exists
    2. make sure spelling of the sheet and code are the same
    Semper in excretia sumus; solum profundum variat.

  7. #7

    Pls check the sheet with blank records.

    Quote Originally Posted by paulked View Post
    1. check sheet exists
    2. make sure spelling of the sheet and code are the same
    Pls find the attached sheet where it is showing while declaring the variable.
    Attached Files Attached Files

  8. #8
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    MAKE SURE THE SPELLING OF THE SHEET AND THE CODE ARE THE SAME!


    Sorry to shout, but there is a space after the name of the sheet... either add it to your code or delete it from the sheet name.
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •