Activesheet.Rows.count will be about a million in xl2007.

Tom Ogilvy wrote:

Dim regTopRow as Long
Dim regRows as Long
'OptionVal is a variable that stores the worksheet from another part of the
code
'OptoinVal does work

Worksheets(OptionVal).Activate

regTopRow = ActiveSheet.UsedRange(1).Row
regRows = ActiveSheet.UsedRange.Rows.Count

Activesheet.Row is your culprit. That isn't a valid command

Activesheet.Rows.count always will return 65536 in Excel 97 to 2007, 16384
in earlier versions.

Activesheet.UsedRange.Rows.Count will give you the number of rows that Excel
considers to be in use. If that isn't what you want, then post back.

--
Regards,
Tom Ogilvy

"Gabriel" wrote in message
...
Bob,
Thanks for the reply, but the code you wrote efectively does the same
thing
as mine. It still generates the run-time error.
--
Thanks,
Gabriel


"Bob Phillips" wrote:

With OptionVal
regTopRow = .Row
regRows = .Rows.Count
End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gabriel" wrote in message
...
Can someone tell me why the following code generates the following
error:

Dim regTopRow
Dim regRows
'OptionVal is a variable that stores the worksheet from another part of
the
code
'OptoinVal does work

Worksheets(OptionVal).Activate

regTopRow = ActiveSheet.Row
regRows = ActiveSheet.Rows.Count

The error that is returned is a:
run-time error '438'
Object Doesn't support this Property or Method.

I am assuming that I don't have the correct 'reference' set.

Basically I am just trying to activate the indicated sheet, move to
the
last record then move back up 3 rows and insert a new row... the last
row
is
a "totals line" that keeps a running total and I want the new records
inserted above that record.

Thanks for any help.
--
Thanks,
Gabriel




--

Dave Peterson