Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2004

Tip: Looking for answers? Try searching our database.

Advanced filter with cut data instead of copy?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Yariev - 12 Oct 2004 15:44 GMT
I am using the advanced filter - Sub ABC (see below) - for transferin
data from 1 input-sheet to various different sheets. However usin
advanced filter, the data is copied to other sheets. Is it possible t
have this cut or moved instead of copied and how can I do this?

Can someone help with this, thnx!

Sub ABC()

Sheets("Blad1").Range("A1:C21").AdvancedFilter Action:=xlFilterCopy,

CriteriaRange:=Sheets("Blad2").Range("A1:C2")
CopyToRange:=Range("A1"), _
Unique:=False
Sheets("Blad B").Select
Sheets("Blad1").Range("A1:C21").AdvancedFilter Action:=xlFilterCopy,

CriteriaRange:=Sheets("Blad2").Range("A4:C5")
CopyToRange:=Range("A1"), _
Unique:=False
Sheets("Blad C").Select
Sheets("Blad1").Range("A1:C21").AdvancedFilter Action:=xlFilterCopy,

CriteriaRange:=Sheets("Blad2").Range("A7:C8")
CopyToRange:=Range("A1"), _
Unique:=False

End Su
Dave Peterson - 12 Oct 2004 22:49 GMT
Maybe you could filter in place, copy the visible cells, paste those visible
cells, then delete the visible rows--but not the header:

This may give you an idea:

Option Explicit
Sub ABCD()

   With Worksheets("blad1")
       .Range("a1:c21").AdvancedFilter Action:=xlFilterInPlace, _
           CriteriaRange:=Worksheets("blad2").Range("A1:C2"), Unique:=False
           
       .Range("_filterDataBase").Cells.SpecialCells(xlCellTypeVisible).Copy _
           Destination:=Worksheets("blad A").Range("a1")
           
       With .Range("_filterDataBase")
           If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
              .Cells.Count > 1 Then
               .Resize(.Rows.Count - 1).Offset(1, 0) _
                  .Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
           End If
       End With
       
       .ShowAllData
       
   End With

End Sub

(it only does one of the filters.)

> I am using the advanced filter - Sub ABC (see below) - for transfering
> data from 1 input-sheet to various different sheets. However using
[quoted text clipped - 30 lines]
> Yariev's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15216
> View this thread: http://www.excelforum.com/showthread.php?threadid=268500

Signature

Dave Peterson
ec35720@msn.com

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.