filter copying?

[anantathaker]

if i have a filtered list in excel, is there a way to copy just the rows displayed after the filter and not the others?

thanks!

 

[Beate Schmitz]

copy autofilter-results to other sheet

Hello,

presumed the filtered list is in sheet1, then the filter results are copied to sheet2, cell A1 and following cells. The filtered range is found automatically by the code:


<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" ><b><span style="color:#000080"; >Public</span> <span style="color:#000080"; >Sub</span> copy_filtered_range()</b><br />    <span style="color:#008000"; >'http://www.online-excel.de/excel/singsel_vba.php?f=81</span><br />    <span style="color:#008000"; >'code from Max Kaffl</span><br />    <span style="color:#000080"; >Dim</span> lngFilterRow <span style="color:#000080"; >As</span> Long, lngFilterColumn <span style="color:#000080"; >As</span> <span style="color:#000080"; >Long</span><br />    <span style="color:#000080"; >Dim</span> lngFilter <span style="color:#000080"; >As</span> <span style="color:#000080"; >Long</span><br />    <span style="color:#000080"; >With</span> Worksheets(<span style="color:#800000"; >"Sheet1"</span>)<br />        <span style="color:#000080"; >If</span> .AutoFilterMode <span style="color:#000080"; >Then</span><br />            <span style="color:#000080"; >If</span> .FilterMode <span style="color:#000080"; >Then</span><br />                <span style="color:#000080"; >With</span> .AutoFilter<br />                    lngFilterRow = .Range.Row<br />                    lngFilterColumn = .Range.Column<br />                    <span style="color:#000080"; >With</span> .Filters<br />                        <span style="color:#000080"; >For</span> lngFilter = 1 <span style="color:#000080"; >To</span> .Count<br />                            <span style="color:#000080"; >If</span> .Item(lngFilter).On <span style="color:#000080"; >Then</span> <span style="color:#000080"; >Exit</span> <span style="color:#000080"; >For</span><br />                        <span style="color:#000080"; >Next</span><br />                    <span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span><br />                <span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span><br />                .Range(.Range(.Cells(lngFilterRow + 1, lngFilterColumn), _<br />                    .Cells(lngFilterRow + 1, lngFilterColumn + .AutoFilter.Filters.Count - 1)), _<br />                    .Cells(lngFilterRow, lngFilter).End(xlDown)).Copy _<br />                    Worksheets(<span style="color:#800000"; >"Sheet2"</span>).Range(<span style="color:#800000"; >"A1"</span>)<br />            <span style="color:#000080"; >Else</span><br />                MsgBox <span style="color:#800000"; >"autofilter not active."</span>, 48, <span style="color:#800000"; >"Hint"</span><br />            <span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span><br />        <span style="color:#000080"; >Else</span><br />            MsgBox <span style="color:#800000"; >"No autofilter in sheet."</span>, 48, <span style="color:#800000"; >"Hint"</span><br />        <span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span><br />    <span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span><br /><b><span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></b><br /></span></nobr></div><br/><div style=" background-color:#f8f8f8; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px; width:300px;" >Codehighlighting with <a href="http://www.haserodt.de/cj_pro/cjdirect.php" >CodeJeanieDirectHtml</a></div>

 

[Peter_SSs]

if i have a filtered list in excel, is there a way to copy just the rows displayed after the filter and not the others?

thanks!

Might need a little more detail, but you could see if this does what you want.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyFiltered()<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        <SPAN style="color:#00007F">If</SPAN> .FilterMode <SPAN style="color:#00007F">Then</SPAN><br>            .AutoFilter.Range.SpecialCells(xlCellTypeVisible).EntireRow.Copy _<br>                Destination:=Sheets("Sheet2").Range("A1")<br>        <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox "No filter applied"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>