richardcdahlgren
Board Regular
- Joined
- Oct 16, 2008
- Messages
- 81
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
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