excel vba - filters and ranges

[[email protected]]

I have a list of transactions to which I add records daily. I want to
to filter and extract records to another location on the spreadsheet
based on user defined criteria. My problem is that the code initially
selects the correct range but range references for the filter do not
appear to change as records are not does the range for filter critieria
adjust to reflect the addition of records. Obviously I am new to VBA
but help would be appreciated.

File format
Date CP Notional Cur Type Rate
28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
28-Jul-06 BNS 1,500,000 USD FWD 1.1250


Filter Criteria
Date CP
28-Jul-06 RBC


Code
Sub Macro1()
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
unique:=False
End Sub

[Jim Thomlinson]

So the issue is that you are inserting rows and moving your criteria range
every time you insert rows? I assume you are also having an issue in that the
paste area needs to be incremented as you insert rows? It is a little hard to
tell from your post...
--
HTH...

Jim Thomlinson


"[email protected]" wrote:

> I have a list of transactions to which I add records daily. I want to
> to filter and extract records to another location on the spreadsheet
> based on user defined criteria. My problem is that the code initially
> selects the correct range but range references for the filter do not
> appear to change as records are not does the range for filter critieria
> adjust to reflect the addition of records. Obviously I am new to VBA
> but help would be appreciated.
>
> File format
> Date CP Notional Cur Type Rate
> 28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
> 28-Jul-06 BNS 1,500,000 USD FWD 1.1250
>
>
> Filter Criteria
> Date CP
> 28-Jul-06 RBC
>
>
> Code
> Sub Macro1()
> Range("A1").Select
> Range(Selection, Selection.End(xlToRight)).Select
> Range(Selection, Selection.End(xlDown)).Select
> Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
> CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
> unique:=False
> End Sub
>
>

[dbuc283]

Yes. As I add another row the criteria range moves down another row but
macro does update the change . In additon the range used for the
filter....("A1:I4").AdvancedFilter does not capture the additonal row.
The paste area will also have to change.


Jim Thomlinson wrote:
> So the issue is that you are inserting rows and moving your criteria range
> every time you insert rows? I assume you are also having an issue in that the
> paste area needs to be incremented as you insert rows? It is a little hard to
> tell from your post...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "[email protected]" wrote:
>
> > I have a list of transactions to which I add records daily. I want to
> > to filter and extract records to another location on the spreadsheet
> > based on user defined criteria. My problem is that the code initially
> > selects the correct range but range references for the filter do not
> > appear to change as records are not does the range for filter critieria
> > adjust to reflect the addition of records. Obviously I am new to VBA
> > but help would be appreciated.
> >
> > File format
> > Date CP Notional Cur Type Rate
> > 28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
> > 28-Jul-06 BNS 1,500,000 USD FWD 1.1250
> >
> >
> > Filter Criteria
> > Date CP
> > 28-Jul-06 RBC
> >
> >
> > Code
> > Sub Macro1()
> > Range("A1").Select
> > Range(Selection, Selection.End(xlToRight)).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
> > CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
> > unique:=False
> > End Sub
> >
> >

[Jim Thomlinson]

Give this code a try. You will need to add a named range for the criteria
range. select the entire Criteria Range (A7:C8) and In the drop down just
above Column A where it says A7 place your cursor and overwrite it to the
word Criteria. This code should then work for you... (Extract is a named
range created automatically for you on an advanced filter copy, so if you
select A10 you will see the word exctract in the cell reference aboce column
A)...

Sub Macro1()
Dim rngToFilter As Range

Set rngToFilter = Range(Range("A1"),
Range("A1").End(xlToRight).End(xlDown))
rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), _
CopyToRange:=Range("Extract"), _
Unique:=False
End Sub

P.S. Say hi to my sister Michelle Thomlinson for me. She works for Agrium
too... I am off to a meeting for a while. I will check back a little later to
see how things are going for you...
--
HTH...

