What object is it looking for?

[davegb]

I've inquired about this before, but it still isn't working. I'm trying
to get XL to determine which cells are to be filtered by using the
CurrentRegion method (or whatever it is). The filter range varies from
datasheet to datasheet. I've declared FilterRange as a range, but can't
get XL to set it equal to the current region from cell A1. Any ideas
how to do this?

RecSht.Select
Range("a1").Select

Set FilterRange = Selection.CurrentRegion.Select<-----Object
required error

CtyExtr.Select
RecSht.Range("FilterRange").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _
CopyToRange:=Range("A5"), Unique:=False

Thanks again!

[Bob Phillips]

RecSht.Select


Set FilterRange = Range("a1").CurrentRegion
CtyExtr.Select
RecSht.Range("FilterRange").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _
CopyToRange:=Range("A5"), Unique:=False


--
HTH

Bob Phillips

"davegb" <davegb@safebrowse.com> wrote in message
news:1118673576.842259.195580@o13g2000cwo.googlegroups.com...
> I've inquired about this before, but it still isn't working. I'm trying
> to get XL to determine which cells are to be filtered by using the
> CurrentRegion method (or whatever it is). The filter range varies from
> datasheet to datasheet. I've declared FilterRange as a range, but can't
> get XL to set it equal to the current region from cell A1. Any ideas
> how to do this?
>
> RecSht.Select
> Range("a1").Select
>
> Set FilterRange = Selection.CurrentRegion.Select<-----Object
> required error
>
> CtyExtr.Select
> RecSht.Range("FilterRange").AdvancedFilter Action:= _
> xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _
> CopyToRange:=Range("A5"), Unique:=False
>
> Thanks again!
>

[davegb]

Thanks for your reply, Bob.
I changed the macro, and am not getting an "application or object
defined error" when I apply the filter. Any suggestions? By the way, I
had a watch on FilterRange and there still is no value. Does that tell
us anything?

[davegb]

Should have read "now getting" instead of "not getting"
davegb wrote:
> Thanks for your reply, Bob.
> I changed the macro, and am not getting an "application or object
> defined error" when I apply the filter. Any suggestions? By the way, I
> had a watch on FilterRange and there still is no value. Does that tell
> us anything?

[Bob Phillips]

Difficult to test as I don't see the data, but try this

Set FilterRange = RecSht.Range("A1").CurrentRegion
CtyExtr.Select
FilterRange.AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=RecSht.Range("AA1:AA2"), _
CopyToRange:=Range("A5"), Unique:=False


--
HTH

Bob Phillips

"davegb" <davegb@safebrowse.com> wrote in message
news:1118675944.349856.235580@z14g2000cwz.googlegroups.com...
>
> Should have read "now getting" instead of "not getting"
> davegb wrote:
> > Thanks for your reply, Bob.
> > I changed the macro, and am not getting an "application or object
> > defined error" when I apply the filter. Any suggestions? By the way, I
> > had a watch on FilterRange and there still is no value. Does that tell
> > us anything?
>

[davegb]

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 =3D ActiveWorkbook.Sheets("County Extract")

'Application.ScreenUpdating =3D False

HomeSht =3D ActiveSheet.Name

CurRow =3D ActiveCell.Row
CtyCode =3D ActiveSheet.Cells(CurRow, "B")
If Len(Trim(ActiveSheet.Cells(CurRow, "E").Text)) > 0 Then

Set RecSht =3D 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 =3D "dave"
Sub AllExtract()
CtyExtr.Select

With RecSht
.Unprotect Password:=3DPWORD
.Range("aa2") =3D CtyCode

End With

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

Rows("1:1").RowHeight =3D 25
Range("a1").WrapText =3D True

Range("A2:e2").Merge
Range("A2").FormulaR1C1 =3D _
"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:=3D1, Length:=3D124).Font
.ColorIndex =3D 3
End With

Range("a2").Select
Selection.WrapText =3D True

'RecSht.Select
'Set FilterRange =3D Range("a1").CurrentRegion

CtyExtr.Select
RecSht.Range("A1:U4000").AdvancedFilter Action:=3D _
xlFilterCopy, CriteriaRange:=3DRecSht.Range("aa1:aa2"), _
CopyToRange:=3DRange("A5"), Unique:=3DFalse


' Set FilterRange =3D RecSht.Range("A1").CurrentRegion
' CtyExtr.Select
' FilterRange.AdvancedFilter Action:=3D _
' xlFilterCopy, CriteriaRange:=3DRecSht.Range("A=ADA1:AA2"), _
' CopyToRange:=3DRange("A5"), Unique:=3DFalse

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.=20
Any ideas?

[davegb]

I found the problem! There was one of those irritating little dashes
that are inserted in the code when it's copied from Google into a
module. It's working now. Thanks for your help, Bob!

[Dave Peterson]

Ron de Bruin forced me to send a message to Google.

http://groups-beta.google.com/support/bin/request.py

Maybe if enough people complain, ...

davegb wrote:
>
> I found the problem! There was one of those irritating little dashes
> that are inserted in the code when it's copied from Google into a
> module. It's working now. Thanks for your help, Bob!

--

Dave Peterson

[davegb]

Just sent my complaint!

Dave Peterson wrote:
> Ron de Bruin forced me to send a message to Google.
>
> http://groups-beta.google.com/support/bin/request.py
>
> Maybe if enough people complain, ...
>
> davegb wrote:
> >
> > I found the problem! There was one of those irritating little dashes
> > that are inserted in the code when it's copied from Google into a
> > module. It's working now. Thanks for your help, Bob!
>
> --
>
> Dave Peterson

[Dave Peterson]

You're gonna get a reply back from Google, too.

davegb wrote:
>
> Just sent my complaint!
>
> Dave Peterson wrote:
> > Ron de Bruin forced me to send a message to Google.
> >
> > http://groups-beta.google.com/support/bin/request.py
> >
> > Maybe if enough people complain, ...
> >
> > davegb wrote:
> > >
> > > I found the problem! There was one of those irritating little dashes
> > > that are inserted in the code when it's copied from Google into a
> > > module. It's working now. Thanks for your help, Bob!
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson