Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 28th 14, 11:44 AM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 20
Default Macro Pause to Select range then Subtotal

Hello: I am trying to create a macro that will allow a user to select a range, which can be variable each time, and then subtotal twice. The range is a list of filtered items that needs to be subtotaled along with the header and is 6 rows below the 1st row. Rows 1 thru 5 have additional information that is not part of the filtered range. The spreadsheet user has 3 assigned macro buttons. The first step is for the user to deselect items from the filtered list that they want and then run the 1st of 3 macros "delete rows macro" of those that are selected which they don't want. They then now have only the items they want. The next step is to run the 2nd macro "subtotal macro" for that list, which is where I have the issue. The "subtotal macro" will first sort on the list, then pause and ask the user to select a range and then go thru the subtotal process. When it gets to the subtotal step in the macro the error message "Microsoft Excel cannot detemine which row in your
list or selection contains column labels", which seems to be the problem. It doesn't seem that the pause in the macro which allows the user to select a range holds that range when it gets to the subtotal step. The last of the 3rd assigned macro buttons gives the user the ability to save the file with a name of their choosing. I have attached the code for the "subtotal macro" in this post. Any help if possible 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("D7320"), 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").ColumnWidth = 10.43
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop / Pause a Macro using Pause button scott56hannah Excel Programming 0 June 27th 08 12:48 PM
Pause to select cell Lweiss Excel Programming 3 March 11th 08 08:59 PM
pause macro until user selects correct range JCIrish Excel Programming 6 March 18th 06 09:26 PM
create a pause in print macro to allow user to select printer Scott53 Excel Programming 1 September 7th 05 04:29 PM
How do I pause a macro to select specific cells lee Excel Worksheet Functions 1 April 2nd 05 02:11 PM


All times are GMT +1. The time now is 10:10 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017