View Full Version : Solved: VBA Subscript out of range errors on sheet code

02-28-2010, 07:33 PM
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
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.

02-28-2010, 08:36 PM
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.

02-28-2010, 08:52 PM
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


02-28-2010, 09:09 PM
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.
Sub checkSheet()
MsgBox ActiveSheet.Name = "sheet2"
End Sub