Macro to Add sheet after last sheet

I have tried to write code to create a sheet to have the same name as in cell A1 on sheet1 ("Data") and to copy all the values and formats


For eg if A1 on sheet1 (named "Data") A1 is July-2019 , then sheet "July-2019" is to be created after the last sheet and the data copied and pasted as values and the format also to be the same


I get a run time error when running the macro

 Sub AddSheet_Names()
  Dim MyCell As Range, MyRange As Range

 Set MyRange = Sheets("Data").Range("A1")
 Set MyRange = Range(MyRange, MyRange.End(xlDown))

 For Each MyCell In MyRange
 Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
 Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
 Next MyCell
End Sub

I have attached sample Data

It would be appreciated if someone could assist me

If you're copy all data and formats from Sheet1 to the newly created sheet, why not just copy it then stamp values over the formulas?

Try this:

Sub CopySheetToEnd()
    Sheets("Data").Copy After:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
        .Name = Format(Sheets("Data").Range("A1"), "mmm yyyy")
        .UsedRange.Value = .UsedRange.Value
    End With
End Sub

Does that work for you?

BSB

Thanks for appreciated the help, much appreciated

I would like to know how to save the sheet once copied to desktop using VBA

Sub test()
    Sheets("data").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 Sub

Thanks Jindon Works perfectly

Also posted on https://www.mrexcel.com/forum/excel-...t-desktop.html