Consulting

Results 1 to 4 of 4

Thread: Solved: VBA Subscript out of range errors on sheet code

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    347
    Location

    Solved: VBA Subscript out of range errors on sheet code

    I'm merging multiple sheets into one and this code keeps giving me a
    Run-time error '9':
    Subscript out of range
    at this point.
     
    Sheets(i).Range("B1").Copy Destination:=Sheets("Masterlist").[A65536].End(xlUp).Offset(1, 0)
    Changing it to this does not help;
     
    Sheets(i).Range("B1").Copy Destination:=Sheets("Masterlist").Range("A65536").End(xlUp).Offset(1, 0)
    Here's the rest of the code, run from a Excel UserForm:

     
    Dim i As Integer
    Dim LastRow As Long
    Application.ScreenUpdating = False
     
    '   Start loop, loop from 3 to the count of how many worksheets
    For i = 3 To ThisWorkbook.Worksheets.Count
                Sheets(i).Activate
                If ActiveSheet.Range("B3") = " " Then Exit Sub
                If ActiveSheet.Name = "New person" Then Exit Sub
                LastRow = Range("A65536").End(xlUp).Row
                
                
            Sheets(i).Range("B1").Copy Destination:=Sheets("Masterlist").[A65536].End(xlUp).Offset(1, 0)
                    
            Range("A3", Range("A65536").End(xlUp)).Resize(LastRow, 26).Copy Destination:=Sheets("Masterlist").[A65536].End(xlUp).Offset(1, 0)
    Next i
    Any ideas why I keep getting this error and how to correct it.

  2. #2
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    Make sure the spelling and spacing of the Masterlist is the same as the sheet in your workbook. Something like an extra space at the end of the tab name.

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    347
    Location

    VBA Subscript out of range errors on sheet code

    mbarron :

    I spent so much time and it was in fact a simple space after Masterlist that caused the error.

    I cant believe that there's not some facility to help with this

    Thanks.

  4. #4
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    Debugging is so much "fun" isn't it? One annoyance is that if you are looping through sheets and checking for a specific sheet name, the case is sensitive, but if you are activating the sheet, the case is not.

    For example this code would select Sheet2 in a workbook yet result in a False response.
    [vba]Sub checkSheet()
    Sheets("sheet2").Activate
    MsgBox ActiveSheet.Name = "sheet2"
    End Sub
    [/vba]

Posting Permissions

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