Solved

VBA Excel 2000: Best way to export Autofilter Result

Posted on 2004-08-18
6
217 Views
Last Modified: 2008-02-01
What is the best way to get the result of an _Autofilter_ to a new worksheet ?

I was not able to find something on the net, most seems to deal with printing these ranges.

Will increase the total amount of points to honor valuable contributions or real cool code.

Question: How do you see this ?
0
Comment
Question by:efarhat
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:ampapa
ID: 11833926
I would use the Edit/Go_To/Special/visiblecellsonly and copy them to a new sheet. Should work fine not coded but effecient.
0
 

Author Comment

by:efarhat
ID: 11834069
there isn't another way? a macro possibly?  its the same as copy/pasting..
0
 
LVL 35

Accepted Solution

by:
mvidas earned 125 total points
ID: 11834595
Hello again efarhat,

The code:

With ActiveSheet
 Intersect(.Cells.SpecialCells(xlCellTypeVisible), .UsedRange).Copy
End With
Sheets.add
ActiveSheet.Paste

will copy only the visible used cells on the page, create a new sheet, and paste them on the new sheet.  If you describe in more detail exactly what you want it to do (and where you want it to go), I'd be glad to help.  The Specialcells(xlCellTypeVisible) is the same thing as ampapa suggested, just in code form.

Matt
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Expert Comment

by:ampapa
ID: 11834767
Sorry, didn't realize you wanted the coded version...
0
 

Author Comment

by:efarhat
ID: 11835366
Ampapa,

Do you know how do I search a column for a number and sum up it's values based on another column??`
 
say i'm looking for a number and its found on row 1, column 1 and i need to add its data value from column 2, row 1.  so, say the number appears in row 1 and row 2. i need to add up it's value from column 2, for row 1 and row 2.

does that make sense?  can this be done with excel formulas??  the Keys are in Sheet1 and you want to search Sheet2 column 1.
0
 
LVL 8

Expert Comment

by:ampapa
ID: 11835655
Your probably looking at a sumproduct formula. Possibly something similar to this?

=SUMPRODUCT((A1:A11=7)*(B1:B11=6)*A1:A11)

Column 1 consists of repeating data and colum 2 also consists of repeating data, for each 7 found in column 1 and for each 6 found in column 2 the values of column 1 will be added to gether.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now