Create worksheet from values in column 1

[djieon]

Hi All

I have a spreadsheet and in column c I have codes such as "AHA01" , "AHA02" , "AHA03" etc...

is it possible for me to do something that will automatically create a worksheet in my excel workbook for each of these values containing the rows that had those vaules in it?

using excel 2007.

thanks in advance

Dave.

 

[kjv1611]

I'm sure it's possible, but why not just manually create the individual worksheets? If it's b/c you have a very large set of data, and that the data is so complex it'd be difficult to do that part manually, then you might want to consider moving that to an Access database instead.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

And what is the purpose for these added sheets, so similarly named? BTW, there is no 'automatic' way, short of programming.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

Beware, also, of taking a step you will later regret. Questions like this can easily be followed up in a few months by a question "I have a workbook that contains many similarly structured sheets; how do I collect the following data from each of them automatically and put it together in one sheet...."

 

[SkipVought]

...contains many similarly structured sheets...

...hence the question regarding the PURPOSE of these 'so similarly named' sheets.

My concern as well. It is much MUCH easier to generate various SUBSETS of data, than to CONSOLIDATE similar data residing in multiple sheets/tables/locations.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lilliabeth]

I have data that is arranged in a well-organized list. How can I partition this data so it won't be so easy to glean valuable information from it?

Just teasing! Don't get mad! Be happy you have a well-organized list now.

--Lilliabeth

 

[kjv1611]

djieon,

If you're still reading this, have you tried using data filters on your column headers? That way you could just look at one group at a time visually without changing the data structure.

--

"If to err is human, then I must be some kind of human!" -Me

 

[djieon]

Hi All, thanks for all of the replies.
the reason i want to seperate this data out, is that I need to create a seperate spreadsheet for each of the customers and send it out to them to charge them! So in this case the 'owner' and therefore the person who pays the bills is indicated in column 'c' (AHA01 etc..)
So ideally I would like to create a spreadsheet for each owner code but I thought that would be impossible! so was going to manually covert each worksheet into a new workbook...
Does that provide enough information?

 

[lionelhill]

this is still a bit manual, but pivot tables can be helpful. If you make a pivot table of your data, classifying by column C, it will give you a list of all people you need to bill, and a double-click on their entry will create a new worksheet containing all individual entries relating to that person.

 

[SkipVought]

You'll need to create a macro.

Get comfortable with the following steps and then trun on your macro recorder and record doing ONE time thru the cycle.

FIRST make a list of unique OWNERS on a separate sheet.

SECOND turn on your AutoFilter on your source data.

Here are the steps in the cycle.

1. Select the first OWNER in the AutoFilter for OWNER.

2. Select ALL cells

3. Insert a NEW SHEET

4. PASTE

5. Right click the new sheet tab and select Move or Copy...

6. Move selected sheets
To book:
(new book)

7. Save the new book with the proper path & name.

8. close the new book

DONE WITH THE CYCLE.

Open the VB Editor (alt_+ F11), copy the macro you recorded and post in a new thread in forum707 along with an explation of what you're trying to accomplish.

You'll get some help to loop thru you list of OWNERS.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

Hi Skip,

Believe it or not, *I* have an EXCELLENT and QUICK solution for bjleon, but before I tell anyone, I want to make sure I'm able to discuss addins in this group.

I realize this is a learning resource but if tools exist, that are free, that can do this, may I share?

If not, I leave it to the gurus because I would manually create each sheet.

If it is possible, I will tell you where to find the free tool and how to do what specifically is being asked.

I just don't want to do anything which is not acceptable within this group. I owe it (you) my life here



LadyCK3
aka: Laurie

 

[SkipVought]

Share away Laurie.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

Thanks Skip

ASAP Utilities

http://www.asaputilities.com

This is a free utility, you can register if you wish but using it free requires that you download an update ever 6 months. And they do up date it all the time, so that's not really a stretch. When you open it after 6 months it will prompt you to download the new one. I use this (have been using it for over 10 yrs) to make SO MANY TASKS so easy, that my order of importance is this:

1. Excel
2. ASAP Utilities
3. Skip <grin> hehehehe well Tek-Tips (when I can't figure it out myself)

THIS IS A GOD SEND for SO MUCH....


djleon, for what you are trying to do...
Once installed it will add a link in the main menu for "ASAP Utilities"

Go to "Sheets" / 2. Insert Multiple Sheets

It will open a dialog box where you can check to use the color of the cell to be replicated on the tab and you can also use another sheet in the workbook as a template (this saved me H O U R S of work)

Get your first one set up the way you want it "AHA01" so you have a template. Then select AHA02 on down.... go to ASAP Utilities, select the menu options and in a matter of 2 seconds, your sheets are done.

You can short sheets in a workbook, change text number to text, remove web garbage when pasting from html, insert multiple rows, remove blank rows, format ALL sheets like a specific sheet, print MULTIPLE PAGES by checking the ones you want to print. You can even... setup the page settings for printing on one, and then format other sheets as the first. This is just a FEW of the things.

Everyone uses it differently but the key here is USE IT... it saves SO MUCH TIME!

Seriously I started using this back in the 90's when I first found it. Gosh, that's 20 yrs? Oh man I'm getting SO OLD hehehe

Be one with the ASAP Utilities... and Skip, I hope maybe I even taught you something today (oh heck, you are propbably a co-author of the tool)





LadyCK3
aka: Laurie

 

[SkipVought]

What?!?!

I'm only THIRD on your list!!!???!!!???

;-)


Have a little purple star, Laurie!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

<thud>



<whispering> "i" got a STAR?


<thud>

Thanks

LadyCK3
aka: Laurie

 

[ladyck3]

LOL, we sent our messages at the same time.. and Skip, now c'mon do you REALLY want to BE at the top of this list? Man can ONLY dream!

I need Excel it is my life, I am putzing in it all the time and w/o Excel I have no need for ASAP Utils and is where I turn to for so much (and it removes leading and ending spaces) oh it does so much I don't even yet know about....

But can you imagine if I did not have that util, Skip you would never hear from any one else but ME! I'd keep you WAY to busy

Ok, I'm shupping now... thanks for the star I am giddy with delight.... *I* got a STAR! (no delusions of grandure though ... "they" can think I'm a guru, but *WE* know different) <wink> hehehehe



LadyCK3
aka: Laurie

 

[lionelhill]

I hate to be boring, but someone should mention it's not strictly a free utility. The license offers free use to home users, but it's a 3-month trial for business use.

That said, thanks for the tip about a great resource!

 

[jgeekw]

ladyck3, I love the ASAP Util! But, I guess I'm not quite finding it to work the way Im needing it to. Here's what I have:

Three Columns titles Email, Contact, and State

There are approximately 40,000 addresses in the email column with accompanying data for contact and state. I need to split the addresses up into individual sheets with 1,500 addresses per sheet with it's accompanying data. How do I go about doing this with ASAP Utils?

 

[ladyck3]

Well, rgITguy....

I've never tried to do such a thing with ASAP Utils and since this is a forum for Microsoft Office, I might suggest viewing the user guide for the product or contacting ASAP Utils to see if they have such an option within their plugin.

I am not trying to be rude or anything but I use it for SO MANY things and know there are so many more uses .. but I don't support the product... I've never tried this .. if I had working knowledge of such a function I would let you know though

The function that I spoke of relative to the initial inquiry here was to create worksheets from data in specific cells. But that does not appear to be what it is you are trying to accomplish so anything I can offer is manual and tedious... and I'm sure you already know that

Sorry!


LadyCK3
aka: Laurie