Need a VB code to generate sheets from a list of cells

Hi, first post here, but have already used the site many times to help me compile a few spreadsheets for work...

i am pretty poor with excel in comparrison to you guys, but am getting there slowly but surely

I used this code from another thread, its used to generate worksheets with a specific name from a list...

Please Login or Register  to view this content.

So at present, its copying sheet 2 and naming them from sheet 1 column A, but i need two things additionally which i am not sure how to do...

firstly, i need it to automatically stop when it runs out of names in the column A rather than generate an error when the list runs out.

Secondly, i need it to operate in a "live" enviorment, so the second someone adds to the list it creates a new worksheet with the corresponding name, and also when a name on the list is deleted it deletes the corresponding worksheet.

Any help much appreciated as its way beyond my capabilities!

Good afternoon Thanatos

...and welcome to the forum!!

Please try and remember in future to wrap your code using the "'#" button from the toolbar above - I've done it for you this time.

Have a look at the file attached. What you're asking is not that straightforward as it requires two checks comparing the range to the sheet names before doing any actions - and one of these requires a loop within a loop. As you want this doing as the user is typing, this may slow the file down a little, and also your undo stack will constantly be cleared (ie you lose your undo functionality on this file) - this is unavoidable. What I've done is a first bash and seems to work OK as a starting point.

HTH

DominicB

Originally Posted by dominicb

Good afternoon Thanatos

Have a look at the file attached. What you're asking is not that straightforward as it requires two checks comparing the range to the sheet names before doing any actions - and one of these requires a loop within a loop. As you want this doing as the user is typing, this may slow the file down a little, and also your undo stack will constantly be cleared (ie you lose your undo functionality on this file) - this is unavoidable. What I've done is a first bash and seems to work OK as a starting point.

HTH
DominicB

Your code worked a treat (couldnt get yawnzzz to work ). However I have a couple of tweaks i need adding to it, wondered if you woud know how...

I have some sheets i want to add manually that wont be affected by the code, and I need the replicated sheets to be copied from sheet 2...

Might be easier to explain what I am doing... lol

Essentially i am creating a document to monitor employees case loads (what they achieve daily.

The idea behind this part of the code I am looking for is that each staff member has their own sheet (only they can see - I have perfected that code already). The master user - e.g. the manager has access to all sheets, one of which has a list of staff numbers which will create the worksheets the employees have access to. So essentially when a new guy starts it adds a sheet for them and when one leaves it removes it. It's the only way I can think of that a lay user can maintain the workbook even with no excel knowledge...

Does that help explain? So essentially employee sheets need auto creating (copying from existing sheet) and the rest of the sheets need leaving alone.


Sorry to be a pain guys!!

edit: Also just noticed it doesnt recognise numbers and create new sheets from them. letters only... that would be a problem too.