Dynamically ReNaming the Most Recent Added Sheet with a Cell Value in the Main Sheet

[e4excel]

Dynamically ReNaming the Most Recent Added Sheet with a Cell Value in the Main Sheet

I have a Template which pulls data from several Sheets and I need to make a Copy of the Main Sheet which has Dynamic data which keeps on changing with the help of DropDown.

Now I have managed to Record a Macro which populates the Entire Sheet contents of the Main Sheet as Values with exactly the same format..

In my Recorded MAcro the first Step is Adding the Sheet and then Copying the ENtire Format of the Main Sheet and some Individual Cells as they are some cells which are Merged and carefully copying these cells Individually..

Now I have managed to make this work decently, however thought of Renaming the Sheet Dynamically with a cell Value but the problem I am facing is I dont know what No of the "Sheet X" is going to get generated and therefore require help in this aspect where I dont know how to keep a Track of the New Generated Sheet..

Please find the earlier query where I had asked about Dynamically Adding Sheet:

http://www.excelforum.com/excel-prog...namically.html

However, In the above query as I was explicitly putting the Sheet No and the Name but in this case I already have the Name Stored in one of the cells and therefore this is a slightly different requirement..

The format of the Sheet is as shown in the attached WorkSheet, strangely when I Run the Macro it works if I am continuosly from Sheet1 and so on , however if I delete any SHeets then it causes the DEbug Error for the Sheet No and does not work smoothly.

I want a Command Button to be Added and on the Click Event of the Command Button I want the SHeet Replication and also Renaming the Sheet with the value conatained in the cell C4 of the Ledger Book concatenated with the text "Ledger Book".

Warm Regards
e4excel