Bernie is exactly right.

However, to answer you problem, I assume you are running this from a sheet
module rather than a general module. In a sheet module, your unqualified
Cells refers to the sheet containing the code.

Either move the code to a general module and call it from your
control/button or qualify Cells

If ActiveCell.Row = 65536 And ActiveSheet.Index < 9 Then
g3000SheetNumber = ActiveSheet.Index + 1
set sh = Sheets(g3000SheetNumber )
sh.Activate
sh.Cells(1, 1).Select

--
Regards,
Tom Ogilvy


"smit127" wrote in
message ...

I have a large amount of data (over 500,000 rows) so I have spread this
data over multiple sheets. The code I have written has to find records
that match certain criteria and does this by looping through cells.
However, when it reaches the bottom of the first sheet (row 65536) I
need it to start on the next sheet but I keep getting a Run time error.
Here is the bit of code....

If ActiveCell.Row = 65536 And ActiveSheet.Index < 9 Then
g3000SheetNumber = ActiveSheet.Index + 1
Sheets(g3000SheetNumber ).Activate
Cells(1, 1).Select

It manages to select the new sheet but when I try to select the first
cell of the sheet it throws an error. Can anyone help please??


--
smit127
------------------------------------------------------------------------
smit127's Profile:
http://www.excelforum.com/member.php...o&userid=26870
View this thread: http://www.excelforum.com/showthread...hreadid=401094