automatically sorting data from master list into separate worksheet

Hi,

I am wondering if there is a way to get my data from my master worksheet to automatically sort itself into other appropriate worksheets.

For example, the first entry in the Master worksheet falls under the category of "Ecology" and "Physics" and thus I have marked an X in the columns "Ecology" and "Physics". In marking these columns with an "X" I would like this entry (Date, Event, and Source) to automatically appear in worksheets "Ecology" and "Physics".

Hopefully that makes sense.

If you don't mind explaining to me how you arrive at your solution, that would be appreciated. I'm new to this and would like to learn.

A macro is the easiest way to do this. This macro applies an AUTOFILTER to the Master sheet, it finds all the rows with an "x" in them in the column that matches the NAME of the sheet, then it copies all those matching rows to the sheet.

This macro occurs automatically each time you view any sheet in the workbook. It triggers automatically on every sheet except the MASTER.

Option Explicit

Private Sub Workbook_SheetActivate(ByVal sh As Object)
Dim Col As Long, LR As Long

If sh.Name = "MASTER" Then Exit Sub     'don't run on the MASTER sheet

On Error Resume Next
sh.UsedRange.Clear                      'clear existing data on the sheet
With Sheets("Master")
  'Find the correct column to filter by on the MASTER
    Col = .Rows(1).Find(sh.Name, LookIn:=xlValues, LookAt:=xlWhole).Column
  'if the column is not found, present an error message
    If Col = 0 Then
        MsgBox "The column for this sheet was not found on the Master sheet." _
               & vbLf & "Please check the spellings and try again."
        Exit Sub
    End If
  'If column is found, proceed
    .AutoFilterMode = False                         'reset previous filters
    .Rows(1).AutoFilter                             'turn on the AUTOFILTER
    .Rows(1).AutoFilter Col, "x"                    'filter for "x" in that column
    LR = .Cells(.Rows.Count, Col).End(xlUp).Row     'make sure there are some "x" rows
    If LR > 1 Then _
        .Range("A1:B" & LR & ", P1:P" & LR).Copy    'copy columns A, B and P from MASTER
        sh.Range("A1").PasteSpecial xlPasteColumnWidths     'copy column widths
        sh.Range("A1").PasteSpecial xlPasteAll              'copy values
    .AutoFilterMode = False                         'reset the filter
End With
[A1].Select
End Sub

To insert this macro into your workbook:

1) Press Alt-F11 to open the VBEditor
2) On the left-hand project panel, locate the THISWORKBOOK module and double click it to open it
3) Paste the code given above into the module window that opens
4) Close the VBEditor, and save the workbook.

Now just navigate your workbook... when you look at the PHYSICS sheet, the macro will bring all the physics data in fresh, each time.