Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Question Unanswered: AdvanceFilter in Macro

    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?

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

    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.
    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.

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    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
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Bob,
    We are on Excel 2003 at this time.
    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.
    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.
    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?

  5. #5
    Join Date
    Dec 2005
    Posts
    121
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •