Rere
New Member
Dear Macro Master 
Greetings from Jakarta
I have some macro code as below for sort Top 10 or more by selecting the target sort with TOP or Bottom with target result for Sort ( I use 1-50 )
However, I need another drop down menu as an option to show the VALUES column with connecting Sort option above
What I expect is :
- I have 2 option ( SFDC and OMS )
- When the SFDC selected then the sort field target will refer to the option ( ex. If I choose type as TOP with range 10 and sort by SFDC, the result will sort TOP 10 by SFDC value with auto Descending from High to Low )
Please teach me how to add another code for selecting the target Sort above
I already create the "SortSel" define a name for the Dropdown menu and "SortCell" as range the Validation with option SFDC and OMS.
Thank You
Greetings from Jakarta
I have some macro code as below for sort Top 10 or more by selecting the target sort with TOP or Bottom with target result for Sort ( I use 1-50 )
However, I need another drop down menu as an option to show the VALUES column with connecting Sort option above
What I expect is :
- I have 2 option ( SFDC and OMS )
- When the SFDC selected then the sort field target will refer to the option ( ex. If I choose type as TOP with range 10 and sort by SFDC, the result will sort TOP 10 by SFDC value with auto Descending from High to Low )
Please teach me how to add another code for selecting the target Sort above
I already create the "SortSel" define a name for the Dropdown menu and "SortCell" as range the Validation with option SFDC and OMS.
Code:
Private Sub Worksheet_Change _
(ByVal Target As Range)
Dim ws As Worksheet
Dim wsL As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pfD As PivotField
Dim rngType As Range
Dim rngTypeSel As Range
Dim rngNum As Range
Dim lType As Long
On Error GoTo errHandler
Set ws = ActiveSheet
Set wsL = Worksheets("RefData")
Set pt = ws.PivotTables(1)
Set pf = pt.PivotFields("Opportunity ID")
Set pfD = pt.DataFields(1)
Set rngType = ws.Range("TypeSel") 'TOP & Bottom
Set rngTypeSel _
= wsL.Range("TypeValSel") 'Set data TOP & Bottom
Set rngNum = ws.Range("NumSel")
Select Case Target.Address
Case rngType.Address, _
rngNum.Address
Application.ScreenUpdating = False
pf.ClearAllFilters
If rngNum.Value > 0 And _
rngType > 0 Then
pf.PivotFilters.Add _
Type:=rngTypeSel.Value, _
DataField:=pfD, _
Value1:=rngNum.Value
End If
End Select
exitHandler:
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Check Pivot Table"
Resume exitHandler
End SubThank You
