MB

total=Selection.Rows.Count is OK as is.

Try this..........

Sub countit()
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
total = Selection.Rows.Count
MsgBox total & " rows"
End Sub

Now add a bunch of rows to the worksheet.

Run again...........note number of rows.

Delete a bunch of rows.

Run again.....whoops! same number as above.

xlLastCell is not an accurate method of finding the "real" last cell.

See Debra Dalgleish's site for more on this.

http://www.contextures.on.ca/xlfaqApp.html#Unused


Gord Dibben MS Excel MVP

On Tue, 23 Jan 2007 13:57:02 -0800, MB wrote:

1. I thought that I was defining a variable that represented the total
number of rows in the worksheet (total = Selection.Rows.Count). How would I
accomplish that...do I have to Dim it?

2. Thanks for that tip about copying worksheets.

Thanks, Jim, for helping me on this one.

"Jim Thomlinson" wrote:

A couple of things I see....
1. Where do you define total. You are counting to some number called total
without defining it for us.

2. There is a known issue with copying worksheets...
http://support.microsoft.com/default...84&Product=xlw

--
HTH...

Jim Thomlinson


"MB" wrote:

I have a macro that is supposed to count how many rows are active in the
current worksheet and generate a new sheet for each record. For some reason,
it generates 55 sheets and stops. When I have 39 records, it generates too
many and when I have 307 records, it doesn't generate enough. What's up with
the 55?

Here's the code:

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
total = Selection.Rows.Count
Range("A1").Select
counter = 1
Do Until counter = total
counter = counter + 1
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Befo=Sheets(1)
Range("C8:D12,C15:D17,C20:D22,C25:D27,A3:H3").Sele ct
Range("A3").Activate
Selection.Replace What:="2", Replacement:=counter
Range("A1").Select
Loop


Please help...signed...Confused!!!