I've been trying to create a macros for creating worksheets. The code should do the following:
1) Create worksheets for ColumnB from Master sheet, using template from "Template" worksheet.
2) The range of ColumnB in Master sheet is variable, but this is my first try with excel-vba, and I don't know how to set a variable range.
3) Rename each worksheet as per the name in each cell in ColumnB
3.1) ColumnB has duplicate entries, but we need to create only 1 worksheet for duplicate cells. (deleting duplicates is not an option)
4)Hyperlink the worksheets to the cells in the Column B of Master sheet.
I am facing issues with the point 3.1 mentioned above. Below is the closest thing I found useful: Can we refine it to my requirements?
Sub CreateAndNameWorksheets()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Sheets("Master").Range("B5:B25000")
Sheets("Template").Copy After:=Sheets(Sheets.Count)
With c
ActiveSheet.Name = .Value
.Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
"'" & .Text & "'!A1", TextToDisplay:=.Text
End With
Next c
Application.ScreenUpdating = True
End Sub
Thank you!