Question #2!

ThisWorkbook.Sheets("Project Template").Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet

Naming = CallLaunched.Value & " - " & ProjectLead.Value

ws.Name = Naming

It is possible that 'Naming' could be the same as a previously generated sheet and therefore throws up an error. What would be the best way to handle this?

Would it be best to just use an Error Goto? If so would the handle only apply to the Naming line?

Again, any help/explanation would be appreciated.

Thanks in advance,

Suzie

CodePudding user response：

Copy a Worksheet (New Name Issue)

• Make sure that Naming contains a valid sheet name.

Option Explicit

Sub CopyWorksheet()
    
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' Source worksheet
    Dim sws As Worksheet: Set sws = wb.Worksheets("Project Template")
    
    ' Destination Worksheet Name
    Dim Naming As String: Naming = "Launched Project"
    'Naming = CallLaunched.Value & " - " & ProjectLead.Value
    
    ' Attempt to create a reference to the Destination Worksheet.
    On Error Resume Next
    Dim dws As Worksheet: Set dws = wb.Worksheets(Naming)
    On Error GoTo 0
    
    ' If it exists, delete it.
    If Not dws Is Nothing Then ' destination exists
        Application.DisplayAlerts = False ' delete without confirmation
        dws.Delete
        Application.DisplayAlerts = True
    Else ' destination doesn't exist
    End If
    
    ' Only now create a copy,...
    sws.Copy After:=wb.Sheets(wb.Sheets.Count)
    ' ... a reference,...
    Set dws = ActiveSheet
    ' and rename it.
    dws.Name = Naming
    
End Sub

CodePudding user response：

When manually copying a sheet the new name is numbered to make it unique, for example "Sheet1 (2)". If you want to allow copying a sheet multiple times you could do similar -

On Error Resume Next
ws.Name = Naming
If Err.Number Then
    n = 1
    Do
        Err.Clear
        n = n   1
        ws.Name = Naming & "  (" & n & ")"
    Loop Until Err.Number = 0 ' Or n <= 50  ' limit how many times can copy?
End If
On error Goto 0

You might need more handling to ensure the new name length does not exceed 31