Solved

VBA Excel 2000: Best way to export Autofilter Result

Posted on 2004-08-18
6
220 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

756 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