I want to save a selection as a new workbook but if the workbook already exists i want to save as a new worksheet within the existing workbook instead

Question

I'm still fairly new to this. I want to be able to do the following:

1. select a copy range
2. paste selection in a new workbook
3. save workbook in a folder with year value found in range H5 (if folder does not exists, create one)
4. save file as "title_month_year" values found in ranges A5,F5,H5 (but if file already exists save as new worksheet/tab)

So far I believe I have 1-3 covered and part of 4.

Option Explicit
Const MYPATH As String = "C:\USERS\3658\Desktop\"

Sub IfNewFolder()
Dim AuditYear As String
    AuditYear = Range("H5").Value

'if a particular directory doesnt exists already then create folder.
If Len(Dir(MYPATH & AuditYear, vbDirectory)) = 0 Then
   MkDir MYPATH & AuditYear
End If

End Sub



Sub SaveCustomizedCourse()
'copy and past selected data in a new workbook

Range("B8").End(xlDown).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy

    Workbooks.Add
    ActiveSheet.Paste

    Range("A1").Select
    Selection.PasteSpecial xlPasteColumnWidths
    Selection.PasteSpecial xlPasteFormats


'save selected data in a new workbook
Dim AuditMonth As String
Dim AuditYear As String
Dim AuditTitle As String

    AuditMonth = Range("F5").Value 'MONTH
    AuditYear = Range("H5").Value 'YEAR
    AuditTitle = Range("A5").Value 'TITLE

    IfNewFolder 'creates a yearly subfolder

    ActiveWorkbook.SaveAs Filename:= _
    MYPATH & AuditYear & "\" & AuditTitle & "_" & AuditMonth & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

        MsgBox ("Audit Saved.")

        'ActiveWindow.Close

End Sub

Answer 1

You can add the below sub and call it after IfNewFolder and remove all the code after it.

Private Sub Carla(AuditMonth, AuditYear, AuditTitle)

Dim CurWb           As Workbook 'This is whatever workbook you are working with
Dim SaveAsWb        As Workbook 'This is spare for the workbook in case that has the same name
Dim SaveFileName    As String

Set CurWb = ActiveWorkbook
SaveFileName = AuditYear & "\" & AuditTitle & "_" & AuditMonth & ".xlsm"

If Len(Dir(MYPATH & SaveFileName)) = 0 Then
    CurWb.SaveAs FileName:=MYPATH & SaveFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Else
    Set SaveAsWb = Workbooks.Open(MYPATH & SaveFileName)
    CurWb.Worksheets("Sheet1").Copy After:=SaveAsWb.Worksheets(Sheets.Count)
    SaveAsWb.Save
    SaveAsWb.Close
End If

MsgBox ("Audit Saved.")

End Sub