Need help with macro please!

[ksykes]

I’m not very experienced with Excel VBA and I’ve got a problem I’m having trouble finding a solution to – I’d sincerely appreciate some help!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have a spreadsheet that is the result of importing company information for companies that have passed at least 1 of 59 different stock screens.
<o> </o>
Each company’s information is in a row beginning in Row 2.

In Row 1 are the column headers:
Column A is the Stock Screen Name the stock passed (such as “Warren Buffett2”, or “Peter Lynch”, "Benjamin Graham", etc.) ; Column B is the Company Name, Column C is the Ticker Symbol, Column D has the month-ending close price, and on and on… with about 30 columns of data for each company.
<o> </o>
The number of stocks passing the screens will obviously vary. This month, I have 2,865 rows of data (or company / screen name combinations) sorted by Stock Screen Name by default. If a particular company passed 8 stock screens, it would be listed in 8 rows, with Column A (the Stock Screen Name) being the only difference in those 8 rows. The 8 rows would not be all together unless I re-sort the worksheet based on company name or ticker.
<o> </o>
Ok, here's the tricky part for me.... Based on back testing of stock returns, I want to emphasize 11 of the 59 stock screens. (I don’t want to disregard the other 48 though). What I want to do but can’t figure out how, is to have a macro that will copy the rows of data for all stocks that pass at least “n” number of the 11 preferred screens.
<o> </o>
So if n=3, all the rows of any stocks with 3 or more of the 11 preferred screen names would be copied to a new worksheet. Thus, if a company passed 4 screens, including any 3 of the preferred screens, all 4 rows would be copied to the new worksheet. On the other hand, if a company passed 5 screens, but only 1 of the preferred screens, its rows would not be copied to the new worksheet.
<o> </o>
This is way beyond my abilities in Excel. Thanks in advance for any help with this….
<o> </o>
Btw, here are some companies to explore further if you are so inclined, based on 7/31/09 closings! (Sorry no guarantees!)
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 112pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=149><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>Ticker<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

At or below<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>ARO<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

36.40<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>FRD<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

5.51<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>FUQI<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

24.13<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>HRBN<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

15.82<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>IEC<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

4.09<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>OMN<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

5.61<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>SPF<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

3.44<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>SYNT<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

39.58<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 9"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>TRLG<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

22.36<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 10"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>TSYS<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

8.28<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 11"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>USNA<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

33.35<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 12; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>WLFC<o></o>
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>

14.45<o></o>​

</TD></TR></TBODY></TABLE>
<o> </o>

 

[goofy78270]

1. I would start by emphasizing the desired companies by coloring the tabs or placing them in the beginning, whatever you choose.
2. Then create a two dimenisional array of all stocks and screen counts.
3. Loop through simply the emphasized screens to fill the array.
4. Evaluate the array for counts greater than 3 or whatever amount you choose.
5. For those that pass the evaulation, loop through all sheets using the find function.
6. If the stock is found, copy it to the desired location.
7. Move on to the next evaluation that passed until complete


I would loop through just the emphasized sheets first, to help speed up the process as it sounds like not all stocks are on all stock screens.

Try this and post any issues you may have.

 

[ksykes]

OK, I'll read about creating dimensional arrays, loops, etc. and see if I can come up with something. Thank you for your input.