Jim Thomlinson


"dbuc283" wrote:

> Yes. As I add another row the criteria range moves down another row but
> macro does update the change . In additon the range used for the
> filter....("A1:I4").AdvancedFilter does not capture the additonal row.
> The paste area will also have to change.
>
>
> Jim Thomlinson wrote:
> > So the issue is that you are inserting rows and moving your criteria range
> > every time you insert rows? I assume you are also having an issue in that the
> > paste area needs to be incremented as you insert rows? It is a little hard to
> > tell from your post...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "[email protected]" wrote:
> >
> > > I have a list of transactions to which I add records daily. I want to
> > > to filter and extract records to another location on the spreadsheet
> > > based on user defined criteria. My problem is that the code initially
> > > selects the correct range but range references for the filter do not
> > > appear to change as records are not does the range for filter critieria
> > > adjust to reflect the addition of records. Obviously I am new to VBA
> > > but help would be appreciated.
> > >
> > > File format
> > > Date CP Notional Cur Type Rate
> > > 28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
> > > 28-Jul-06 BNS 1,500,000 USD FWD 1.1250
> > >
> > >
> > > Filter Criteria
> > > Date CP
> > > 28-Jul-06 RBC
> > >
> > >
> > > Code
> > > Sub Macro1()
> > > Range("A1").Select
> > > Range(Selection, Selection.End(xlToRight)).Select
> > > Range(Selection, Selection.End(xlDown)).Select
> > > Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
> > > CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
> > > unique:=False
> > > End Sub
> > >
> > >
>
>

[dbuc283]

Thanks again. When i paste the code I get an error highlighted in red
for the following portion. Unfortunately, I am not able to resilve the
issue due to my inexperience

Set rngToFilter = Range(Range("A1"),
Range("A1").End(xlToRight).End(xlDown))

Jim Thomlinson wrote:
> Give this code a try. You will need to add a named range for the criteria
> range. select the entire Criteria Range (A7:C8) and In the drop down just
> above Column A where it says A7 place your cursor and overwrite it to the
> word Criteria. This code should then work for you... (Extract is a named
> range created automatically for you on an advanced filter copy, so if you
> select A10 you will see the word exctract in the cell reference aboce column
> A)...
>
> Sub Macro1()
> Dim rngToFilter As Range
>
> Set rngToFilter = Range(Range("A1"),
> Range("A1").End(xlToRight).End(xlDown))
> rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
> CriteriaRange:=Range("Criteria"), _
> CopyToRange:=Range("Extract"), _
> Unique:=False
> End Sub
>
> P.S. Say hi to my sister Michelle Thomlinson for me. She works for Agrium
> too... I am off to a meeting for a while. I will check back a little later to
> see how things are going for you...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "dbuc283" wrote:
>
> > Yes. As I add another row the criteria range moves down another row but
> > macro does update the change . In additon the range used for the
> > filter....("A1:I4").AdvancedFilter does not capture the additonal row.
> > The paste area will also have to change.
> >
> >
> > Jim Thomlinson wrote:
> > > So the issue is that you are inserting rows and moving your criteria range
> > > every time you insert rows? I assume you are also having an issue in that the
> > > paste area needs to be incremented as you insert rows? It is a little hard to
> > > tell from your post...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "[email protected]" wrote:
> > >
> > > > I have a list of transactions to which I add records daily. I want to
> > > > to filter and extract records to another location on the spreadsheet
> > > > based on user defined criteria. My problem is that the code initially
> > > > selects the correct range but range references for the filter do not
> > > > appear to change as records are not does the range for filter critieria
> > > > adjust to reflect the addition of records. Obviously I am new to VBA
> > > > but help would be appreciated.
> > > >
> > > > File format
> > > > Date CP Notional Cur Type Rate
> > > > 28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
> > > > 28-Jul-06 BNS 1,500,000 USD FWD 1.1250
> > > >
> > > >
> > > > Filter Criteria
> > > > Date CP
> > > > 28-Jul-06 RBC
> > > >
> > > >
> > > > Code
> > > > Sub Macro1()
> > > > Range("A1").Select
> > > > Range(Selection, Selection.End(xlToRight)).Select
> > > > Range(Selection, Selection.End(xlDown)).Select
> > > > Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
> > > > CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
> > > > unique:=False
> > > > End Sub
> > > >
> > > >
> >
> >

