Macro Pause to Select range then Subtotal


Gene Haines

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


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. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtotal Formatting 2
range definition by cells numbers 3
List sorting and macros 3
Subtotal by VBA 5
Undo Macro Action 3
Sorting Question 5
Automatic sort macro 2