Help needed with VBA code

[Peter-Jan De Smedt]

Hi

I've got below code (based on the recording of a macro) which filters 2 pivot tables on basis of the input of a cell and then sorts the pivot tables in ascending and descending order. The code works unless there is no data in either of the pivot tables. Can anybody please help me improve the code so that I no longer get the error message "Subscript out of range"?

Many thanks in advance for any help you can give me.

[pre]

Private Sub CommandButton1_Click()
'filters the pivot table
Sheets("Pivottopbottom").PivotTables("PivotTable2").PivotFields("Key").ClearAllFilters
Sheets("Pivottopbottom").PivotTables("PivotTable2").PivotFields("Key").PivotFilters.Add _
Type:=xlCaptionContains, Value1:=Sheets("Pivottopbottom").Range("B3").Value
'sorts the pivot table
Range("C18").Select
Sheets("Pivottopbottom").PivotTables("PivotTable2").PivotFields("Key").AutoSort xlAscending _
, "Sum of € MM Depot", Sheets("Pivottopbottom").PivotTables("PivotTable2").PivotColumnAxis. _
PivotLines(1), 1
'filters the pivot table
Sheets("Pivottopbottom").PivotTables("PivotTable1").PivotFields("Key").ClearAllFilters
Sheets("Pivottopbottom").PivotTables("PivotTable1").PivotFields("Key").PivotFilters.Add _
Type:=xlCaptionContains, Value1:=Sheets("Pivottopbottom").Range("J3").Value
'sorts the pivot table
Range("L18").Select
Sheets("Pivottopbottom").PivotTables("PivotTable1").PivotFields("Key").AutoSort xlAscending _
, "Sum of € MM Depot", Sheets("Pivottopbottom").PivotTables("PivotTable1").PivotColumnAxis. _
PivotLines(1), 1
End Sub

[/pre]

 

[sdsurzh]

Hi,


Can you please upload the same workbook.


Thanks,

Suresh Kumar S

 

[Peter-Jan De Smedt]

Hi Suresh

Thanks for your reply. I found the answer in the meantime. This is how I amended the code should you be interested.

'sorts depot table largest to smallest
Range("C16:E19&#34.Select
ActiveWorkbook.Worksheets("X&#34.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("X&#34.Sort.SortFields.Add Key:=Range( _
"C19:E19&#34, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("X&#34.Sort
.SetRange Range("C16:E19&#34
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
'sorts fee table largets to smallest
Range("C31:E34&#34.Select
ActiveWorkbook.Worksheets("X&#34.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("X&#34.Sort.SortFields.Add Key:=Range( _
"C34:E34&#34, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("X&#34.Sort
.SetRange Range("C31:E34&#34
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
Range("a3&#34.Select
End With
'selects pivot won top 5 depot
Sheets("S&#34.PivotTables("PivotTable1&#34.PivotFields("Key&#34.ClearAllFilters
Sheets("S&#34.PivotTables("PivotTable1&#34.PivotFields("Key&#34.PivotFilters.Add _
Type:=xlCaptionContains, Value1:=Sheets("S&#34.Range("B4&#34.Value
'if pivot won emppty macro goes to pivot two
On Error GoTo Error_Handler
'sorts pivot won
Sheets("S&#34.PivotTables("PivotTable1&#34.PivotFields("Key&#34.AutoSort xlAscending _
, "Sum of ", Sheets("S&#34.PivotTables("PivotTable1&#34.PivotColumnAxis. _
PivotLines(1), 1
'intermediate step to enable 2nd error handler which is ohterwise ignored by macro
Error_Handler:
Resume Here
Here:
'selects pivot lost bottom 5 depot
Sheets("S&#34.PivotTables("PivotTable2&#34.PivotFields("Key&#34.ClearAllFilters
Sheets("S&#34.PivotTables("PivotTable2&#34.PivotFields("Key&#34.PivotFilters.Add _
Type:=xlCaptionContains, Value1:=Sheets("S&#34.Range("B4&#34.Value
'if pivot lost empty macro errorhandler 2
On Error GoTo Error_Handler_2
Sheets("S&#34.PivotTables("PivotTable2&#34.PivotFields("Key&#34.AutoSort xlAscending _
, "Sum of ", Sheets("S&#34.PivotTables("PivotTable2&#34.PivotColumnAxis. _
PivotLines(1), 1
Error_Handler_2:
Resume Here_2
Here_2:
'selects pivot won top 5 revenue
Sheets("S&#34.PivotTables("PivotTable3&#34.PivotFields("Key&#34.ClearAllFilters
Sheets("PS&#34.PivotTables("PivotTable3&#34.PivotFields("Key&#34.PivotFilters.Add _
Type:=xlCaptionContains, Value1:=Sheets("S&#34.Range("B4&#34.Value
On Error GoTo Error_Handler_3

'sorts pivot won top 5 revenue
Sheets("S&#34.PivotTables("PivotTable3&#34.PivotFields("Key&#34.AutoSort xlAscending _
, "Sum of ", Sheets("S&#34.PivotTables("PivotTable3&#34.PivotColumnAxis. _
PivotLines(1), 1
Error_Handler_3:
Resume Here_3
Here_3:
'selects pivot lost bottom 5 revenue
Sheets("S&#34.PivotTables("PivotTable4&#34.PivotFields("Key&#34.ClearAllFilters
Sheets("PS&#34.PivotTables("PivotTable4&#34.PivotFields("Key&#34.PivotFilters.Add _
Type:=xlCaptionContains, Value1:=Sheets("S&#34.Range("B4&#34.Value
On Error GoTo Error_Handler_4
'sorts pivot lost bottom 5 revenue
Sheets("S&#34.PivotTables("PivotTable4&#34.PivotFields("Key&#34.AutoSort xlAscending _
, "Sum of ", Sheets("S&#34.PivotTables("PivotTable4&#34.PivotColumnAxis. _
PivotLines(1), 1
Error_Handler_4:
If Application.WorksheetFunction.CountA(Sheets("S&#34.Range("C18:L18:R18:V18&#34) = 0 Then
MsgBox "SSSS"
Exit Sub
End If