Edward

That's my fault. This line

Set Rng = Sheet1.Range("a2", Sheet1.Range("A2").End(xlDown))

needs to be changed to this

If Sheet1.Range("A65536").End(xlUp).Address = $A$1 Then
Set Rng = Sheet1.Range("a2")
Else
Set Rng = Sheet1.Range("a2", Sheet1.Range("A65536").End(xlUp))
End If

I think the problem is that you only have a value in A2 and nothing below
it. (Alternatively, you could have values in every cell in column A, but
it's not likely.) The loop creates a sheet for every value in column A,
except A1. The Range("A2").End(xlDown) statement is like selecting A2 and
hitting the End key, then the Down Arrow. If that takes you to the bottom
of the sheet (row 65536), then the loop will try to create 65,536 new
sheets. You don't have enough memory for that - no one does.

So changed it to start at the bottom and go up. This will ensure that if
there is only a value in A2 and no other values below it, then only one new
sheet will be created.

That was just a little sloppiness on my part. Try the new way and let me
know if it works.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com

"E. Kohl" wrote in message
...
Hello Dick,

Sorry if I'm being an idiot, but when I run the macro that you posted,
Excel simply starts an endless loop of creating new worksheets. It gets
to about 850 sheets and then I get the Run time error 1004 "Method 'Add'
of Object 'Sheets' failed." The debugger points me to this line:

'Create new sheet
Set wsh = ThisWorkbook.Worksheets.Add(, _
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

Any ideas? Thanks in advance. Edward




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!