Solved

VBA Excel 2000: Best way to export Autofilter Result

Posted on 2004-08-18
6
216 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

760 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

24 Experts available now in Live!

Get 1:1 Help Now