Macro that Creates New Worksheets based on Data from a List [Closed]

I've been researching and attempting to create a macro for the past few hours. I have a workbook with 2 worksheets. 1st worksheet contains a table that contains 5 columns: Last Name, First Name, DoB, Something, Something. 2nd worksheet is a template that I'm making copies of and populating 4 different fields on using the data from each row in worksheet 1.

Currently my script will copy the names from B2 through B200 and create/name new worksheets based on the name in the B column. It then creates a hyperlink to the new worksheet from worksheet 1.

What my script doesn't do: I can't get it to autopopulate any of the data into the corresponding cells on the new worksheets from worksheet 1. I also can't figure out a way to combine the names from the B and C columns and use that value (Last,First) to name the worksheet.

Sub CreateAndNameWorksheets()
Dim c As Range
Dim d As Range
Dim e As Range
Dim f As Range

Application.ScreenUpdating = False
For Each c In Sheets("1Names").Range("B2:B200")
Sheets("2MARS").Copy After:=Sheets(Sheets.Count)
With c
ActiveSheet.Paste Destination:=ActiveSheet.Range("F28")
ActiveSheet.Name = .Value
.Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
"'" & .Text & "'!A1", TextToDisplay:=.Text
End With
Next c
For Each d In Sheets("1Names").Range("C2:C200")
With d
ActiveSheet.Paste Destination:=ActiveSheet.Range("F27")
End With
Next d
For Each e In Sheets("1Names").Range("D2:D200")
With e
ActiveSheet.Paste Destination:=ActiveSheet.Range("S1")
End With
Next e
For Each f In Sheets("1Names").Range("E2:E200")
With f
ActiveSheet.Paste Destination:=ActiveSheet.Range("FAB26")
End With
Next f
Application.ScreenUpdating = True
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!