All,
I need help. I have tried every way I can think of but can't get this to work and it seems so simple.

I have a spreadsheet that auto generates a list of inbound po's. The worksheet is auto set up with auto filter turned on for each column. Column S is the department number column. There are about 400 different department numbers. The auto filter allows the user to select one department number to filter by. I want to reduce the sheet down to a list of 7 static department numbers. These will not change, but the number of rows in the worksheet will due to daily PO additions and subtractions.

I tried a criteria column with the 7 numbers, but it will not filter in my macro. I need to filter down to these 7 department numbers, then copy and paste the results into a new file.

Any suggestions? Anyone else had this problem?

Hi,

Which version of Excel are you using, Bob?

In XL 2007+, to filter by 7 department numbers in 1 column (essentially OR conditions) can be done with the autofilter with no problem at all but, in earlier versions, you would have to either:
[i] use the advanced filter; or
[ii] add a helper column which you would autofilter by.

It's hard to say without seeing some sample data and the VBA code. If you could remove any confidential information and zip+attach an example to this thread then I might be able to help.

We are on Excel 2003 at this time.

The company has a very complicated Excel report that connects to data sources thru ODBC to build a "Aging Report" of PO's, and based on initial input boxes either builds the report for one distribution center, or all distribution centers. Once it is complete, the masterdata tab is set to auto filter each columnn for the user.

What I have done is piggybacked onto this file by creating a seperate Excel file that contains macros that open up the aging report and let it run normally, then once it finishes, filter down to a specific department number in column S and copy and paste certain columns of information into a new file that can be printed and used to monitor delivery of PO's within that department number. That part of the macro works fine, but I would like to now take the list to include all jewelry department numbers, not just one. What would be nice would be to replace the line that targets department number 927 with an input box that asks for either one of the 7 numbers, or ALL which would filter all 7 jewelry department numbers. The current code is below:

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 10/28/2008 by tk13883
'

'
MsgBox "When asked for DC, type ALL, when asked to include E-Comm, select No, when asked to exclude sets, select No, when asked for number of days, enter 0. Good Luck, and May the Force be with you!"
Workbooks.Open Filename:= _
"S:\share\dc\LP\HTM - High Theft Merchandise Tracking Tools\Current Base Aging Report\DC Aging Report File.xls"
Application****n "'DC Aging Report File.xls'!Update_aging_report"
Sheets("MasterData").Select
ActiveWindow.SmallScroll ToRight:=7
Selection.AutoFilter Field:=19, Criteria1:="927"
ActiveWindow.SmallScroll ToRight:=-7
Cells.Select
Selection.Copy
Windows("Build New Dept 927 Report Program.xls").Activate
Sheets("Prod").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=8
Range("L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,U:U").Select
Range("U1").Activate
ActiveWindow.SmallScroll ToRight:=9
Range("L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,U:U,V:V,W:W,X:X ,Y:Y,Z:Z,AA:AA,AB:AB").Select
Range("AB1").Activate
ActiveWindow.SmallScroll ToRight:=10
Range( _
"L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,U:U,V:V,W:W,X:X,Y:Y,Z :Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG,AH:AH ,AI:AI" _
).Select
Range("AI1").Activate
ActiveWindow.SmallScroll ToRight:=-6
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-3
Range("B:B,C:C,D").Select
Range("D1").Activate
ActiveWindow.SmallScroll ToRight:=3
Range("B:B,C:C,D,K:K,M:M").Select
Range("M1").Activate
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=-3
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("G1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:M").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("G2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A1").Select
Workbooks.Open Filename:= _
"S:\share\dc\LP\HTM - High Theft Merchandise Tracking Tools\Jewelry Inbound List\DC Dept 927 Update.xls"
Windows("Build New Dept 927 Report Program.xls").Activate
Application.DisplayAlerts = False
Sheets("Prod").Select
Sheets("Prod").Copy After:=Workbooks("DC Dept 927 Update.xls").Sheets _
(1)
Sheets("DC Department 927 Report").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Prod").Select
Sheets("Prod").Name = "DC Department 927 Report"
ActiveWorkbook.Save
Windows("DC Aging Report File.xls").Activate
ActiveWindow.Close SaveChanges:=False
Windows("Build New Dept 927 Report Program.xls").Activate
Sheets("Prod").Select
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("Menu").Select
Application.DisplayAlerts = True
ActiveWorkbook.Save
ActiveWindow.Close SaveChanges:=False


End Sub

This macro resides in a seperate Excel file named "Build New Dept 927 Report Program". This files sole purpose is to open the aging report, filter down results further and extract info into a second Excel file the building can use. In essance, just trying to make it run more user friendly for our computer challenged staff.

I have attached Excel files, both a simple version of the masterdata tab from the Aging report and the final copy of the filtered information that it copies the filtered data into.

Your help is very much appreciated, if I can make this work, it will help about 9 buildings to perform this task much easier that to run it 7 different times into 7 different files.

Thanks,
Bob

Hi Bob,
Okay, this is the most important factor. The autofilter was greatly enhanced in XL 2007, but you won't be able to take advantage of its new capabilities.
If the 7 department numbers you want happen to be in a sequence that would not include other department numbers then you can easily do this with the XL 2003 autofilter. For example, if the department numbers you want happen to be
924, 925, 926, 927, 928, 929, 930
then you could filter for >= 924 and <=930
Any pattern along this line will be fine.

On your attachment, the aging report only has 9 unique department codes whereas I believe the "full" version has 400, so I'm not sure if this is viable.

If the department numbers are not so conveniently grouped (and, given that this is the real world, this is what I'm expecting!) then you're back to the two options I highlighted in my initial reply:
[i] use the advanced filter; or
[ii] add a helper column which you would autofilter by.

Let me know which appeals the most and we'll see if we can cobble something together?

It is not quite so easily simple that all the department numbers are in sequence. They are 27, 29, 129, 227, 327, 422 and 727.

I am sure we may run into more instances where I need to filter down the department number list in other areas like this, so I am all ears on how you see best to make this work. I think our team will appreciate the automation on this if I can make it happen.

Thanks,
Bob