[dbuc283]

Thanks again. When i paste the code I get an error highlighted in red
for the following portion. Unfortunately, I am not able to resilve the
issue due to my inexperience

Set rngToFilter = Range(Range("A1"),
Range("A1").End(xlToRight).End(xlDown))

Jim Thomlinson wrote:
> Give this code a try. You will need to add a named range for the criteria
> range. select the entire Criteria Range (A7:C8) and In the drop down just
> above Column A where it says A7 place your cursor and overwrite it to the
> word Criteria. This code should then work for you... (Extract is a named
> range created automatically for you on an advanced filter copy, so if you
> select A10 you will see the word exctract in the cell reference aboce column
> A)...
>
> Sub Macro1()
> Dim rngToFilter As Range
>
> Set rngToFilter = Range(Range("A1"),
> Range("A1").End(xlToRight).End(xlDown))
> rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
> CriteriaRange:=Range("Criteria"), _
> CopyToRange:=Range("Extract"), _
> Unique:=False
> End Sub
>
> P.S. Say hi to my sister Michelle Thomlinson for me. She works for Agrium
> too... I am off to a meeting for a while. I will check back a little later to
> see how things are going for you...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "dbuc283" wrote:
>
> > Yes. As I add another row the criteria range moves down another row but
> > macro does update the change . In additon the range used for the
> > filter....("A1:I4").AdvancedFilter does not capture the additonal row.
> > The paste area will also have to change.
> >
> >
> > Jim Thomlinson wrote:
> > > So the issue is that you are inserting rows and moving your criteria range
> > > every time you insert rows? I assume you are also having an issue in that the
> > > paste area needs to be incremented as you insert rows? It is a little hard to
> > > tell from your post...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "[email protected]" wrote:
> > >
> > > > I have a list of transactions to which I add records daily. I want to
> > > > to filter and extract records to another location on the spreadsheet
> > > > based on user defined criteria. My problem is that the code initially
> > > > selects the correct range but range references for the filter do not
> > > > appear to change as records are not does the range for filter critieria
> > > > adjust to reflect the addition of records. Obviously I am new to VBA
> > > > but help would be appreciated.
> > > >
> > > > File format
> > > > Date CP Notional Cur Type Rate
> > > > 28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
> > > > 28-Jul-06 BNS 1,500,000 USD FWD 1.1250
> > > >
> > > >
> > > > Filter Criteria
> > > > Date CP
> > > > 28-Jul-06 RBC
> > > >
> > > >
> > > > Code
> > > > Sub Macro1()
> > > > Range("A1").Select
> > > > Range(Selection, Selection.End(xlToRight)).Select
> > > > Range(Selection, Selection.End(xlDown)).Select
> > > > Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
> > > > CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
> > > > unique:=False
> > > > End Sub
> > > >
> > > >
> >
> >

[Jim Thomlinson]

Sorry the Window wrapped the text... It should all be on one line in your
code window... or use this...

Sub Macro1()
Dim rngToFilter As Range

Set rngToFilter = Range(Range("A1"), _
Range("A1").End(xlToRight).End(xlDown))
rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), _
CopyToRange:=Range("Extract"), _
Unique:=False
End Sub

PS... My sister is Michelle Nutting... you would think I would know her
married name...
--
HTH...

Jim Thomlinson


"dbuc283" wrote:

