Create sheet after last sheet

[howard]

I have a sheet called "Costing Sheet"

I have written code to put todays date - 5 days in col A1 on Sheet "Costing Sheet" and the copy the sheet after the last sheet and then to name sheet based on month and year in A1 for eg Oct-2019


I get an application defined or Object defined error and the following code is highlighted

.Name = .[a1].Text

See Full code below

 Sub AddSheet_Names()
 With Sheets("Costing Sheet")
 
    With Range("A1")
    .FormulaR1C1 = "=NOW()-5"
    With Range("A1")
    .NumberFormat = "mmm-yyyy"
 
Sheets("Costing Sheet").Copy , Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
        .Name = .[a1].Text
        .Copy
        With ActiveWorkbook
            .SaveAs CreateObject("WScript.Shell").SpecialFolders("desktop") & _
                    "\" & .Sheets(1).[a1].Text & ".xlsx"
            .Close False
        End With
    End With
     End With
        End With
             End With
End Sub

It would be appreciated if someone could kindly amend my code

 

[Fluff]

Try

 Sub AddSheet_Names()
    With Sheets("Costing Sheet")
        With .Range("A1")
            .FormulaR1C1 = "=today()-5"
            .NumberFormat = "mmm-yyyy"
        End With
        .Copy , Sheets(Sheets.Count)
    End With
    With ActiveSheet
        .Name = .[a1].Text
        .Copy
    End With
    With ActiveWorkbook
        .SaveAs CreateObject("WScript.Shell").SpecialFolders("desktop") & _
        "\" & .Sheets(1).[a1].Text & ".xlsx"
        .Close False
    End With
End Sub

 

[howard]

Thanks Fluff. Code now works perfectly

 

[Fluff]

You're welcome & thanks for the feedback