Hi, I have a spreadsheet that contains two sheets the first (called "Index") is to have a list of data as entered by the user. The second sheet (called "Blank") is a blank template sheet.

What i want the macro to do is upon entering all the data i want it to create a sheet for every piece of data, i want the sheets to be in the order the data is entered and i want the sheets to be the same name as the data.

The code i have written is:

Sub CreateSheets()

Dim row As Integer

Application.ScreenUpdating = False

Sheets("Index").Select

row = 4

Do Until Cells(row, 2) = "" 'to go down data collumn untill it reaches bottom

Sheets("Blank").Copy After:=Worksheets(Worksheets.Count) 'copy blank sheet and move to end

ActiveSheet.name = Sheet1.Cells(row, 2) 'rename to same value as data

row = row + 1

Sheets("Index").Select

Loop

Application.ScreenUpdating = True

End Sub

This works fine upto about 50 sheets but any more than that and i get a 'Run time error 1004, Copy method of worksheet class failed'.

The:

Sheets("Blank").Copy After:=Worksheets(Worksheets.Count)

is the line which is highlighted as the problem.

Can anyone spot what i'm doing wrong?

Thanks, Johnny