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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Thanks Jindon Works perfectly

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