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
Macro that Creates New Worksheets based on Data from a List