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

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

Please Login or Register  to view this content.

Dear ProtonLeah,

Thanks for the prompt reply however its not working as I keep on getting the error in the "Sheet1" line i.e.

Please Login or Register  to view this content.

As this does not get Updated at all after I have added more than 1 Sheet..

Honestly speaking I'm no good in VBA but understand some coding...

Now in the above line the "Sheet1" gets hardcoded so how do I make it increment to the New Sheet No or the Current No of Sheets Total No incase if any SHeets were to be deleted or removed..


If this works then the ReNaming portion would also work I suppose..So,I would appreciate if you could please add or edit the file...

Thanks in advance

Warm Regards
e4excel

Thanks a lot Protonleah,

It worked, there was a mistake on my part as the Name of the Main Sheet was "Ledger-View" and not "Ledger Book" as given by me in the example and therefore it took so much of time for me to rectify the same..

Then had to just replace the following line in the entire code wherever it occured as follows:

Please Login or Register  to view this content.

I am thinking of making the New Sheets by Time Stamping them with seconds as in "hh:mm:ss" so that even Multiple Copies cannot throw errors..
So please suugest how do I get the current time in the format as shown above.

I can store the time value in the cell G3 but how do I make it ticking without rendering the file slow?


Warm Regards
e4excel

How about:

Please Login or Register  to view this content.