Clean up VBA code (Network Steve Forum)

Clean up VBA code

I need help cleaning this up. I'm fairly new to the process (only dabbled in it for a couple of years) and most of it seems bulky. Any help is appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("D1")) Is Nothing Then Exit Sub

On Error GoTo Error_handler

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = Worksheets("Pivot").PivotTables("DrillingInt")
Set Field = pt.PivotFields("AFE")
NewCat = Worksheets("Well Detail").Range("D20").Value
NewCat2 = Worksheets("Well Detail").Range("A1").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

Set pt = Worksheets("Pivot").PivotTables("CompInt")
Set Field = pt.PivotFields("AFE")
NewCat = Worksheets("Well Detail").Range("D112").Value
NewCat2 = Worksheets("Well Detail").Range("A1").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

Set pt = Worksheets("Pivot").PivotTables("EquipInt")
Set Field = pt.PivotFields("AFE")
NewCat = Worksheets("Well Detail").Range("D204").Value
NewCat2 = Worksheets("Well Detail").Range("A1").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

    Worksheets("Pivot").PivotTables("DrillingInt").PivotFields("Account Number"). _
        ClearLabelFilters
    Worksheets("Pivot").PivotTables("DrillingInt").PivotFields("Account Number"). _
        PivotFilters.Add2 Type:=xlCaptionIsNotBetween, Value1:="71000", Value2:= _
        "72000"
    Worksheets("Pivot").PivotTables("DrillingTan").PivotFields("Account Number"). _
        ClearLabelFilters
    Worksheets("Pivot").PivotTables("DrillingTan").PivotFields("Account Number"). _
        PivotFilters.Add2 Type:=xlCaptionIsBetween, Value1:="71000", Value2:= _
        "72000"
    Worksheets("Pivot").PivotTables("CompInt").PivotFields("Account Number"). _
        ClearLabelFilters
    Worksheets("Pivot").PivotTables("CompInt").PivotFields("Account Number").PivotFilters. _
        Add2 Type:=xlCaptionIsNotBetween, Value1:="71000", Value2:="72000"
    Worksheets("Pivot").PivotTables("CompTan").PivotFields("Account Number"). _
        ClearLabelFilters
    Worksheets("Pivot").PivotTables("CompTan").PivotFields("Account Number").PivotFilters. _
        Add2 Type:=xlCaptionIsBetween, Value1:="71000", Value2:="72000"
    Worksheets("Pivot").PivotTables("EquipInt").PivotFields("Account Number"). _
        ClearLabelFilters
    Worksheets("Pivot").PivotTables("EquipInt").PivotFields("Account Number").PivotFilters. _
        Add2 Type:=xlCaptionIsNotBetween, Value1:="71000", Value2:="72000"
    Worksheets("Pivot").PivotTables("EquipTan").PivotFields("Account Number"). _
        ClearLabelFilters
    Worksheets("Pivot").PivotTables("EquipTan").PivotFields("Account Number").PivotFilters. _
        Add2 Type:=xlCaptionIsBetween, Value1:="71000", Value2:="72000"

Exit Sub

Error_handler:
Field.ClearAllFilters
Field.CurrentPage = NewCat2
pt.RefreshTable
If Result = vbOK Then Resume Next

End Sub

March 10th, 2015 3:29pm

Re: cleanup code he said

This looks a little better, it may even still work.
Also, the first line in every code module should be: Option Explicit
'---
Private Sub WorksheetChanges_SelectionChange(ByVal Target As Range)
On Error GoTo Error_handler
If Intersect(Target, Range("D1")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Dim NewCat2 As String

Set pt = Worksheets("Pivot").PivotTables("DrillingInt")
Set Field = pt.PivotFields("AFE")
NewCat = Worksheets("Well Detail").Range("D20").Value
'NewCat2 = Worksheets("Well Detail").Range("A1").Value

Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable

Set pt = Worksheets("Pivot").PivotTables("CompInt")
Set Field = pt.PivotFields("AFE")
NewCat = Worksheets("Well Detail").Range("D112").Value
'NewCat2 = Worksheets("Well Detail").Range("A1").Value

Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable

Set pt = Worksheets("Pivot").PivotTables("EquipInt")
Set Field = pt.PivotFields("AFE")
NewCat = Worksheets("Well Detail").Range("D204").Value
NewCat2 = Worksheets("Well Detail").Range("A1").Value

Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable

With Worksheets("Pivot").PivotTables("DrillingInt").PivotFields("Account Number")
     .ClearLabelFilters
     .PivotFilters.Add2 Type:=xlCaptionIsNotBetween, Value1:="71000", Value2:="72000"
End With
With Worksheets("Pivot").PivotTables("DrillingTan").PivotFields("Account Number")
     .ClearLabelFilters
     .PivotFilters.Add2 Type:=xlCaptionIsBetween, Value1:="71000", Value2:="72000"
End With
With Worksheets("Pivot").PivotTables("CompInt").PivotFields("Account Number")
     .ClearLabelFilters
     .PivotFilters.Add2 Type:=xlCaptionIsNotBetween, Value1:="71000", Value2:="72000"
End With
With Worksheets("Pivot").PivotTables("CompTan").PivotFields("Account Number")
     .ClearLabelFilters
     .PivotFilters.Add2 Type:=xlCaptionIsBetween, Value1:="71000", Value2:="72000"
End With
With Worksheets("Pivot").PivotTables("EquipInt").PivotFields("Account Number")
     .ClearLabelFilters
     .PivotFilters.Add2 Type:=xlCaptionIsNotBetween, Value1:="71000", Value2:="72000"
End With
With Worksheets("Pivot").PivotTables("EquipTan").PivotFields("Account Number")
     .ClearLabelFilters
     .PivotFilters.Add2 Type:=xlCaptionIsBetween, Value1:="71000", Value2:="72000"
End With
Exit Sub

Error_handler:
Field.ClearAllFilters
Field.CurrentPage = NewCat2
pt.RefreshTable
'If Result = vbOK Then Resume Next
End Sub
'---

Jim Cone
Portland, Oregon USA
free & commercial excel programs (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

Free Windows Admin Tool Kit Click here and download it now

March 10th, 2015 7:24pm

Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator Wednesday, March 18, 2015 2:08 AM

March 10th, 2015 11:22pm

Hi,

Please try Mr. Jim's suggestion first, and this is the forum to discuss questions and feedback for Microsoft Excel, if you have any further question about coding, please post your question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support

Free Windows Admin Tool Kit Click here and download it now

March 11th, 2015 9:54pm

Sorry. Will do.

March 19th, 2015 10:17am

This topic is archived. No further replies will be accepted.