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

 

[SudokuKing]

If you want a quick solution, here's 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):

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")
i = StRow
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
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