Need help improving/condensing my VBA script

[SomethngWicked]

Hey guys, I have a VBA script that runs through a set of data and does the following:

Filters through the data and extracts a certain value. In this case, anything marked as 'Pen Distributions' and then it creates a table.
Next, adjusts the row height and converts two columns to a data formate
After that, it applies a CF statement whereby if the data value is less than three days old, highlight green, three to five days, yellow, and greater than seven days, red
finally it applies some additional formatting

The problem is I have this setup via 4 subprocedures. What is the best way to tighten/clean up this code so I only have one sub? Any help would be greatly appreciated.

Code is below:

Sub FilterPen()
   If Not Evaluate("isref(Pen Distributions!A1)") Then
      With ActiveSheet
         .Range("A1:L1").AutoFilter 6, "Pen Distributions"
         Worksheets.Add.Name = "Pen Distributions"
         .AutoFilter.Range.Copy Sheets("Pen Distributions").Range("a1")
         .AutoFilterMode = False
      End With
    With Sheets("Pen Distributions")
         .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "Pen"
      End With
      
      With Range("A1:L1").EntireColumn.AutoFit
      End With
      
   End If
End Sub

Sub AdjustRowHeight()
With Sheets("Pen Distributions")
 Dim finalRow As Integer
    Dim i As Integer
  finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Range("A1:A" & finalRow).EntireRow.AutoFit
    End With
    
    With Sheets("Pen Distributions")
     Columns("H:I").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Range("Pen[[#Headers],[Number]]").Select
    End With
    
End Sub

Sub MacroPenCF()
'
' Macro1 Macro
'

'
    Range(Range("I1"), Range("I1").End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOW()-I1<3"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
   Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(NOW()-I1>3,NOW()-I1<7)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOW()-I1>7"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Sub PenSortCF()
'
' PenSortCF Macro
'

'
    ActiveWorkbook.Worksheets("Pen Distributions").ListObjects("Pen"). _
        Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Pen Distributions").ListObjects("Pen"). _
        Sort.SortFields.Add Key:=Range("Pen[[#All],[Updated]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Pen Distributions").ListObjects( _
        "Pen").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("Pen[[#Headers],[Number]:[Task type]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("Pen[[#Headers],[Number]]").Select
End Sub

 

[rlv01]

There is nothing to prevent you from pasting the code into one big macro. If they ran as separate macros, and assuming you want to run them in the order posted, the code should execute the same way. I do notice that you have many ambiguous range references where Excel is going to default to ActiveSheet as the range parent but your code suggests you intended something else. That seems like potential trouble. One example:

    With Sheets("Pen Distributions")
        Columns("H:I").Select                         '<-- ambiguous Columns reference. Excel will assume you mean ActiveSheet.Columns
        Selection.NumberFormat = "mm/dd/yy;@"
        Range("Pen[[#Headers],[Number]]").Select      '<-- ambiguous range reference. Excel will assume you mean ActiveSheet.Range
    End With

Your 'With' statement suggest your intent is to have the col/range reference use worksheet "Pen Distributions", but that's not what's going to happen. In the happy circumstance where sheet "Pen Distributions" is the the Active Sheet, then all will be fine. If it is not, then your code will go off the rails. There are multiple instances sprinkled throughout your code.

If you intent was to reference worksheet "Pen Distributions" then the code should look like this

    With Sheets("Pen Distributions")
        .Columns("H:I").Select                    
        Selection.NumberFormat = "mm/dd/yy;@"
        .Range("Pen[[#Headers],[Number]]").Select    
    End With

You should review your code for every instance where "Range", "Columns", "Cells", or "Rows" appears without a preceding "." and decide if you are content with Excel interpreting them as "ActiveSheet.Range", "ActiveSheet.Columns", "ActiveSheet.Cells", or "ActiveSheet.Rows"