Macro to rename sheet based on date in a cell with multiple occurrences.

**timlocke** · 04-08-2011, 06:32 PM

Guys,

I am getting whooped here.

I have a spreadsheet that has 25 tabs. This worksheet updates based on an external data pull. I need to rename all the tabs in this sheet based on a date that is in cell C7 on each sheet.

I have that part down with this code.

Please Login or Register  to view this content.

Now the problem that I have is that I may have the same date repeat 5 or 6 times in this data and need it to add a (2), (3), etc... to each extra one after the first. It is acceptable if it puts a (1) on the first one also.

Anyone have any ideas?

**Marcol** · 04-08-2011, 09:31 PM

Try this

Please Login or Register  to view this content.

Hope this helps

**timlocke** · 04-09-2011, 12:22 AM

Doesn't quite work, I tried it in your sheet and in mine, it does the first one then gives me an error saying you cannot name the same as another sheet.

It seems to be closer than what I had though.

**Marcol** · 04-09-2011, 08:10 AM

Try this then, I didn't allow for you running the macro more than once, is that what you meant by.

...I tried it in your sheet and in mine, it does the first one then gives me an error....

Change the variable strRange to suit your reference cell

Please Login or Register  to view this content.

I have left a macro in this attachment that will reset the sheet names to "Sheet1", "Sheet2", "Sheet3", etc this should only be used after running the main macro.

If this doesn't work post a sample workbook that contains the conditions met in your workbook.

**timlocke** · 04-09-2011, 12:42 PM

This one works like a champ. I really appreciate all of your help. You have just saved me probably 2 to 3 hours a week.

**e4excel** · 04-12-2011, 03:08 PM

Marcol,

I was trying a similar query a few weeks back and this solution is very close to the one I required!..

Thats a very good solution..!

Warm Regards

e4excel