Merging VBA for different Ranges.

M

mxalex229

New Member
Joined
Feb 9, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have two Ranges with the same code. Each works separately but not together. I want the double click event to specify which column to filter in the target column based on the range of the cell that is selected.
Example
If cell is in range C2:AH38 I want it to filter column 1 and 12
If cell is in range C41:AH45 I want it to filter column 11 and 12

The split in the code for the ranges is after case 34

How can I do this?


VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C41:AH45")) Is Nothing Then Exit Sub
    Select Case Target.Column
    Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
    Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
    Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -13)
                .AutoFilter 12, "Placeholder"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select


    If Intersect(Target, Range("C2:AH38")) Is Nothing Then Exit Sub
    Select Case Target.Range
        Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
        Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
        Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -13)
                .AutoFilter 12, "Placeholder"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select
End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
How about
VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("C41:AH45")) Is Nothing Then
      Select Case Target.Column
      Case Is = 3
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -2)
                  .AutoFilter 12, "Insurance Package"
              End With
      Case Is = 4
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -3)
                  .AutoFilter 12, "A valid fee for procedure"
              End With
      Case Is = 5
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -4)
                  .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
              End With
      Case Is = 6
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -5)
                  .AutoFilter 12, "Procedure code is missing"
              End With
      Case Is = 7
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -6)
                  .AutoFilter 12, "Provider(Null)"
              End With
      Case Is = 8
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -7)
                  .AutoFilter 12, "Provider"
              End With
      Case Is = 9
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -8)
                  .AutoFilter 12, "Relationship"
              End With
      Case Is = 10
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -9)
                  .AutoFilter 12, "Drug Unit Qualifier"
              End With
      Case Is = 11
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -10)
                  .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
              End With
      Case Is = 12
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -11)
                  .AutoFilter 12, "ICD-10 Diagnosis Code"
              End With
      Case Is = 13
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -12)
                  .AutoFilter 12, "Insurance Package(Direct)"
              End With
      Case Is = 14
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -13)
                  .AutoFilter 12, "Placeholder"
              End With
      Case Is = 15
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -14)
                  .AutoFilter 12, "Adjustments are only supported for single claims."
              End With
      Case Is = 16
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -15)
                  .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
              End With
      Case Is = 17
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -16)
                  .AutoFilter 12, "Invalid segment ordering"
              End With
      Case Is = 18
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -17)
                  .AutoFilter 12, "Department"
              End With
      Case Is = 19
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -18)
                  .AutoFilter 12, "NDC"
              End With
      Case Is = 20
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -19)
                  .AutoFilter 12, "All mappings for this message have been completed."
              End With
      Case Is = 21
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -20)
                  .AutoFilter 12, "Department(Null)"
              End With
      Case Is = 22
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -21)
                  .AutoFilter 12, "COUNTRY 10028   must be mapped."
              End With
      Case Is = 23
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -22)
                  .AutoFilter 12, "Procedure code is missing  Provider(Null)"
              End With
      Case Is = 24
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -23)
                  .AutoFilter 12, "Unexpectedly large units received"
              End With
      Case Is = 25
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -24)
                  .AutoFilter 12, ""
              End With
      Case Is = 26
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -25)
                  .AutoFilter 10, "Under 30"
              End With
      Case Is = 27
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -26)
                  .AutoFilter 10, "30-59"
              End With
      Case Is = 28
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -27)
                  .AutoFilter 10, "60-89"
              End With
      Case Is = 29
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -28)
                  .AutoFilter 10, "90-119"
              End With
      Case Is = 30
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -29)
                  .AutoFilter 10, "120-149"
              End With
      Case Is = 31
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -30)
                  .AutoFilter 10, "150-209"
              End With
      Case Is = 32
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -31)
                  .AutoFilter 10, "210-269"
              End With
      Case Is = 33
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -32)
                  .AutoFilter 10, "270-364"
              End With
      Case Is = 34
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -33)
                  .AutoFilter 10, "365"
              End With
      End Select
    ElseIf Not Intersect(Target, Range("C2:AH38")) Is Nothing Then
      Select Case Target.Column
          Case Is = 3
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -2)
                  .AutoFilter 12, "Insurance Package"
              End With
          Case Is = 4
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -3)
                  .AutoFilter 12, "A valid fee for procedure"
              End With
      Case Is = 5
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -4)
                  .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
              End With
      Case Is = 6
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -5)
                  .AutoFilter 12, "Procedure code is missing"
              End With
      Case Is = 7
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -6)
                  .AutoFilter 12, "Provider(Null)"
              End With
      Case Is = 8
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -7)
                  .AutoFilter 12, "Provider"
              End With
      Case Is = 9
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -8)
                  .AutoFilter 12, "Relationship"
              End With
      Case Is = 10
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -9)
                  .AutoFilter 12, "Drug Unit Qualifier"
              End With
          Case Is = 11
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -10)
                  .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
              End With
      Case Is = 12
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -11)
                  .AutoFilter 12, "ICD-10 Diagnosis Code"
              End With
      Case Is = 13
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -12)
                  .AutoFilter 12, "Insurance Package(Direct)"
              End With
      Case Is = 14
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -13)
                  .AutoFilter 12, "Placeholder"
              End With
      Case Is = 15
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -14)
                  .AutoFilter 12, "Adjustments are only supported for single claims."
              End With
      Case Is = 16
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -15)
                  .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
              End With
      Case Is = 17
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -16)
                  .AutoFilter 12, "Invalid segment ordering"
              End With
      Case Is = 18
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -17)
                  .AutoFilter 12, "Department"
              End With
      Case Is = 19
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -18)
                  .AutoFilter 12, "NDC"
              End With
      Case Is = 20
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -19)
                  .AutoFilter 12, "All mappings for this message have been completed."
              End With
      Case Is = 21
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -20)
                  .AutoFilter 12, "Department(Null)"
              End With
      Case Is = 22
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -21)
                  .AutoFilter 12, "COUNTRY 10028   must be mapped."
              End With
      Case Is = 23
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -22)
                  .AutoFilter 12, "Procedure code is missing  Provider(Null)"
              End With
      Case Is = 24
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -23)
                  .AutoFilter 12, "Unexpectedly large units received"
              End With
      Case Is = 25
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -24)
                  .AutoFilter 12, ""
              End With
      Case Is = 26
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -25)
                  .AutoFilter 10, "Under 30"
              End With
      Case Is = 27
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -26)
                  .AutoFilter 10, "30-59"
              End With
      Case Is = 28
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -27)
                  .AutoFilter 10, "60-89"
              End With
      Case Is = 29
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -28)
                  .AutoFilter 10, "90-119"
              End With
      Case Is = 30
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -29)
                  .AutoFilter 10, "120-149"
              End With
      Case Is = 31
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -30)
                  .AutoFilter 10, "150-209"
              End With
      Case Is = 32
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -31)
                  .AutoFilter 10, "210-269"
              End With
      Case Is = 33
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -32)
                  .AutoFilter 10, "270-364"
              End With
      Case Is = 34
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -33)
                  .AutoFilter 10, "365"
              End With
      End Select
   End If
