Checking Workbook for Duplicate Worksheet name, and prompting for overwrite if it exists.

[JAlbers]

Greetings all

I have a report that I generate every week that uses a pivot table to aggregate data which I take and massage into the format for the report. Once formatted, the sheet (Data Staging) is copied to a new sheet and named Six Month Rpt mmddyy. That works fine until someone asks for a report that has already been generated (possibly some bad data was corrected or more likely someone couldn't find the printout on the network drive). At any rate, what I am wanting to do, is insert a test to see if this worksheet name exists in the workbook and either overwrite, or prompt for a new name. While either of these options are workable, it would be preferable to prompt for a new name.

I would certainly appreciate any help!

Sub SixMonthRpt()
Dim EDate As Date
Dim HDate As Date
Dim Msg As String
Dim wsSheet As Worksheet
Dim wsName As String
Dim lrowDS As Long
Dim lrowPT As Long


    EDate = Application.InputBox _
            (prompt:="Enter Report Date Here.", _
            Title:="Report Date Input Box", Type:=1)


    HDate = EDate - 180
    
    wsName = "Six Month Rpt " & Format(EDate, "mmddyy")
        
    Msg = "Do you wish to run the Six Month Production Summary Report?"
    Msg = Msg & vbNewLine & vbNewLine & vbNewLine
    Msg = Msg & "The Beginning date for report will be " & HDate & vbNewLine
    Msg = Msg & "Ending date will be " & EDate & vbNewLine & vbNewLine


    Title = "Confirm Report Date Range"
    config = vbYesNo + vbQuestion
    ans = MsgBox(Msg, config, Title)
    If ans = vbNo Then
            MsgBox "This Report has been TERMINATED by USER"
            Exit Sub
    End If

' Bunch of code formatting the data and such not

    ActiveWorkbook.Sheets("Data Staging").Copy after:=ActiveWorkbook.Sheets("Data Staging")
    ActiveSheet.Name = wsName



Exit Sub

Thanks

Jacob

 

[pboltonchina]

Complete stab in the dark but try this on a copy of your data.
Replace this

wsName = "Six Months Rpt " & Format(EDate, "mmddyy")

with this

If SheetExists( Then
Sheets("Six Months Rpt " & Format(EDate, "mmddyy").Select
Cells.ClearContents
Else
Sheets.Add After:=Sheets(Sheets.count)
ActiveSheet.Name = "Six Month Rpt " & Format(EDate, "mmddyy")
End If

And put this after the "End Sub" line

Private Function SheetExists(SheetName As String) As Boolean
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(SheetName)
SheetExists = (Err = 0)
Set x = Nothing
End Function

This will overwrite if the sheet exists or create it if it doesn't exist. I've tried to adapt this from something I've used before but I'm not sure about the date involvement with the sheet name.

Just trying to help

 

[JAlbers]

Thanks for taking the time to take a look at this. Right now, I am getting a compile error: Argument not optional and it highlights the SheetExists portion of the If Then statement.

I'm going to keep playing with it and I may have come up with a solution on my own albeit extremely convoluted. I should be able to shorten mine down as I work through the if then logic. Life is a journey not a destination, got to keep learning.

Thanks