Thanks, Bob.
It's actually 2 macros. The first gets data selected by the user and
identifies which sheet to get the records from (RecSht). Then it calls
the other macro which gets the appropriate records from RecSht and
pastes them to yet another sheet (CntyRec).
I've omitted the rest of the second macro for simplicity.
Public CurRow As Integer
Public RecSht As Object 'Records sheet
Public FilterRange As Range
Sub InCareExtr()
'Password used
Set CtyExtr = ActiveWorkbook.Sheets("County Extract")
'Application.ScreenUpdating = False
HomeSht = ActiveSheet.Name
CurRow = ActiveCell.Row
CtyCode = ActiveSheet.Cells(CurRow, "B")
If Len(Trim(ActiveSheet.Cells(CurRow, "E").Text)) > 0 Then
Set RecSht = ActiveWorkbook.Sheets("In Care Records")
AllExtract
Else
MsgBox "There are no In Care for " & CtyCode & " for SFY 2005
2nd Quarter", vbOKOnly
End If
End Sub
Const PWORD As String = "dave"
Sub AllExtract()
CtyExtr.Select
With RecSht
.Unprotect Password:=PWORD
.Range("aa2") = CtyCode
End With
CtyExtr.Select
CtyExtr.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
Rows("1:1").RowHeight = 25
Range("a1").WrapText = True
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
End With
Range("a2").Select
Selection.WrapText = True
'RecSht.Select
'Set FilterRange = Range("a1").CurrentRegion
CtyExtr.Select
RecSht.Range("A1:U4000").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _
CopyToRange:=Range("A5"), Unique:=False
' Set FilterRange = RecSht.Range("A1").CurrentRegion
' CtyExtr.Select
' FilterRange.AdvancedFilter Action:= _
' xlFilterCopy, CriteriaRange:=RecSht.Range("AA1:AA2"), _
' CopyToRange:=Range("A5"), Unique:=False
The remarked out code is your last suggestion, which still gives an
Object not defined error. The other code works, using the range
A1:U4000, that being the largest filter range of any of the datasheets
involved at this time. But the data will be updated quarterly, and I
don't want to have to remember to edit this area in case next quarter's
data is larger than the current region. There must be a way to tell XL
just to filter the current region, but this has been driving me nuts
for a couple of weeks! I tried using a range name, but that just seemed
to complicate matters significantly more.
Any ideas?
