Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Excel 2000: Best way to export Autofilter Result

Posted on 2004-08-18
6
Medium Priority
?
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

688 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