Creating Worksheets

[kluitna]

I have a question about greating wprksheets in a workbook. I have writen a macro the does this and names them based on a list on a worksheet already in the workbook.
Here is my code:

Sub Make_Sheet()
Dim Itemnum As String
For i = 2 To 500
Itemnum = Worksheets(1).Cells(i, 2)
If Itemnum = blank Then End
Sheets(i).Select
Sheets(i).Copy After:=Sheets(i)
Sheets(i + 1).Select
Sheets(i + 1).Name = Itemmun
Next i
End Sub

The problem with it is the numbering of the spread sheets. How does excel number its sheets. I have 2 sheets in the workbook. Sheet 1 is my list sheet and sheet 2 is the sheet that is being copied. The problem comes in when the sheets are copies. I does not name them numerically and the then wreaks havoc with my loop for naming.

Does anyone out there know how or why excel would do this? An example of how it numbers the sheets is:
Sheet 21
Sheet 211
Sheet 2111 and so on..
Any help on this would be appreciated.

 

[hedrijw]

In your statement

Sheets(i + 1).Name = Itemmun

You have Itemnum spelled incorrectly. You defined the variable as Itemnum

Correct that and see what happens

Incidentally, there is an Excel 97 problem that arises from programmatic copying of worksheets.

XL97: Copy Method of Sheets Object Causes Invalid Page Fault

MS problem number is

Q177634
This message was edited by hedrijw on 2002-08-21 12:16

 

[kluitna]

Thanks what was part of the problem, but I am still having a problem with excels naming of the sheets. It actually shuts down my macro eventually.
sheet2
sheet211 and so on for each interation of the macro. Can't figure out why it does not just number sequentially.

Anyone out there know?

 

[hedrijw]

Please post some of your item numbers.

Are any of them duplicates?

Are you running Excel 97?

If so look up

XL97: Copy Method of Sheets Object Causes Invalid Page Fault

MS problem number is

Q177634