PDA

View Full Version : Solved: Generate sheets based on data



Sir Babydum GBE
10-30-2005, 05:32 PM
Hi

I have 3 columns of data:

A contains what we call a ?Role Profile?
B contains codes for learning modules
C contains a list of cities.

Let?s say there were 3 entries under each, for Role Profile we had ?Dialling?, ?Answering? and ?Greeting?. For modules we had ?Mod1?, ?Mod2?, and ?Mod3?. Finally, for Cities we had ?London?, ?Edinburgh? and ?Cardiff?

What I want to do is for a macro to create a sheet for each Role Profile ? each sheet being named after that profile. A profile name may contain illegal characters as far as sheet tab names ? or may be too long ? but the macro should strip out illegal characters and truncate the name if necessary.

So let?s say the attached workbook only contained sheet 1 to start with, can a macro generate the sheets as shown in my example?

Thanks a lot.

(I'm using Excel 2000 where this will be employed)

Bob Phillips
10-31-2005, 03:16 AM
BD,

What is Consolidated? Do you want TabName calculated, or will you do it? Do you want the data from consolidated copied to the new sheets?

Sir Babydum GBE
10-31-2005, 03:32 AM
Mr X,

Thanks for looking at this. Consolidated needs to be created too, it'll be used for a pivot table - it is the same principle as the other sheets, except that all role profiles appear on it, so it will be much, much bigger. I would like the TabName created, but that's not a big deal - just a nice to have.

By the way, what I actually have is around 60 Role Profiles, 80 Modules, and 8 sites - that's why I want this process automated.

Bob Phillips
10-31-2005, 05:04 AM
.

Sir Babydum GBE
10-31-2005, 07:46 AM
Mr X,

You are a star - but can I have my money back please?

It doesn't appear to do the seperate tabs for each profile, maybe i didn't explain that one very well. But on the consol sheet, the same role profile is displayed - not all three - any ideas?

(You really are a star, and I'm only kidding about wanting all my money back)

Bob Phillips
10-31-2005, 03:52 PM
Mr X,

You are a star - but can I have my money back please?

It doesn't appear to do the seperate tabs for each profile, maybe i didn't explain that one very well. But on the consol sheet, the same role profile is displayed - not all three - any ideas?

(You really are a star, and I'm only kidding about wanting all my money back)

Sir BD (oops, sorry, didn't mean to let that slip),

Don't know what happened there. Your second post made it quite clear what was required, and I did code it to such, but I seem to have lost the latest version and posted an earlier.

Anyway, here is the full code.

I think I have to be fair, so I'll re-imburse half the fee. Is that acceptable?

Sir Babydum GBE
11-01-2005, 12:41 AM
Thanks very much Mr X,

You truly are out standing in your field! :)

Bob Phillips
11-01-2005, 04:43 AM
You truly are out standing in your field! :)

Which field might that be?