Finding column location in VBA based on column name

[littlejilly]

Trying to add several columns into my worksheet (tab) in a workbook that contains multiple tabs. However, I am now realizing that the data is stacked in a different order than I need so referring to the column by letter will not work. However, the column that i need is named consistently ("date"). I am not sure how I can easily reference this for the code included below.

Sub Check()
    Dim rng As Range
    Dim i As Long

    Range("A:A").Insert
    Range("B:B").Insert

    'Set the range in column A you want to loop through
    Set rng = Range("G:G")
    For Each cell In rng
        'test if cell is empty
        If Len(cell) > 0 Then
            'write source in column A
            cell.Offset(0, -6).Value = ThisWorkbook.Name & ActiveSheet.Name
            'write value in column B
            cell.Offset(0, -5).Value = 1
            
            'name column A as source
            Range("A1").Value = "Source"
            'name Column B as value
            Range("B1").Value = "Count"
        End If
    Next
End Sub

Set rng = Range("G:G")

Is where I would need to replace the column refers to the location of the column 'Date'

I am also looking to embed this code within an extrapolation function so that I can save each individual tab as its own workbook. The code I have written for this works well (below) but unsure where I could embed the above to conduct this in a single run. Any help is greatly appreciated!

Sub SplitEachWorksheet()
Dim FPath As String
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    ws.Copy
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Here is one way. Assuming "Date" will always be in row 1.

Set rng = Rows(1).Find("Date", , xlValues).EntireColumn