Sort Statement

[Zach]

Ladies/Gentlemen
I need to get a sort statement in here. It pulls all the data and does all the subtotaling I want but I need it to sort first. I forgot that part and need column "G" to sort before it subtotals.

Any suggestions?

'Filter file
 CVT.Select
  CVT.Range(Cells(1, 1), "K" & lLast).AutoFilter Field:=2, Criteria1:="Phoenix"
  CVT.Range(Cells(1, 1), "K" & lLast).SpecialCells(xlCellTypeVisible).Copy
  MTab.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Application.CutCopyMode = False
  With MTab
  .Select
  .Cells.Select
  .Cells.EntireColumn.AutoFit
  lLast = .Range("A" & Rows.Count).End(xlUp).Row
  .Sort.SortFields.Add Key:=Range(.Cells(1, 1), "G" & lLast) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Range(.Cells(1, 1), "K" & lLast).Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(11), _
  Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  lLast = .Range("G" & Rows.Count).End(xlUp).Row
  With .Range(.Cells(1, 1), "K" & lLast).Borders
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With

I'm attaching the file as well.

Thanks

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Zach]

Hey - This works good. Thanks for the help.

 

[Zach]

Is that a way to get an Input box to pop up and allow me to call out the division I want the Macro to run on? Input/message boxes are something I've never worked with before.

 

[Chihiro]

Something like this, and replace "Phoenix" with myDiv in NARAYANK991's code.

Dim myDiv As String
myDiv = InputBox("Type Division Name")
If Len(myDiv) = 0 Then
    MsgBox "Please Enter a Division Name", vbCritical
    Exit Sub
    Else
End If

However, InputBox is very limited and isn't very user friendly. As you will need to code to catch if user typed in something that does not exist in list of divisions.

Instead, I'd recommend using another sheet to house dropdown list or some other type of list to choose division and use command button to run the macro.

 

[Chihiro]

Here's quick sample using command button to extract unique Division names and button to run NARAYANK991's code with myDiv set to active cell value.