VBA - Worksheet creation

[oggy3000]

Hey,
with the following code I am creating a new worksheet.

Private Sub cmdCreate_Click()
Dim intInlength, intOutlength, intMonth, intYear As Integer
intMonth = cboMonth.Value

Worksheets("Cost Temp").Copy After:=Sheets(Worksheets.Count)

With ActiveSheet
  .Name = cboMonth.Value & "-" & cboYear.Value
  .Range("C1") = cboMonth.Value
  .Range("D1") = cboYear.Value
End With

End Sub

However, I want it to overwrite existing sheets with the same name. How can I do that?

Jan

 

[chuckchuckit]

Perhaps that would be deleting old and renaming new?

1. Name new sheet with "temp' name
2. Delete "old" sheet
3. Rename "temp" name sheet with "old" sheet name

 

[Norie]

Jan

Only lightly tested:

Option Explicit
 
Private Sub cmdCreate_Click()
Dim wsNew As Worksheet
Dim strNewName As String
Dim intInlength As Long, intOutlength As Long, intMonth As Long, intYear As Long

    intMonth = cboMonth.Value
    Worksheets("Cost Temp").Copy After:=Sheets(Worksheets.Count)

    strNewName = cboMonth.Value & "-" & cboYear.Value

    Set wsNew = ActiveSheet
 
    Application.DisplayAlerts = False

    On Error Resume Next
    Worksheets(strNewName).Delete
    On Error GoTo 0

    Application.DisplayAlerts = True

    With wsNew
        .Name = strNewName
        .Range("C1") = cboMonth.Value
        .Range("D1") = cboYear.Value
    End With
 
End Sub