End Sub
 
Upvote 0
Solution
That did not work.
I Tried this as well but it is not working either.
VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("C2:AH38")) Is Nothing Then
    Select Case Target.Column
        Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
        Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
        Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -13)
                .AutoFilter 12, "Patient"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select
    ElseIf Intersect(Target, Range("C41:AH45")) Is Nothing Then
    Select Case Target.Column
    Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
    Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
    Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -13)
                .AutoFilter 12, "Patient"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select
    End IF
End Sub
 
Upvote 0
Once entered, neither range filtered properly. Clicking in either targeted range then brings you to the correct sheet, and selects the targeted AutoFilter columns, but does not filter the content. It just displays as a highlighted/selected column.
However, If you remove either range of code and try either separately it works perfectly

WORKS FOR (C2:AH38)
VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C2:AH38")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
        Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
        Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -13)
                .AutoFilter 12, "Patient"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select
End Sub

Works for (C41:AH45)
VBA Code: 
If Intersect(Target, Range("C41:AH45")) Is Nothing Then
    Select Case Target.Column
    Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
    Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
    Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -13)
                .AutoFilter 12, "Patient"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select
End Sub
 
Upvote 0
None of the posted codes should be taking you to a different sheet or selecting anything.
If that is happening then you must have some other code doing that.
 
Upvote 0
This is the code currently on Sheet 2. If you double click on a cell, (C2) it brings you to sheet labeled "Table Data" and filters column 1 to a numerical value on sheet 2 cell A2 and column 12 to "Insurance Package". There is no other code in the file.
VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C2:AH38")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
        Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
        Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -13)
                .AutoFilter 12, "Patient"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select
End Sub
 
Upvote 0
mxalex229 said:
If you double click on a cell, (C2) it brings you to sheet labeled "Table Data"
Click to expand...
Not for me it doesn't & there is nothing in that code (that I can see) that would cause the active sheet to change.
 
Upvote 0
Fluff said:
Not for me it doesn't & there is nothing in that code (that I can see) that would cause the active sheet to change.
Click to expand...
Is there a way I can upload a sample file? I have one prepared with private data replaced with placeholders.
 
Upvote 0
You can upload to a site such as OneDrive DropBox, GoogleDrive. Mark for sharing & then post the link you are given here.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
493
Alex Blakenburg
A
J
  • Question
Merge 2 VBA code help
Replies
1
Views
465
Joe4
Joe4
M
  • Solved
Replacing delete with a value
Replies
3
Views
159
Alex Blakenburg
A
J
  • Question
Marco to add tabs to WKS with values from column A
Replies
4
Views
318
Skyybot
Skyybot
A
  • Question
VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section
Replies
1
Views
185
John_w
J

Forum statistics

Threads
1,214,952
Messages
6,122,454
Members
449,083
Latest member
Ava19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top