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

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


" 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

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


" 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

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


" 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

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


" 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

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


" 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

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


" 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

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


" 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