> Thanks again. When i paste the code I get an error highlighted in red
> for the following portion. Unfortunately, I am not able to resilve the
> issue due to my inexperience
>
> Set rngToFilter = Range(Range("A1"),
> Range("A1").End(xlToRight).End(xlDown))
>
> Jim Thomlinson wrote:
> > Give this code a try. You will need to add a named range for the criteria
> > range. select the entire Criteria Range (A7:C8) and In the drop down just
> > above Column A where it says A7 place your cursor and overwrite it to the
> > word Criteria. This code should then work for you... (Extract is a named
> > range created automatically for you on an advanced filter copy, so if you
> > select A10 you will see the word exctract in the cell reference aboce column
> > A)...
> >
> > Sub Macro1()
> > Dim rngToFilter As Range
> >
> > Set rngToFilter = Range(Range("A1"),
> > Range("A1").End(xlToRight).End(xlDown))
> > rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
> > CriteriaRange:=Range("Criteria"), _
> > CopyToRange:=Range("Extract"), _
> > Unique:=False
> > End Sub
> >
> > P.S. Say hi to my sister Michelle Thomlinson for me. She works for Agrium
> > too... I am off to a meeting for a while. I will check back a little later to
> > see how things are going for you...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "dbuc283" wrote:
> >
> > > Yes. As I add another row the criteria range moves down another row but
> > > macro does update the change . In additon the range used for the
> > > filter....("A1:I4").AdvancedFilter does not capture the additonal row.
> > > The paste area will also have to change.
> > >
> > >
> > > Jim Thomlinson wrote:
> > > > So the issue is that you are inserting rows and moving your criteria range
> > > > every time you insert rows? I assume you are also having an issue in that the
> > > > paste area needs to be incremented as you insert rows? It is a little hard to
> > > > tell from your post...
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "[email protected]" wrote:
> > > >
> > > > > I have a list of transactions to which I add records daily. I want to
> > > > > to filter and extract records to another location on the spreadsheet
> > > > > based on user defined criteria. My problem is that the code initially
> > > > > selects the correct range but range references for the filter do not
> > > > > appear to change as records are not does the range for filter critieria
> > > > > adjust to reflect the addition of records. Obviously I am new to VBA
> > > > > but help would be appreciated.
> > > > >
> > > > > File format
> > > > > Date CP Notional Cur Type Rate
> > > > > 28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
> > > > > 28-Jul-06 BNS 1,500,000 USD FWD 1.1250
> > > > >
> > > > >
> > > > > Filter Criteria
> > > > > Date CP
> > > > > 28-Jul-06 RBC
> > > > >
> > > > >
> > > > > Code
> > > > > Sub Macro1()
> > > > > Range("A1").Select
> > > > > Range(Selection, Selection.End(xlToRight)).Select
> > > > > Range(Selection, Selection.End(xlDown)).Select
> > > > > Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
> > > > > CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
> > > > > unique:=False
> > > > > End Sub
> > > > >
> > > > >
> > >
> > >
>
>

[dbuc283]

I have not met your sis - i am in Treasury but i will drop by. I named
the range but the code results in a "400 error" or a run time error
"1004 with application defined or object defined error" if I step
through it. Too bad you cannot attach files in the forum.

