Dynamically creating new sheets when data is added to a column

The following code creates a new sheet with a for every value on the "C" column from the "Zuordnung" sheet and renames the sheet with the contents of the cell.
However, if I want to make it dynamic, i.e. for a new sheet to be created every time I enter an additional entry in the column, what is the best way to do so?

Please Login or Register  to view this content.

Any help would be appreciated!

Welcome to the forum

Put this in sheet module for "Zuordnung"

(working example in attached file)

Please Login or Register  to view this content.

SheetModule.jpg

Some characters are not allowed within sheet names
\ / * [ ] : ?

This is a modified version of the same code that can handle illegal characters without the code failing
A warning is given and the new sheet is deleted if the name is illegal

Please Login or Register  to view this content.

IMPORTANT - please edit post#01 - click on edit post, then select the VBA code text and click on # icon
(this puts your code in code tags and makes it easier to read etc)
thanks

I will experiment with "click and drag" and post amended code later this morning

In the meantime, please post the complete "corrected" code (enclosed in code tags) for the benefit of future readers

Here you go

The tricky bit to understand here is that:
- where there is only one cell the Target cell is not usually the selected cell (cursor has usually moved to adjacent cell)
- when you click and drag you are selecting cells whose worksheets already exist - so selected cells contains too many cells
- the range is set either to the one cell or the selected range and new worksheet(s) created
- if a sheet name already exists, then it cannot be used again and the not-required sheet is deleted
- the same bit of code also prevents illegal names being used

Message strings added to tell user what happened
- sheets will not have been added for one of 2 reasons (illegal name or already exists)
- sheet already exists if it's the 2(or more) cells inducing pattern at top of click and drag range - so not a problem
- sheet already exists if a value in column C is duplicated
(you may prefer to get rid of the messages)

(There probably is a way to specify the range for the newly added cells in click and drag (and thus avoid creating and deleting some sheets) but I was too lazy to look it up - the result is the same - I may investigate sometime and let you know )

Please Login or Register  to view this content.

or without the messages

Please Login or Register  to view this content.