Macro's, filtering, the result of the filter and copying that cell

[Bob]

Hello,
I've recorded a macro that does almost everything I need it to do. The
problem I'm having is that it copies the exact same cell every time no matter
what the filter does. Of course, when I recorded it, it was correct but when
I work with new data and run the macro, it copies the exact cell from the
time the macro was recorded not the official result of the filters.

I need the macro to copy the results of the filter not the exact same cell
everytime. Here is Visual Basic code that was written during the time of the
recording. The Range of "W15" is where I believe I need the change. Any
help on correcting the problem would be appreciated.

Selection.AutoFilter Field:=1, Criteria1:="400w x 400h"
Selection.AutoFilter Field:=2, Criteria1:="Center facing"
Selection.AutoFilter Field:=3, Criteria1:="Transparent"
Range("W15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Macro Sheet").Select
Regards,

Bob

 

[Ivyleaf]

Hello,  
I've recorded a macro that does almost everything I need it to do.  The
problem I'm having is that it copies the exact same cell every time no matter
what the filter does.  Of course, when I recorded it, it was correct butwhen
I work with new data and run the macro, it copies the exact cell from the
time the macro was recorded not the official result of the filters.

I need the macro to copy the results of the filter not the exact same cell
everytime.  Here is Visual Basic code that was written during the time of the
recording.  The Range of "W15" is where I believe I need the change.  Any
help on correcting the problem would be appreciated.  

    Selection.AutoFilter Field:=1, Criteria1:="400w x 400h"
    Selection.AutoFilter Field:=2, Criteria1:="Center facing"
    Selection.AutoFilter Field:=3, Criteria1:="Transparent"
    Range("W15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Sheet").Select
Regards,

Bob

Hi Bob,

Would I be correct in assuming that once you have applied the three
filters, there is only one row left visible and you want you want the
value from that row in column W?

Cheers,
Ivan.

 

[Bob]

Hi Ivan,

Yes, most of the time it would only return one. Occassionally two results
but I'm ok with choosing the top one of the two.

Thanks,

Bob

 

[Ivyleaf]

Hi Ivan,

Yes, most of the time it would only return one.  Occassionally two results
but I'm ok with choosing the top one of the two.  

Thanks,

Bob








- Show quoted text -

Hi Bob,

Try this:

Sub filter()
Selection.AutoFilter Field:=1, Criteria1:="400w x 400h"
Selection.AutoFilter Field:=2, Criteria1:="Center facing"
Selection.AutoFilter Field:=3, Criteria1:="Transparent"
Range(Range("W2"), Range("W65536").End(xlUp)) _
.SpecialCells(xlCellTypeVisible).Cells(1).Select
Selection.Copy
End Sub

Are you aware that this macro will give an error if you haven't
selected a cell in the table before you run it? If you want to fix
that, just change the "Selection.AutoFilter" to Range("A1").AutoFilter
ensuring of course that cell A1 is in your table, ow use W1 or
something.

Cheers,
Ivan.

 

[Bob]

Hi Ivan...that worked! Thanks!

Hi Bob,

Try this:

Sub filter()
Selection.AutoFilter Field:=1, Criteria1:="400w x 400h"
Selection.AutoFilter Field:=2, Criteria1:="Center facing"
Selection.AutoFilter Field:=3, Criteria1:="Transparent"
Range(Range("W2"), Range("W65536").End(xlUp)) _
.SpecialCells(xlCellTypeVisible).Cells(1).Select
Selection.Copy
End Sub

Are you aware that this macro will give an error if you haven't
selected a cell in the table before you run it? If you want to fix
that, just change the "Selection.AutoFilter" to Range("A1").AutoFilter
ensuring of course that cell A1 is in your table, ow use W1 or
something.

Cheers,
Ivan.

 

[Ivyleaf]

Hi Ivan...that worked!  Thanks!










- Show quoted text -

Hi Bob,

No problem, glad to hear you got it working and thanks for the reply.

Cheers,
Ivan.