Auto creation of multiple sheets and population of data from one main source (VBA Coding)

Hello!

I've setup a workbook for event registration which, using VLookup, populates 'Sheet 1 columns B-G' from 'Sheet 2 columns B-G' when a unique ID number is entered into 'Sheet 1 column A'. The information it populates is (Applicant Number, Name, Surname, Talk description, Talk time, Group).

What I'm hoping for is that when a unique ID number is entered in Sheet 1 column A, new sheets are automatically created based upon the 'talk description' and populated with the appropriate (Applicant Number, Name, Surname, Talk description, Talk time, Group) information.

I've tried a few methods of VBA coding but all require a manual intervention to run and ideally I was hoping this would be an automatic function.

I've attached a sample file - Any help is greatly appreciated!

Install this code in the worksheet code module for sheets("Registration")

Please Login or Register  to view this content.

The code will run when any change is made to the worksheet. It will only execute when the change is made in column A in a single cell. You cannot execute the code if you paste more than one cell of data into column A.

.
And ... if you are interested ... here is an addition to the macro code that will auto-name the newly created tab corresponding to the person's Last Name, First Name for quick reference :

Please Login or Register  to view this content.

NOTE: I changed your second sheet name to DATABASE. If you prefer the original tab name, edit the macro code anywhere you see "Database".

Brilliant - thanks both, much appreciated!

Is it possible to include the following in the same coding? (I've had a look at editing but wanted to check here!)

1. Live cell/cursor to always remain in Sheet 1 Column A - During event registration I'll be barcode scanning the unique numbers into cell A2 to create the data distribution.

2. For each named sheet, English, Italian etc. to be populated with list of registered individuals linked to that 'talk description'. When I tested the coding it created an individual sheet for each name or talk description (when I amended it so possibly my error).

*Not sure if it's easier if I create the 'talk description' sheets beforehand and use them as reference points to populate upon entering a unique ID in cell A2.

Apologies if I'm asking simple questions - relatively new to VBA coding!

.
Question #1 -

In the Worksheet_Change macro :

Please Login or Register  to view this content.

Question #2 -

If I understand correctly, rather than each individual having their own worksheet ... you desire to have an individual sheet for each language, and those participants who
speak that language be included on the appropriate language sheet ?

Is that correct ?

That's great, thanks for the update for Q1.

Q2. Yes, that's correct. A completed registration would include individual sheets for English, Italian etc. and a list of people attending under those 'talk descriptions'. Some lists may have 10, some 2 people and so on.

Hope that helps!

.
See attached:

Amazing - Thank you so much!

Apologies, one final query - This functionality removes the automation from cell A2 with the need to press 'enter' to create and populate. Is it possible for that to be automated?

.
I tried a number of different ways to fully automate the input process however, the code errors. Using the Command Button click is the best I could do.

Someone else might be able to obtain full automation for you.

Ah okay, no problem at all - Thanks, you've been a massive help!

You are welcome.

Cheers

.
Tino1010

Here is an automated version however, I can't vouch for the functional accuracy. So long as the only data entry you make
on the REGISTRATION sheet is in cell A2, it should not cause you any headaches. Entering data in any other location may cause
the workbook to error.

If anyone else would like to contribute .. please do so. Your suggestions are welcome.

Amazing - Just tested with different sets of data and works perfectly!

Data entry will always be cell A2 which hopefully will avoid any functional errors - Thanks again for all your help, really is appreciated!