copy zone only after auto filter

[kingz2000]

Hi,

I have an excel sheet with a long list of data..I actually want to filter some columns and copy THE RESULT ONLY onto an existing tab page. I thought these instructions would do it, but it copies everything with the filter the settings..when I actually only want the resulting data with no filter seetings...Here is my code

Private Sub split_data()
With Worksheets("Original_Data")
[Y3].AutoFilter Field:=25, Criteria1:="X"
[Z3].AutoFilter Field:=26, Criteria1:="X"
[AA3].AutoFilter Field:=27, Criteria1:="X"
[AB3].AutoFilter Field:=28, Criteria1:="X"
[AC3].AutoFilter Field:=29, Criteria1:="X"
[AD3].AutoFilter Field:=30, Criteria1:="X"
[A1].Copy Sheets("Filtered_Data").[B1]
Worksheets(1).Copy After:=Sheets(2)
End With

Basically, Is there anyway I can just copy the resulting data onto another page??

 

[PHV]

You may try something like this:
Worksheets("Original_Data").Cells.SpecialCells(xlCellTypeVisible).Copy Sheets("Filtered_Data").[B1]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kingz2000]

hmm....Well I get an error message reading: 'index out of valid region'..[translated]...on the line which you have given me
so something is wrong with the given line.

 

[SkipVought]

Hi,

Try this

    Worksheets("Original_Data").[A1].CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
        Sheets("Filtered_Data").[B1]

assuming that your data starts in A1.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[kingz2000]

that didn't work either , but his did!:

With Worksheets("MasterData")
[Y3].AutoFilter Field:=25, Criteria1:="X"
[Z3].AutoFilter Field:=26, Criteria1:="X"
[AA3].AutoFilter Field:=27, Criteria1:="X"
[AB3].AutoFilter Field:=28, Criteria1:="X"
[AC3].AutoFilter Field:=29, Criteria1:="X"
[AD3].AutoFilter Field:=30, Criteria1:="X"
Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Schleckerland").[A1]

End With

Now I was wondering, How can I now go back to the 'MasterData' tabpage and erase those selected and remove the gaps caused by the erasure???
Thanks for any help.

Kingsley

 

[Danp129]

Do a sort on the sheet

 

[kingz2000]

ok, that would cater for the empty lines, but what about the erasure of the selected lines which I dumped unto a another tabpage???

 

[PHV]

Have a look at the Rows collection, the EntireRow property and the Delete method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886