Jim Thomlinson wrote:
> Sorry the Window wrapped the text... It should all be on one line in your
> code window... or use this...
>
> Sub Macro1()
> Dim rngToFilter As Range
>
> Set rngToFilter = Range(Range("A1"), _
> Range("A1").End(xlToRight).End(xlDown))
> rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
> CriteriaRange:=Range("Criteria"), _
> CopyToRange:=Range("Extract"), _
> Unique:=False
> End Sub
>
> PS... My sister is Michelle Nutting... you would think I would know her
> married name...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "dbuc283" wrote:
>
> > Thanks again. When i paste the code I get an error highlighted in red
> > for the following portion. Unfortunately, I am not able to resilve the
> > issue due to my inexperience
> >
> > Set rngToFilter = Range(Range("A1"),
> > Range("A1").End(xlToRight).End(xlDown))
> >
> > Jim Thomlinson wrote:
> > > Give this code a try. You will need to add a named range for the criteria
> > > range. select the entire Criteria Range (A7:C8) and In the drop down just
> > > above Column A where it says A7 place your cursor and overwrite it to the
> > > word Criteria. This code should then work for you... (Extract is a named
> > > range created automatically for you on an advanced filter copy, so if you
> > > select A10 you will see the word exctract in the cell reference aboce column
> > > A)...
> > >
> > > Sub Macro1()
> > > Dim rngToFilter As Range
> > >
> > > Set rngToFilter = Range(Range("A1"),
> > > Range("A1").End(xlToRight).End(xlDown))
> > > rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
> > > CriteriaRange:=Range("Criteria"), _
> > > CopyToRange:=Range("Extract"), _
> > > Unique:=False
> > > End Sub
> > >
> > > P.S. Say hi to my sister Michelle Thomlinson for me. She works for Agrium
> > > too... I am off to a meeting for a while. I will check back a little later to
> > > see how things are going for you...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "dbuc283" wrote:
> > >
> > > > Yes. As I add another row the criteria range moves down another row but
> > > > macro does update the change . In additon the range used for the
> > > > filter....("A1:I4").AdvancedFilter does not capture the additonal row.
> > > > The paste area will also have to change.
> > > >
> > > >
> > > > Jim Thomlinson wrote:
> > > > > So the issue is that you are inserting rows and moving your criteria range
> > > > > every time you insert rows? I assume you are also having an issue in that the
> > > > > paste area needs to be incremented as you insert rows? It is a little hard to
> > > > > tell from your post...
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "[email protected]" wrote:
> > > > >
> > > > > > I have a list of transactions to which I add records daily. I want to
> > > > > > to filter and extract records to another location on the spreadsheet
> > > > > > based on user defined criteria. My problem is that the code initially
> > > > > > selects the correct range but range references for the filter do not
> > > > > > appear to change as records are not does the range for filter critieria
> > > > > > adjust to reflect the addition of records. Obviously I am new to VBA
> > > > > > but help would be appreciated.
> > > > > >
> > > > > > File format
> > > > > > Date CP Notional Cur Type Rate
> > > > > > 28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
> > > > > > 28-Jul-06 BNS 1,500,000 USD FWD 1.1250
> > > > > >
> > > > > >
> > > > > > Filter Criteria
> > > > > > Date CP
> > > > > > 28-Jul-06 RBC
> > > > > >
> > > > > >
> > > > > > Code
> > > > > > Sub Macro1()
> > > > > > Range("A1").Select
> > > > > > Range(Selection, Selection.End(xlToRight)).Select
> > > > > > Range(Selection, Selection.End(xlDown)).Select
> > > > > > Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
> > > > > > CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
> > > > > > unique:=False
> > > > > > End Sub
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >

[Jim Thomlinson]

I just e-mailed you a spreadsheet showing you what I have... It could be an
issue with the named ranges most likely... Let me know if you don't get it or
if you have any problems with it...
--
HTH...

Jim Thomlinson


"dbuc283" wrote:

