filter/sort/subtotal via a macro

[osaben]

I'm really strugglling with developing a macro to do the following:

rows of 6 columns of figures and two columns of dates...would like to filter, sort & subtotal the data based on a range of dates in a date column. The column (date 1 or date 2) that I would need to filter on would vary based on what subtotal I wanted to view.

once this is accomplished, I would like to take the subtotals and copy/paste/value it to a second sheet.

All the while being able to clear the sorts/subtotals once the information is viewed/used.

Any help would be GREATLY apprecitated!

 

[osaben]

Here is the code that I have.....The sort & filter seem to be working. The subtototal is working. (although there is not visible outline)....I assume this is ok. But I am unable to get only the subtotals to copy to the second sheet.


Private Sub CommandButton1_Click()
' Apply advance filter
Range("g6").Select
Range("A6:H50000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("J1:K2"), Unique:=False
' Sort data
Range("A6:H50000").Sort Key1:=Range("G3"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Apply Subtotal
Selection.Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(1, 2, 3, 4, _
5, 6), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub
Private Sub CommandButton2_Click()
Range("g6").Select
Selection.RemoveSubtotal ' Remove subtotal
ActiveSheet.ShowAllData ' Remove Advanced filter
End Sub
Private Sub CommandButton3_Click()
' Apply advance filter
Range("g6").Select
Range("A6:H50000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("L1:M2"), Unique:=False
'Sort data
Range("A6:H50000").Sort Key1:=Range("h3"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Apply subtotal
Selection.Subtotal GroupBy:=8, Function:=xlSum, TotalList:=Array(1, 2, 3, 4, _
5, 6), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'Show all data
'ActiveSheet.ShowAllData
End Sub