Create multiple worksheets and automatically enter data from list

[LeilaniMerle]

Hi,

I have a list in my Excel spreadsheet, and a template that I use to generate student Names, Surnames, Student Numbers and I.D.S. How would I get the relevant information into the correct cells and automatically create the worksheets, and possibly rename the worksheet to the Student Number.

P.S. I have blanked out I.D. Numbers and the names are fictitious for the purpose of confidentiality.

Thank you for any assistance..
Kind regards

Merle Tait

 

[mumps]

What is the name of the worksheet containing your data and what is the name of the sheet containing the template? What are the cells addresses of the student number, ID number, first name and last name in the template?

 

[Fluff]

How about something like

Sub LeilaniMerle()
   Dim Cl As Range
   
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets("Template").Copy , Sheets(Sheets.Count)
            ActiveSheet.Name = Cl.Value
            Range("C3") = Cl.Value
            Range("C5") = Cl.Offset(, 1).Value
            Range("F5") = Cl.Offset(, 2).Value
            Range("F4") = Cl.Offset(, 3).Value
         End If
      Next Cl
   End With
End Sub

Change sheet names & ranges to suit

 

[LeilaniMerle]

What is the name of the worksheet containing your data and what is the name of the sheet containing the template? What are the cells addresses of the student number, ID number, first name and last name in the template?

Hi Mumps, thanks for response. 2020-NSP-0003 Is the template which needs to be recreated. Student Number C3 , First Name C5, ID Number F4, Last Name F5.

 

[LeilaniMerle]

Hi Mumps, thanks for response. 2020-NSP-0003 Is the template which needs to be recreated. Student Number C3 , First Name C5, ID Number F4, Last Name F5.

How about something like

Sub LeilaniMerle()
   Dim Cl As Range
  
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets("Template").Copy , Sheets(Sheets.Count)
            ActiveSheet.Name = Cl.Value
            Range("C3") = Cl.Value
            Range("C5") = Cl.Offset(, 1).Value
            Range("F5") = Cl.Offset(, 2).Value
            Range("F4") = Cl.Offset(, 3).Value
         End If
      Next Cl
   End With
End Sub

Change sheet names & ranges to suit

Will give it a try, thank you Fluff.

 

[Fluff]

Ok, let us know how it goes

 

[LeilaniMerle]

Greetings,

I would like to try this question again please if I may? I was not sure how the VBA script works.

I have a list of Students consisting as follows: Student Number etc. please see image Student List
Then I have a template. Please see image Template.
I have two scenarios I need to complete here.

1. Create multiple worksheets from the template and naming them using the Student Number. e.g. 2020-NSP-0003
2. On the new worksheets I need to have : Student Number in cell C3 / First Name in cell C5 / Surname in Cell F5 / ID Number in Cell F4
in sequence from the Student List.

Please assist. I have thousands of transcript to do

 

[Fluff]

That's exactly what the macro does

 

[LeilaniMerle]

So i run that code for each sheet? Sorry I am script ignorant completely.

 

[Fluff]

No it will create a new sheet for every value in col A. Why not try it