Open Multiple Excel and update Row value based on Header

[RickSorkin]

I am new to this- I was trying to find a way to update multiple sheets in folder with values in column based on header in the sheets:
Scenario:
I have Multiple excel files at location and want to update the rows data based on Header-
Example-
Data to be update ="1-New"
Header in the Multiple excel- "Status"

So I want to open each and every excel and update the column data to "1-New" if the Header is Status till the last row in excel

Previously it was hardcoded to column but I want to update based on header because in every file the Header position changes.

Need Help!

 

[mumps]

This macro assumes the headers are in row 1. Change the folder path (in red) and the sheet name (in blue) to suit your needs.

Sub UpdateRows()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, LastRow As Long, fnd As Range
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set desWB = Workbooks.Open(strPath & strExtension)
        With desWB.Sheets("Sheet1")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Set fnd = .Rows(1).Find("Status", LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                .Cells(2, fnd.Column).Resize(LastRow - 1).Value = "1-New"
            End If
        End With
        desWB.Close savechanges:=True
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[RickSorkin]

This macro assumes the headers are in row 1. Change the folder path (in red) and the sheet name (in blue) to suit your needs.

Sub UpdateRows()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, LastRow As Long, fnd As Range
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set desWB = Workbooks.Open(strPath & strExtension)
        With desWB.Sheets("Sheet1")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Set fnd = .Rows(1).Find("Status", LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                .Cells(2, fnd.Column).Resize(LastRow - 1).Value = "1-New"
            End If
        End With
        desWB.Close savechanges:=True
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Thank you for your response. This solved my problem. Thank you for taking time and helping out.

 

[mumps]

You are very welcome.