Macro Pause to Select range then Subtotal

Discussion in 'Excel Beginners' started by Gene Haines, Mar 28, 2014.

  1. Gene Haines

    Gene Haines Guest

    Hello: I am trying to create a macro that will allow a user to select
    range, which can be variable each time, and then subtotal twice. Th
    range is a list of filtered items that needs to be subtotaled along wit
    the header and is 6 rows below the 1st row. Rows 1 thru 5 hav
    additional information that is not part of the filtered range. Th
    spreadsheet user has 3 assigned macro buttons. The first step is for th
    user to deselect items from the filtered list that they want and the
    run the 1st of 3 macros "delete rows macro" of those that are selecte
    which they don't want. They then now have only the items they want. Th
    next step is to run the 2nd macro "subtotal macro" for that list, whic
    is where I have the issue. The "subtotal macro" will first sort on th
    list, then pause and ask the user to select a range and then go thru th
    subtotal process. When it gets to the subtotal step in the macro th
    error message "Microsoft Excel cannot detemine which row in your
    list or selection contains column labels", which seems to be th
    problem. It doesn't seem that the pause in the macro which allows th
    user to select a range holds that range when it gets to the subtota
    step. The last of the 3rd assigned macro buttons gives the user th
    ability to save the file with a name of their choosing. I have attache
    the code for the "subtotal macro" in this post. Any help if possibl
    would be appreciated.
    Thank you

    Gene

    Sub SORTSUBTOTAL()
    '
    ' SORTSUBTOTAL Macro
    '

    '
    ActiveWorkbook.Worksheets("Test Form").Sort.SortFields. _
    Clear
    ActiveWorkbook.Worksheets("Test Form").Sort.SortFields.Add _
    Key:=Range("F7:F320"), SortOn:=xlSortOnValues
    Order:=xlAscending, _
    DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Test Form").Sort.SortFields.Add _
    Key:=Range("D7:D320"), SortOn:=xlSortOnValues
    Order:=xlAscending, _
    DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Test Form").Sort
    .SetRange Range("A7:F320")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    Dim myRng As Range
    Set myRng = Nothing
    On Error Resume Next
    Set myRng = Application.InputBox("Select a range", Type:=8)
    On Error GoTo 0

    If myRng Is Nothing Then
    'user hit cancel
    'what happens here
    Else
    MsgBox myRng.Address 'do what you want
    End If
    Selection.SUBTOTAL GroupBy:=6, Function:=xlSum, TotalList:=Array(3), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.SUBTOTAL GroupBy:=4, Function:=xlSum, TotalList:=Array(3)
    _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    Columns("F:F").ColumnWidth = 18.29
    Columns("D:D").ColumnWidth = 10.43
    End Su


    --
    Gene Haines
     
    Gene Haines, Mar 28, 2014
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Jerry Cropanese

    Remove Subtotal In Macro Not Working

    Jerry Cropanese, Jun 27, 2005, in forum: Excel Beginners
    Replies:
    0
    Views:
    128
    Jerry Cropanese
    Jun 27, 2005
  2. BrightEyes_PR
    Replies:
    2
    Views:
    108
    Pete_UK
    Feb 21, 2006
  3. 06Speed6
    Replies:
    2
    Views:
    190
    06Speed6
    Oct 5, 2006
  4. Anastasio Jimenez

    If all cells in a range are N/A, then N/A?

    Anastasio Jimenez, Nov 10, 2006, in forum: Excel Beginners
    Replies:
    3
    Views:
    93
  5. Maaz
    Replies:
    1
    Views:
    106
  6. NP
    Replies:
    1
    Views:
    182
    Gord Dibben
    Feb 20, 2008
  7. teenbeat
    Replies:
    3
    Views:
    156
    Shane Devenshire
    Mar 14, 2009
  8. gls858

    subtotal by a range

    gls858, Apr 6, 2009, in forum: Excel Beginners
    Replies:
    4
    Views:
    106
    gls858
    Apr 7, 2009
Loading...