I have a large amount of data (over 500,000 rows) so I have spread thi
data over multiple sheets. The code I have written has to find record
that match certain criteria and does this by looping through cells
However, when it reaches the bottom of the first sheet (row 65536)
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 firs
cell of the sheet it throws an error. Can anyone help please?

--
smit12
-----------------------------------------------------------------------
smit127's Profile: http://www.excelforum.com/member.php...fo&userid=2687
View this thread: http://www.excelforum.com/showthread.php?threadid=40109

smit,

You would be much better off not activating or selecting anything. You are much better off using
native Excel procedures such as find - post the rest of your code or a description of what your are
doing for suggestions.

HTH,
Bernie
MS Excel MVP


"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

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

Thanks guys. Got it working.


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