Create cheet based on cell value - dont alow replication

Afternoon all,

I am trying to create a VBA macro that creates a new worksheet in a workbook at the click of a button. The problem I have is that I would like this worksheet to be named after the value (a date) in a certain sell (say A1). Also, I would like the macro to NOT allow two worksheets of the same name (with (1) etc.) to be created, but for the macro simply to take the user to the sheet already created, should they select a date for which a sheet already exists. Finally, once a new sheet has been created, I would like the user to be taken to this sheet.

While I have figured out the macro to make a new sheet, the naming and the rest of it is proving challenging. Does anyone have any good ideas?

Thanks in advance!

Are the values (dates) in column A and how does the user select the value for which a new sheet should be created?

Hi Mumps,

The sheets will be created on an individual basis, based on the date that the user will select from a drop down menu (the value of which is in a single cell only), and a "create" button. They will pick a date, and click create. If there is no sheet for this date, it should create a sheet with the date name (taken from the single cell mentioned above), if the sheet already exists with that name, it should just go to that sheet.

The drop down menu and all of that is already working, it is just a matter of taking the value from the single cell, and making the sheet.

Thanks

Instead of using a button, a Worksheet_Change event will automatically create the sheet upon the selection of the date in A1. Right click the tab for the sheet that contains your date in A1. Click 'View Code' and then paste the macro below in the empty code window that opens up. Close the window to return to your sheet. Select a date in A1. Please let me know if this is what you were looking for.

Please Login or Register  to view this content.

Thanks for the help. This is very very close to what I want. The problem I now have is that the value in cell A1 is a lookup function, and thus the macro comes up with an error (works great if I just type text in). Is there some way around this?

Thanks again!

I'm not sure if I have a solution for you but if I could have a look at your file, it might help. Could you post a copy of your file replacing any confidential information with generic data?

Try the attached file. Choose the date in the drop down list and click the button.

Perfect! Thanks so much.

Hi again,

I have changed this code slightly so that it now creates the new sheet from another "template" sheet. The problem I now have is that the new sheet appears at the end of the workbook (i.e. the last in the list of worksheets) and I would like it to appear as the third sheet (or if this isnt possible, the first sheet). I can quite figure this out, so any help would be hugely appreciated.

Please Login or Register  to view this content.

Try changing this line:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Perfect, thanks. I knew it must be something like this, just couldn't get the syntax right.

Much appreciated Mumps.

My pleasure.