Macro to Double Click 2 cells to create a copy of a Hidden Sheet.

Hey Excel gods. Needing help with this challenging task and I hope I can properly articulate the challenge and desired outcome

Here is the scenario:

1) I have 2 Sheets (Sheet1 & Sheet2). Sheet 2 is a Hidden Sheet.

2) In Sheet1, I have the Cells, D1,D23,D50 and D70 and A1

Macro Challenge/Criteria:
a) I'd Like a Macro that kicks in when D1 (or D23 or D50 or D70) in Sheet1 is double-clicked, a thick black border around the cell appears.

b) When cell A1 is also double-clicked (after either D1, D23,D50 and D70), the cell is given a thick Red border and THEN a copy of Sheet2 (Hidden Sheet) is created and made visible

c) The newly created copy of Sheet2 is named based on the values in cells A1 & D1 or D23 or D50 or D70 & the current date
(e.g. if Cell D1 has the value "Tom" and A1 has the value "Jones" and the macro was run on the date June 20, 2015, then the name of the copied sheet would be "Tom_Jones_June20_2015").

d) if the new sheet created already has a sheet with the same name, in (c) above, then add an increasing number to the end of the name (e.g. the copied sheet would be "Tom_Jones_June20_2015_2" or "Tom_Jones_June20_2015_3"..etc

e) Double clicking any of the cells already double clicked (as indicated by thick cell border) would make the borders disappear.

I Hope I laid out the needs/requirements for the macro. Pls help.
Thanks

Regards
H.

Oh wow. You guys are Excellent
Thanks for the speedy response
Will give both a whirl and test and report back soonest!

Thanks Lewis.
I attached the Workbook to show how I have tested it.
It works fantastic except the new sheets being generated with the Unique Sheet names only show one at a time (i.e the current sheet generated is visible and the previous ones are hidden sans their unique sheet names).

Pls how can the code be modified to rectify this?

Thanks

nikerickson,
Thanks for your code. It works great!!!! ...but slightly a bit different than planned.

Here are the 2 issues with the results When the macro is activated;
a) By the double click of Cell "A1", the copy of sheet 2 that is generated is hidden automatically, and
b) It is sheet1 that is named uniquely instead of the newly generated copy of sheet2 (which should be visible)..all the copies of sheet2 generated should be visible with the unique names

Does that make sense?

Thanks so much for the time taken to help with this!!!

Originally Posted by mikerickson

If you put this in the code module for sheet1, it should do what you want.

Please Login or Register  to view this content.

Harry,

It works fantastic except the new sheets being generated with the Unique Sheet names only show one at a time (i.e the current sheet generated is visible and the previous ones are hidden sans their unique sheet names).

There seems to be a discrepancy between Excel 2003 (where the software was written) and Excel 2010 where I tested after you reported the problem. I will try to have a fix for you by tomorrow afternoon.

Lewis

Try the attached file which has been tested using Excel 2003 and Excel2010, and seems to work for me in both versions of Excel.

Lewis

LJMetzger & mikerickson,
Thank you guys so much with your help on this. I really appreciate it (you have no idea). I have been out of the office on medial leave and only now just getting around to properly testing/using your solutions to my query. I apologize for the delay in feedback (it could not be helped).
Will give both a whirl and report back soonest.
Thanks thanks Thanks again!!!!
- Harry