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



    ActiveWorkbook.Worksheets("Test Form").Sort.SortFields. _
    ActiveWorkbook.Worksheets("Test Form").Sort.SortFields.Add _
    Key:=Range("F7:F320"), SortOn:=xlSortOnValues
    Order:=xlAscending, _
    ActiveWorkbook.Worksheets("Test Form").Sort.SortFields.Add _
    Key:=Range("D7:D320"), SortOn:=xlSortOnValues
    Order:=xlAscending, _
    With ActiveWorkbook.Worksheets("Test Form").Sort
    .SetRange Range("A7:F320")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    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
    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, Mar 28, 2014
    1. Advertisements

Ask a Question

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.