With help here, I've written the following macro to take filter
criteria from a selected cell on the source sheet and apply an advanced
filter to a datasheet and put the filtered records on a separate sheet
called "County Records". I've used the same macro, with sheet names and
other things adjusted, on 2 previous source sheets (each source sheet
has a corresponding datasheet).

Sub ReunificationExtract()
'Password used

Dim CtyCode As String
Dim WkSht As Object
Dim PWORD As String
Dim CurRow As Integer
Dim SourceSht As String
Dim Cnt As Integer

Dim CtyCell As Object


PWORD = "dave"
Application.ScreenUpdating = False

HomeSht = ActiveSheet.Name

CurRow = ActiveCell.Row
CtyCode = ActiveSheet.Cells(CurRow, "B")


Set CtyCell = Sheets("Reunification
Records").Columns("c").Find(What:=CtyCode, LookIn:=xlValues)
If Not CtyCell Is Nothing Then

'If Len(Trim(ActiveSheet.Cells(CurRow, "c").Text)) 0 Then
Set WkSht = ActiveWorkbook.Sheets("Reunification Records")

WkSht.Unprotect Password:=PWORD
Sheets("Reunification Records").Range("aa2") = CtyCode
WkSht.Protect Password:=PWORD

Sheets("County Records").Select
Worksheets("County Records").UsedRange.Clear
Range("a1:e1").Merge
Range("a1").FormulaR1C1 = _
"WARNING: This data will be erased the next time
County Records are extracted. "
With Range("a1").Characters(Start:=1, Length:=78).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With

Range("A2:e2").Merge
Range("A2").FormulaR1C1 = _
"If you wish to save the data, copy and paste it to
another spreadsheet or print it before doing another data extraction."
With Range("A2").Characters(Start:=1, Length:=124).Font
.ColorIndex = 3
Rows("2:2").RowHeight = 25
End With
Range("a2").Select
Selection.WrapText = True

Sheets("Reunification
Records").Range("A1:M192").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Reunification
Records").Range("aa1:aa2"), _
CopyToRange:=Range("A5"), Unique:=False
Range("A4:E4").Merge
Range("a4") = CtyCode & " County Reunification Records"
With Range("a4").Characters(Start:=1, Length:=78).Font
.FontStyle = "Bold"
.ColorIndex = 10
.Size = 16
End With

Columns("A:M").EntireColumn.AutoFit
Range("A5:M5").Select

With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Font.Bold = True
End With

Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFormat Format:=xlRangeAutoFormatList2,
Number:=False, Font _
:=True, Alignment:=False, Border:=True, Pattern:=True,
Width:=False

Rows("5:5").RowHeight = 25

Worksheets("Reunification Records").Range("aa5:aa25").Copy
Destination:= _
Worksheets("County Records").Range("a5") _
.End(xlDown).Offset(2, 0)


Worksheets("County Records").Range("a1").Select
Sheets("County Records").Range("aa4").Value = HomeSht
Else
MsgBox "There are no Reunifications for " & CtyCode & " for SFY
2005 2nd Quarter", vbOKOnly
End If
Application.ScreenUpdating = True

End Sub

For some reason, on the third set of sheets, the macro works with any
of the first 16 rows of the source sheet selected, but hangs up when
any of the rows below 16 are selected (I'm not sure if this is
relevant, just giving all my observations). I've done watches and
stepped through the macro, both on data where it works, and on data
(below row 16) where it doesn't work. For some reason, when it doesn't
run, it doesn't find any filterable data in the source sheet, even when
there's plenty of it there. I've run advanced filter using criteria
copied from the source sheet and it filters just fine. Why does this
macro run on some data in the source sheet, but not on other
selections? Why does it run in 2 other sheets but not, in some cases,
in this one?
I'd appreciate any help. I know the code is crude, I'm very new at VBA.
I'm sure it can be written much more efficiently, and would be
interested in feedback on that as well. But I'm mostly concerned about
why it's hanging up as is so I can learn from my mistakes.
Thanks!