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.

Code:

---


Sheets(i).Range("B1").Copy Destination:=Sheets("Masterlist").[A65536].End(xlUp).Offset(1, 0)

---

Changing it to this does not help;

Code:

---


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:

Code:

---


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.

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.

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.

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]