Need Excel Code to Create/Save Sheets

Microsoft Office 2007 home and student
March 10, 2010 at 05:29:08
Specs: Windows XP
I'm very in experienced at codes, please help...(excel2007)
I have a work sheet (sheet1) with header rows 1-3. Data rows start at 4 though to up to 500. This has columns A-I.
I have 12 brokers that submit deals for approval contained in sheet1, hence these 12 names come up as the deals get entered onto sheet1 (rows up to 500)

I need a code to do the following if possible:
1. Sort sheet1 according to Col A - ascending. (not including row1-3 in the sort) -
2. Create 12 different worksheets (basically copying sheet1 12 times (one for each broker)
3. Name new worksheets according to the 12 unique names contained in columnA
4. Delete rows not pertaining to the broker/worksheet name.
5. Save each worksheet separately with the file name = tab/worksheet name under the following path: My computer/ my file / Expendable Money1/ ”name of the broker”

Now for the kicker: I will be adding names to the 12 in Col A, this might eventually go up to 30 or 40. so it must be “open ended”? The Process must them include the new names added. Obviously empty rows in between must be deleted.

PLEASE HELP, too complicated for me.

See More: Need Excel Code to Create/Save Sheets

Report •

March 10, 2010 at 06:13:21

I think that you have more complication in your proposed code than is necessary.

1. You want this code to create 12 workbooks (and later add new workbooks as new names are added). Essentially the code will be used only once to create each workbook. As the workbooks have the users name as the workbook name, you will either add new data to the existing named workbooks or overwrite the existing data, but you won't create a new workbook every time you run the code.
1a. I suggest you create the 12 workbooks manually, and then add a new workbook as new 'names' come on board.

2. Copying Sheet1 in it's entirety to worksheets, naming the worksheets and then saving the worksheets as individual workbooks does not appear to add anything to the process.
2a. With a list of current names, the code could open the named workbooks in turn, and for each named workbook it would search column A (A4 to A500) for the same name, and as each entry is found, copy it directly to the next avaialble row in that person's workbook.
2b. This eliminates the need to copy and create worksheets, and eliminates the need to delete rows of data that don''t belong on that worksheet.

3. Also:
3a. You don't mention how new data is handled - is there a new source workbook coming in every day, which has to be handled from the beginning (row 4), or is the existing workbook updated, so only new rows of data need to be handled.
3b. How much data is stored for each person in their named workbook. Do you add new rows for ever, or is the old data deleted or overwritten.

In summary, I suggest that you have named workbooks, and the code just adds records to each named workbook as appropriate.
You might want each named workbook to have 12 worksheets, so that records are more manageable, and summary reports could easily be prepared for each month.

Obviously it's your call, you can use the process you have outlined (but you will still have to identify how new data is handled), or you can consider the outcome required, and not worry about the steps to get there.

Think about the final outcome you require and how you will use the sorted and stored data. If you know how you are going to use the data, then how you store it now, will impact how easy it will be to get the information out of it in the future.


Report •
Related Solutions

Ask Question