> I have not met your sis - i am in Treasury but i will drop by. I named
> the range but the code results in a "400 error" or a run time error
> "1004 with application defined or object defined error" if I step
> through it. Too bad you cannot attach files in the forum.
>
> Jim Thomlinson wrote:
> > Sorry the Window wrapped the text... It should all be on one line in your
> > code window... or use this...
> >
> > Sub Macro1()
> > Dim rngToFilter As Range
> >
> > Set rngToFilter = Range(Range("A1"), _
> > Range("A1").End(xlToRight).End(xlDown))
> > rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
> > CriteriaRange:=Range("Criteria"), _
> > CopyToRange:=Range("Extract"), _
> > Unique:=False
> > End Sub
> >
> > PS... My sister is Michelle Nutting... you would think I would know her
> > married name...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "dbuc283" wrote:
> >
> > > Thanks again. When i paste the code I get an error highlighted in red
> > > for the following portion. Unfortunately, I am not able to resilve the
> > > issue due to my inexperience
> > >
> > > Set rngToFilter = Range(Range("A1"),
> > > Range("A1").End(xlToRight).End(xlDown))
> > >
> > > Jim Thomlinson wrote:
> > > > Give this code a try. You will need to add a named range for the criteria
> > > > range. select the entire Criteria Range (A7:C8) and In the drop down just
> > > > above Column A where it says A7 place your cursor and overwrite it to the
> > > > word Criteria. This code should then work for you... (Extract is a named
> > > > range created automatically for you on an advanced filter copy, so if you
> > > > select A10 you will see the word exctract in the cell reference aboce column
> > > > A)...
> > > >
> > > > Sub Macro1()
> > > > Dim rngToFilter As Range
> > > >
> > > > Set rngToFilter = Range(Range("A1"),
> > > > Range("A1").End(xlToRight).End(xlDown))
> > > > rngToFilter.AdvancedFilter Action:=xlFilterCopy, _
> > > > CriteriaRange:=Range("Criteria"), _
> > > > CopyToRange:=Range("Extract"), _
> > > > Unique:=False
> > > > End Sub
> > > >
> > > > P.S. Say hi to my sister Michelle Thomlinson for me. She works for Agrium
> > > > too... I am off to a meeting for a while. I will check back a little later to
> > > > see how things are going for you...
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "dbuc283" wrote:
> > > >
> > > > > Yes. As I add another row the criteria range moves down another row but
> > > > > macro does update the change . In additon the range used for the
> > > > > filter....("A1:I4").AdvancedFilter does not capture the additonal row.
> > > > > The paste area will also have to change.
> > > > >
> > > > >
> > > > > Jim Thomlinson wrote:
> > > > > > So the issue is that you are inserting rows and moving your criteria range
> > > > > > every time you insert rows? I assume you are also having an issue in that the
> > > > > > paste area needs to be incremented as you insert rows? It is a little hard to
> > > > > > tell from your post...
> > > > > > --
> > > > > > HTH...
> > > > > >
> > > > > > Jim Thomlinson
> > > > > >
> > > > > >
> > > > > > "[email protected]" wrote:
> > > > > >
> > > > > > > I have a list of transactions to which I add records daily. I want to
> > > > > > > to filter and extract records to another location on the spreadsheet
> > > > > > > based on user defined criteria. My problem is that the code initially
> > > > > > > selects the correct range but range references for the filter do not
> > > > > > > appear to change as records are not does the range for filter critieria
> > > > > > > adjust to reflect the addition of records. Obviously I am new to VBA
> > > > > > > but help would be appreciated.
> > > > > > >
> > > > > > > File format
> > > > > > > Date CP Notional Cur Type Rate
> > > > > > > 28-Jul-06 RBC 1,972,950 EUR SWAP 1.4360
> > > > > > > 28-Jul-06 BNS 1,500,000 USD FWD 1.1250
> > > > > > >
> > > > > > >
> > > > > > > Filter Criteria
> > > > > > > Date CP
> > > > > > > 28-Jul-06 RBC
> > > > > > >
> > > > > > >
> > > > > > > Code
> > > > > > > Sub Macro1()
> > > > > > > Range("A1").Select
> > > > > > > Range(Selection, Selection.End(xlToRight)).Select
> > > > > > > Range(Selection, Selection.End(xlDown)).Select
> > > > > > > Range("A1:I4").AdvancedFilter Action:=xlFilterCopy,
> > > > > > > CriteriaRange:=Range( _"A7:C8"), CopyToRange:=Range("A10"),
> > > > > > > unique:=False
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
>