| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro's, filtering, the result of the filter and copying that cell
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro's, filtering, the result of the filter and copying thatcell
On Apr 5, 3:02*am, Bob wrote:
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 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro's, filtering, the result of the filter and copying that
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" wrote: On Apr 5, 3:02 am, Bob wrote: 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 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro's, filtering, the result of the filter and copying that
On Apr 5, 3:51*am, Bob wrote:
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" wrote: On Apr 5, 3:02 am, Bob wrote: 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 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.- Hide quoted text - - 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro's, filtering, the result of the filter and copying that
Hi Ivan...that worked! Thanks!
"Ivyleaf" wrote: On Apr 5, 3:51 am, Bob wrote: 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" wrote: On Apr 5, 3:02 am, Bob wrote: 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 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.- Hide quoted text - - 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro's, filtering, the result of the filter and copying that
On Apr 5, 10:06*am, Bob wrote:
Hi Ivan...that worked! *Thanks! "Ivyleaf" wrote: On Apr 5, 3:51 am, Bob wrote: 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" wrote: On Apr 5, 3:02 am, Bob wrote: 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 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.- Hide quoted text - - 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.- Hide quoted text - - Show quoted text - Hi Bob, No problem, glad to hear you got it working and thanks for the reply. Cheers, Ivan. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
| filtering based on a search result | New Users to Excel | |||
| Filtering a Top or Bottom 10 result set | Excel Discussion (Misc queries) | |||
| copying rows where a result cell has a certain value or higher | Excel Discussion (Misc queries) | |||
| how do i copy a formula's result to another cell, without copying. | Excel Worksheet Functions | |||
Linear Mode
