Mass filter

[flopez82]

Hello at all,

I really need help with this

I need to filter by these equipments codes and by the zip codes and have it paste on another sheet

How can I do this???

This is what i have.... but this just hides the fileds i dont want on the same sheet.

Sub Macro3()
'
' Macro3 Macro
'

'
ActiveSheet.Range("$A$1:$AC$542").AutoFilter Field:=16, Criteria1:=Array( _
"FR", "A", "D3", "WW", "G3", "G5", "ND", "KC", "GM", "VS", "VW", "ET", "V0", "VU", "F3", "F4", "F5", "AK", "DV", "H1", "H2", "H5", "H6", "HD", "K", "MW", "MH", "LC", "MC", "MS", "MQ", "X1", "X2", "MA"), Operator:=xlFilterValues

ActiveSheet.Range("$A$1:$AC$542").AutoFilter Field:=7, Criteria1:=Array( _
"=*33178*", "=*33181*"), Operator:=xlFilterValues
End Sub



Thanks in advance

[nimrod]

Once you've done your filter you can tell copy to only use the visiable cells like this

Rows("1:542").SpecialCells(xlCellTypeVisible).Copy

[flopez82]

ok and for it to be pasted in sheet2??

[nimrod]

What kind of paste are you doing ? A pastespecial ? PasteAll ? Is it an overwrite starting in A1 ? Or is it an Append ? If it is an append what column should be referenced to determine where append should start ? Is there a row or colomn offset ?

[flopez82]

i want to paste the results from the search into the first field on sheet2 which is A1

[flopez82]

WOW.... ok i need help from the begining because apparently i can only filter by two zip codes at a time with this code

how can a build a filter that will let me filter by all those zip codes?? and then paste the results in sheet2 A1

thanks in advance

[nimrod]

Please Login or Register  to view this content.

[nimrod]

Hi:
How many equipments codes and zip codes will you be wanting to filter by ?

[flopez82]

24 zip codes

33035 33034 33033 33039 33030 33031 33190 33032 33170 33187 33189 33177 33157 33158 33176 33156 33186 33196 33183 33193 33173 33143 33146 33133

34 eqipment types

"FR", "A", "D3", "WW", "G3", "G5", "ND", "KC", "GM", "VS", "VW", "ET", "V0", "VU", "F3", "F4", "F5", "AK", "DV", "H1", "H2", "H5", "H6", "HD", "K", "MW", "MH", "LC", "MC", "MS", "MQ", "X1", "X2", "MA"

[nimrod]

Excel 2007 will allow you to filter by more than two ... but it's only going to show you the zips that meet the requirements of the already applied filters ... are you sure it's not the case that only two zip codes meet the requirements of the first filter ??

[flopez82]

nope its not doing it dunno why only showing me the first two zip codes

[nimrod]

if , in the equipment type column, you choose to show all , then do you see more zip codes than just two ??

[flopez82]

i have separated the two codes and im working on just the zip codes to see if that was the case
once i run this code it doesnt show any results just the headings

Sub Macro3()
'
' Macro3 Macro
'

'
ActiveSheet.Range("$A$1:$AC$542").AutoFilter Field:=7, Criteria1:=Array( _
"=*33035*", "=*33034*", "=*33039*", "=*33030*", "=*33031*", "=*33190*", "=*33032*", "=*33170*", "=*33187*", "=*33189*", "=*33177*", "=*33157*", "=*33158*", "=*33176*", "=*33156*", "=*33186*", "=*33196*", "=*33183*", "=*33193*", "=*33173*", "=*33143*", "=*33146*", "=*33133*"), Operator:=xlFilterValues
End Sub

[nimrod]

Can you upload the file to this thread ?

[flopez82]

it has alot of confidential info.... can i email it to you (less people will have access to it im guessing)

[nimrod]

What column are your zip codes in ?

[nimrod]

I have sent you hotmail email addrss ... though I'm only on line for about another 30 minutes

[flopez82]

EMAIL SENT

thanks so much for this

[nimrod]

PROPOSED WORKAROUND:
Your problem is that you are using "wildcards" (i.e. "*" ) in your filter. The filter doesn't seem to like wild cards when for more than two zip codes.
So to get rid of the problem:
1. add another column beside zip codes
2. use "Text to columns" with "-" as you delimter to seperate you zip codes into 2 columns
3. Now you don't need to use "wildcards" now filter will look like this ...

Please Login or Register  to view this content.

[flopez82]

any questions while looking at the report?

the zip codes have the tag after the main number so it needs to filter for the main numbers only ex:33178-2254 should only filter by 33178 so that all the other 33178s come out

[nimrod]

Please look at my last post and see if that sol'n will work for you .

[nimrod]

Hi flopez82:
I'm having to signoff now ... I will be back online tomorrow . Hopefully the information I've provided you has got you pointed in the correct direction.

[flopez82]

the sip codes work just fine ty

[flopez82]

now gotta make it filter the equipment codes and then paste on the sheet2

[nimrod]

I'm back on line today ... did you get it working ?

[flopez82]

hey..... i gotta it to kinda work lol i got really crazy and started to add more things to the macro and now its out of hand lol

think i may have to go back a couple steps lol

[flopez82]

hey is there a way to only select the text and not the whole worksheet???

ex lets say i got text in rows 1-145 and colums a-ac

is there a way to just select the text in those rows only? the trick is that each report will have a different number of rows and columns lol

[nimrod]

Please Login or Register  to view this content.

[flopez82]

hey.... how can i join that formula and this one below so that once i run the macro it selects only the text in sheet2 and saves that text in a new csv file with a new file name which inludes todays date ??? The formula below works but since it selects all the cells in sheet2 its runining my next step which only allows the text and not the extra blank rows its pasting in there .....

Sub SAVE2()
'
' SAVE2 Macro
'
ThisWorkbook.Worksheets("sheet2").Copy
Dim Today As Date
Today = Date
ActiveWorkbook.SaveAs Filename:="C:\Users\str11ker\Desktop\COMCAST\felix reports\2011\" & Format(Today, "MM_DD_YYYY") & "METRO" & ".csv"
ActiveWorkbook.Close
'
End Sub

[flopez82]

hey nimrod you abandoned me lol

hey that previous formula you gave me in post #28 is the right concept cause it shows me which rows and colums have text
but
i need it to actually copy the text in those rows and colums only and paste that text onto a new workbook

thanks for your help