Filtering and sorting data, copied to an additional sheet

[icerabbit]

I'm looking for some guidance / a solution so I could hopefully automatically sort linked data alphabetically on an additional worksheet.

My mom volunteers for a non-profit organization consisting mainly of retired people and I'm her go-to guru for anything Word, Excel, ... so I'm working with her to revamp a variety of older documents ... improving, automating, merging and consolidating them as possible, to eliminate manual calculation and maintaining two or three lists, where one could do.

The document I'm currently working is stumping me a bit and starting to show the limits of my experience.

I will try to explain the setup in short order.

I created document with multiple worksheet. Each worksheet has a representative, a series of members and some numbers per member.

To accommodate for a large amount of membership fluctuation and future proofing (more joining members); I gave each page 36 rows for member names. Sometimes there are more blanks than members, but some are quite full.

So, each row 4 - 39 is a member. At the bottom the columns are tallied up.

The whole document goes in essence like this:

Page1: Representative 1 + n Members (12) in 4-39
Page2: Representative 2 + n Members (6)
...
Page7: Representative 7 + n Members (24)
...

Page25: Grand total / summary page with 24 rows with linked/copied data from the totals of each page + an extra row with the grand total per column.

So far so good.

Now Page 26 should become an alphabetical list of all members across all worksheets and their information.

To that extent, I created this worksheet with cell links to all the member data on sheet 1, sheet 2, sheet 3, ... etc ... So I have a copy of 36 rows per sheet into page 26.

One issue: When no name is filled out in a name or address cell, I get cell with 0.

If you go to sort, the 0s come first.

To counter that I can apply the filter, and eliminate anything with 0.

Then sort alphabetically by name and I get a good list.

The real issue is now that this is today's status. When anything changes as far as an extra member joins or someone drops out on any page, this change doesn't happen on page 26. The filter doesn't adjust. Nor will it re-sort.

Question: Is it possible to set the document up, that it can maintain an automated alphabetical name list on a separate sheet?

Which would eliminate me working on this alphabetical list every month, or teaching my mom how to do the filtering and sorting.

How should I look to tackle this page?

I've looked around the help file, searched the internet and this forum, found some partial stuff ... but I'm not getting nearer to solve the puzzle. I also get a sense that I may need a macro or VBA.

I really really appreciate any pointers and help anyone can offer to help me automate this alphabetical page.

PS:
I'm in Windows Vista, Excel 2007, USA. Saving files to Excel 97-2003. As my mom uses Excel 2000 Dutch in XP.

 

[icerabbit]

In further thinking ( just trying to get something workable ), I've recorded a macro to apply the filter and do the sorting. Which works well, except there's no undo ... to leave sheet 26 intact with all its references to the all other cells that currently have no data.

So, now I'm thinking to have a macro create a copy of sheet26, into a new sheet and do the filter & sorting. This sheet 27 could then be scrapped the next month.

Still hoping to hear from someone which would be the best and most professional avenue.

 

[icerabbit]

The recorded macro route may not work:

The recorded macro route doesn't seem flexible enough. It uses create new sheet. Rename: Alphabetic ... which doesn't work the next time around. If sheet3 or sheet4 (whichever autofill name excel suggests for a new sheet) has been deleted previously and it names it sheet5, then it gets stuck.

So the scenario of having a copy of all member data (36 copied rows per sheet) on page 26 (which is locked against user editing) and creating a page 27 (to filter and alphabetize members) once a month, and then the next time deleting the previous alphabetical page and creating a new one ... doesn't seem to work for now.

Also, the macro records the existing member names in the array for filtering purposes ... at the time of the creation of the macro ... what will happen when there are new members?

I'm going to need something more flexible than my recorded macro.

 

[icerabbit]

Made some progress with VBA getting the sheet added with a name, instead of recorded macro bugging on Sheet6 or ...

 

[icerabbit]

After a few hours of research I have a working solution.

Didn't find an automatic solution, but fixed bugs with the partial solutions I had. With the macro I built - with plenty of research, trial and some error - it is possible to build a the desired sheet on demand, in 5 secs.

 

[Jerry Sullivan]

Hello icerabbit,

It looks like you made some progress on this on your own.

Regarding the problem of adding a sheet name when that sheet name might already be used, you might be able to adapt the code from this post #6 of this thread:
http://www.mrexcel.com/forum/showthread.php?p=2629338

Looking at the conceptual design of your workbook, there is probably a simpler way to accomplish your task, but that will depend on how the data will be used.
I'd suggest you try to keep your data on one worksheet if possible and either:
Use autofilters to sort and/or show the data you want to isolate or
Generate reports that link to this data on one or more "Report" or "Summary" worksheets.

To do that, you'll probably need to restructure your data to include a separate column "Representative".

If you want any further help, please clarify what obstacles there are to your putting the data on one worksheet.

Good luck!

 

[icerabbit]

Thank you, JS411.

The template with all data on every page in a fixed number of rows, starting on row three, has remained the same.

What I ended up doing was:

Create new last page with codename (instead of new sheet#)
Disable display refresh
Copy header cells from page one into codename sheet
One range copy from each page into the codename sheet
Create extra row with autosum per every column
Copy header cells from page one below autosum row in codename sheet
Apply filter against blank cells
Sort A-Z
Apply formatting rules (format page, margins, header, lines around cells, bold, align as necessary, apply date formats ...)
Enable display refresh
Rename sheet from codename to sheet with current date yyyy-mm-dd
Protect page against editing



Additional things I'd like, but haven't sampled source code for yet:

- create custom toolbar akin office <2003 with macro button (easy access for the novice user) ... posted question in the forum [ http://www.mrexcel.com/forum/showthread.php?t=535714 ] ... to which I just noticed you replied

- add a control that if a sheet with the current already exists in the beginning, to abort and request it is deleted first -or- run through the whole thing and just name the sheet with a trailing a or b if the date already exists. This in case the user runs the macro twice in the same day, after adding additional data.