Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I run the following macro, all the sheets duplicated and hyperlinked it is working perfect.
BUT
if in case if I run this macro again and if all the sheets are already present becuase of 1st run then
Please modify for below macro
Sub CopyAndListSheets()
Dim wsData As Worksheet
Dim wsTemplate As Worksheet
Dim wsNew As Worksheet
Dim copyAfter As Worksheet
Dim cell As Range
Dim outRow As Long
Dim newName As String
Set wsData = ThisWorkbook.Worksheets("Data File")
Set wsTemplate = ThisWorkbook.Worksheets("Master Template_Collectives")
Set copyAfter = wsTemplate
outRow = 4
For Each cell In wsData.Range("C4:C15")
If Trim(cell.Value) <> "" Then
newName = CStr(cell.Value)
wsTemplate.Copy After:=copyAfter
Set wsNew = ActiveSheet
wsNew.Name = newName
Set copyAfter = wsNew
wsData.Cells(outRow, "F").Value = newName
wsData.Cells(outRow, "F").HorizontalAlignment = xlLeft
wsData.Hyperlinks.Add _
Anchor:=wsData.Cells(outRow, "F"), _
Address:="", _
SubAddress:="'" & newName & "'!A1", _
TextToDisplay:=newName
outRow = outRow + 1
End If
Next cell
End Sub
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Inside your FOR loop after assigning a value to newName: