Help with trimming text in column with consistent header name but inconsistent column location

[kalamazoo]

Hello all,

I am trying to trim text that is in a column with a consistent header name in the worksheets but will be in different column locations (for instance sometimes in column AY and sometimes in column AZ). This is the macro I recorded where I searched for the column header name, inserted a column next to it then did text to columns to separate them at the point I wanted them to separate at. Is there any way to get this to where it would work even when the column with that column name is in a different spot?

Thanks,

Jordan

Sub Trim_Text()
'
' Trim_Date Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Selection.Find(What:="ColumnHeaderName", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Columns("AZ:AZ").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AY:AY").Select
Selection.TextToColumns Destination:=Range("AY1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(9, 1)), TrailingMinusNumbers:=True
End Sub

 

[Warship]

This will look in Row 1 on all Sheets

Sub FindCol()
    Dim ColName As String
    Dim sh As Worksheet
    Dim fnd As Range
    
    ColName = "theCol"
    
    For Each sh In Worksheets
        Set fnd = sh.Rows(1).Find(ColName, , , xlWhole)
        If Not fnd Is Nothing Then
            'do your thing here
        End If
    Next sh
End Sub