Copying data with a macro using autofilter
 

I set up a few columns of data with an autofilter. The user selects only 1
row at a time with the auto filter. I want to set up a macro to copy this
row to another area of the worksheet for graphing. When All is selected by
the filter, the first record is row 5. I can set up a macro to copy row 5 to
another location for graphing. However, when a user selects any other row,
row 5 of the spreadsheet is now replaced by that row and the macro does not
work. Is there a way to determine what row the user selects by a filter, and
copy that row to another area of the spreadsheet ?

Copying data with a macro using autofilter
 

Study this code for insights:

http://www.rondebruin.nl/copy5.htm


--
Regards,
Tom Ogilvy


"rmcompute" wrote:

I set up a few columns of data with an autofilter. The user selects only 1
row at a time with the auto filter. I want to set up a macro to copy this
row to another area of the worksheet for graphing. When All is selected by
the filter, the first record is row 5. I can set up a macro to copy row 5 to
another location for graphing. However, when a user selects any other row,
row 5 of the spreadsheet is now replaced by that row and the macro does not
work. Is there a way to determine what row the user selects by a filter, and
copy that row to another area of the spreadsheet ?

Copying data with a macro using autofilter
 

On additional bit, if you copy the entire range of data that has a filter
applied, only the visible rows are copied. This is the default behavior.

If you step down one row so the header is not included, then you only will
copy your single row.

ActiveSheet.AutFilter.Range includes the header

With ActiveSheet.Autofilter.Range
set r = .offset(1,0).Resize(.rows.count-1)
End with

r should be your row.

--
Regards,
Tom Ogilvy


"rmcompute" wrote:

I set up a few columns of data with an autofilter. The user selects only 1
row at a time with the auto filter. I want to set up a macro to copy this
row to another area of the worksheet for graphing. When All is selected by
the filter, the first record is row 5. I can set up a macro to copy row 5 to
another location for graphing. However, when a user selects any other row,
row 5 of the spreadsheet is now replaced by that row and the macro does not
work. Is there a way to determine what row the user selects by a filter, and
copy that row to another area of the spreadsheet ?

Copying data with a macro using autofilter
 

It worked. Thank you.

"Tom Ogilvy" wrote:

On additional bit, if you copy the entire range of data that has a filter
applied, only the visible rows are copied. This is the default behavior.

If you step down one row so the header is not included, then you only will
copy your single row.

ActiveSheet.AutFilter.Range includes the header

With ActiveSheet.Autofilter.Range
set r = .offset(1,0).Resize(.rows.count-1)
End with

r should be your row.

--
Regards,
Tom Ogilvy


"rmcompute" wrote:

I set up a few columns of data with an autofilter. The user selects only 1
row at a time with the auto filter. I want to set up a macro to copy this
row to another area of the worksheet for graphing. When All is selected by
the filter, the first record is row 5. I can set up a macro to copy row 5 to
another location for graphing. However, when a user selects any other row,
row 5 of the spreadsheet is now replaced by that row and the macro does not
work. Is there a way to determine what row the user selects by a filter, and
copy that row to another area of the spreadsheet ?