Johnny2combs
08-21-2008, 08:32 AM
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
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