won't select cell on another sheet

[smit127]

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??

[Bernie Deitrick]

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" <smit127.1uoimb_1125576337.354@excelforum-nospam.com> wrote in message
news:smit127.1uoimb_1125576337.354@excelforum-nospam.com...
>
> 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
>

[Tom Ogilvy]

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" <smit127.1uoimb_1125576337.354@excelforum-nospam.com> wrote in
message news:smit127.1uoimb_1125576337.354@excelforum-nospam.com...
>
> 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
>

[smit127]

Thanks guys. Got it working.