Sub Total Macro

[Papi]

I have two workbooks. The first is a macro enabled (2010) workbook called Report with five columns. The other workbook is generated from a database into Excel and is simply called book1. What I am trying to do is to have only the first five columns of data (there are more) copied from book1 workbook into the Report then I would like the Sub total feature turn on and sort by Column C and then Column B. Once that is done I need the sum based on Column B with the sum going to column E.

The error is arrives at .SetRange ActiveCell.Offset(-1, 0).Range("A1:E199")

It does copy the data but stops there. The other thing is that the number of rows will vary each time the report is generated anywhere from a few rows to several hundred.

Is it possible to run the Sub Total feature with a macro?

Sub Retrieve_Report()
'
' Retrieve_Report Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Application.ScreenUpdating = False
Application.Goto Reference:="R1C1"
ActiveCell.Range("A1:E1").Select
Selection.Copy
ActiveWindow.ActivateNext
Application.Goto Reference:="R1C1"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Columns("A:E").EntireColumn.Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
ActiveWorkbook.Worksheets("Report").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Report").Sort.SortFields.Add Key:=ActiveCell.Offset(0, _
2).Range("A1:A198"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Report").Sort.SortFields.Add Key:=ActiveCell.Offset(0, _
1).Range("A1:A198"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Report").Sort
.SetRange ActiveCell.Offset(-1, 0).Range("A1:E199")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Application.ScreenUpdating = True
End With
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

 

[Papi]

Got It. Thanks in part to Wigi I was able to put one together to do the copying, sorting and sub totals into one macro.

Wigi's code was

Sub Sort_Quotes()
With Selection
.Sort Range("R" & .Row), , Range("E" & .Row), , , Range("F" & .Row), Header:=xlNo
End With
End Sub