Sort Multiple Sheets by Category

[nmccart]

Hey, I was wondering if you Excel Gurus could give me a hand. My mother is making a list (in Excel) of all of the books in the church library. The church would like to see each book sorted by titles on a tab for each letter of the alphabet. So, she has created a workbook with 26 worksheets, each one is labeled with the letter of the alphabet (A, B, C, etc). On each sheet, the Title is in Column A, the Author is in Column B, and the Book Categoy is in Column C.

Is there a function that can be written to go through each sheet (A - Z), and sort the data on a summary sheet (a 27th tab) by the Category (Column C of each sheet)?

If this is possible, I'd love to see how. My Excel programming knowledge is just enough that I know this could be done, but I am not sure how to work it.

[Dave Peterson]

First, don't do it.

Apply Data|Filter|Autofilter to that range.

You can use a custom filter (begins with) to show the letters you want. You
could even use another column and extract the first character of the cell:

=left(a2,1)
(for example).

You're life will be much easier if you keep all your data in one single
location.

=========
But if you want...

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

nmccart wrote:
>
> Hey, I was wondering if you Excel Gurus could give me a hand. My mother
> is making a list (in Excel) of all of the books in the church library.
> The church would like to see each book sorted by titles on a tab for
> each letter of the alphabet. So, she has created a workbook with 26
> worksheets, each one is labeled with the letter of the alphabet (A, B,
> C, etc). On each sheet, the Title is in Column A, the Author is in
> Column B, and the Book Categoy is in Column C.
>
> Is there a function that can be written to go through each sheet (A -
> Z), and sort the data on a summary sheet (a 27th tab) by the Category
> (Column C of each sheet)?
>
> If this is possible, I'd love to see how. My Excel programming
> knowledge is just enough that I know this could be done, but I am not
> sure how to work it.
>
> --
> nmccart
> ------------------------------------------------------------------------
> nmccart's Profile: http://www.excelforum.com/member.php...o&userid=35870
> View this thread: http://www.excelforum.com/showthread...hreadid=556622

--

Dave Peterson

[SudokuKing]

Here's an alternative solution: a brute force macro that should do the trick (it doesn't do an alphabetical sort - it just dumps everything that starts with "A" into sheet "A", and so forth, so perform a sort first). As Dave suggested, this is not the best way to do it, but it will get the job done.

Sub SortCategories()
'Takes values from Range A, B, & C of "Summary Sheet" and
'sorts them alphabetically onto sheets labeled "A", "B",....
'NOTE: Runs down column A of "summary sheet" until it
'encounters an empty cell, then stops.

'Assumes:
'Title is in Column A, the Author is in Column B, and Category is in Column C.
'Worksheet with unsorted data is called "Summary Sheet"
Dim i As Long, j As Long
Dim SummarySh As Worksheet
Dim PasteSh As Worksheet
Dim StRow As Integer
Dim PasteRow As Long

'Change StRow to the number of the first row containing data
StRow = 2
'Change "Summary sheet" to the name of the workbook with data
Set SummarySh = ActiveWorkbook.Worksheets("Summary Sheet")

For i = 1 To 26
Sheets(ColumnLetter(i)).Range("A1").Value = "Title"
Sheets(ColumnLetter(i)).Range("B1").Value = "Author"
Sheets(ColumnLetter(i)).Range("C1").Value = "Category"
Next i
i = StRow
Do While (SummarySh.Range("A" & i).Value <> "")
Set PasteSh = Sheets(Left(SummarySh.Range("A" & i).Value, 1))
MsgBox PasteSh.Name
PasteRow = PasteSh.Range("A1").End(xlDown).Row + 1
If PasteRow = 65537 Then PasteRow = 2
PasteSh.Range("A" & PasteRow) = SummarySh.Range("A" & i)
PasteSh.Range("B" & PasteRow) = SummarySh.Range("B" & i)
PasteSh.Range("C" & PasteRow) = SummarySh.Range("C" & i)
i = i + 1
Loop

End Sub

Function ColumnLetter(ByVal ColumnNumber As Integer) As String
If ColumnNumber > 26 Then
ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function