Add sheet is workbook exists

[richardcdahlgren]

I have a code that creates a workbook and adds a sheet to it then saves. I need to adjust the code so it looks for the filename first and if it exists to just add the sheet to the existing file. Any and all help is greatly appreciated!! Code below:

Sub Button5_Click()

Dim filename As String
Dim path As String
Dim report As Workbook
Dim t As Range
Dim rs As Worksheet

Set report = ActiveWorkbook

filename = report.path & "\" & Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1)) & " " & "Ticket Review" & ".xlsx"

Application.ScreenUpdating = False

report.Sheets("Ticket Review").Range("A1:K125").Copy
Workbooks.Add
ActiveSheet.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
ActiveSheet.Range("A1").PasteSpecial xlPasteFormats
For Each rs In Sheets
rs.Name = rs.Range("K3")
Next rs

ActiveSheet.SaveAs filename
Application.CutCopyMode = False
ActiveWorkbook.Close
Application.ScreenUpdating = True

End Sub

 

[mmhill]

Something like this will work.

Sub FileVoodoo()
Dim fs As Object, wb As Workbook

    Set fs = CreateObject("scripting.filesystemobject")
    
    strPath = "C:\TEST\Me.xlsm"
    
    If Not fs.FileExists(strPath) Then
    
        'do your new workbook stuff
        
    Else
    
        Set wb = Workbooks.Open(strPath)
        
        'do your existing workbook stuff
        
        wb.Close True, Path
    
    End If

End Sub