Generate multiple worksheets

[jb]

Hi experts,
I have one worksheet named title in which 10 cells (C1 to c10) are given to teachers to fill titles of subjects. Depending upon requirement, teacher may use any number of cells from c1 to c10 to enter titles.
Also, teacher is given another worksheet called data which contains format of marksheet. Teacher will enter roll numbers and attendance marks in Marksheet worksheet.

Now, user is given a button called generate. when user clicks on generate button given in title sheet, multiple worksheets should be generated with same format as Marksheet worksheet.
worksheets title will be taken from title worksheet cells c1 to c10.

After generating multiple sheets, user must remain in title sheet only.


Help required.

 

[Smallman]

Hi jb

This code will do what you require. I will post a file to show workings.

Option Explicit
 
Sub Gener8()
Dim i As Integer
 
For i = 1 To Sheet1.Range("C" & Rows.Count).End(xlUp).Row
    Sheet2.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheet1.Range("C" & i)
Next i
 
End Sub

Take care

Smallman

 

[jb]

Thanks smallman.
It worked. Can you tell me where should i post above code in my excel file, if I want to use this logic?
And after generating multiple sheets, I want to remain in title sheet. here in your logic, cursor goes to last sheet.

 

[Smallman]

Hi jb

Depends what version of XL you are using. Hit ALT F11 and you will be taken to XLs back end. Now go Insert Module.

Paste my code in there. Now check a couple of things. To the left in the section titled VBA Project you will see some Excel Objects

Sheet1(Title)
Sheet2(Data)

etc. What you need to ensure is that the sheet reference you use in the code matches the sheet reference numbers. Sheet1, Sheet2 etc. This will ensure you are protected against changes to the worksheet name.

Also use the following code to give the appearance of staying on the same sheet.

Option Explicit
Sub Gener8()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To Sheet1.Range("C" & Rows.Count).End(xlUp).Row
    Sheet2.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheet1.Range("C" & i)
Next i
Sheet1.Activate
Application.ScreenUpdating = True
End Sub

Take care

Smallman