Modify macro to override if run twice and thrice

Chaturvedi, Santosh 590 Reputation points
2026-10-05T14:31:23.4433333+00:00

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

  1. it should override the existing duplicated sheets and update the liste present in F5 till end again.
  2. It should not duplicate Master Template_Collectives and making two files like Master Template_Collectives & Master Template_Collectives(2)

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

Microsoft 365 and Office | Excel | For home | Windows
0 comments No comments

1 answer

Sort by: Most helpful
  1. Barry Schwarz 6,191 Reputation points
    2026-10-05T21:36:47.3933333+00:00

    Inside your FOR loop after assigning a value to newName:

    • Check if WorkSheets(newName) exists.
    • If it does, delete it.
    • In either case, you can now create the copy without fear of duplication.

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.