VBA Loop through all worksheets in a file , find a specific value and delete all rows above that value

[Giggs1991]

Hi All,

I have a work book with the word " Work Order" in many of the worksheets.

I am looking for a VBA code that would loop through all worksheets , find the cell containing the term "work order" and delete all rows above it.


Regards

 

[aRandomHelper]

Sub me1158548()
    Dim ws As Worksheet, f As Range
    
    For Each ws In Sheets
        Set f = ws.Cells.Find("Work Order", lookat:=xlWhole)
        If Not f Is Nothing Then
            f.EntireColumn.Delete
        End If
    
    Next
End Sub

 

[Giggs1991]

thank you so much.
Is it possible to get vba to select range A1 to BZ1 in all worksheets and change format to "mmm-yy" , change background colour in this range to black and make font bold with white font colour.

 

[aRandomHelper]

Yes it's possible, but it is preferable that if you have any new questions, they be asked in a new thread.

Here's what you need:

    For Each ws In Sheets
        With ws.Range("A1:BZ1")
            .NumberFormat = "mmm-yy"
            .Interior.Color = 0
            .Font.Color = vbWhite
        End With
    Next

 

[Giggs1991]

Yes it's possible, but it is preferable that if you have any new questions, they be asked in a new thread.

Here's what you need:

    For Each ws In Sheets
        With ws.Range("A1:BZ1")
            .NumberFormat = "mmm-yy"
            .Interior.Color = 0
            .Font.Color = vbWhite
        End With
    Next

This one also works well. I had another question regarding looping through worksheets. As requested, I have posted it as a new thread here : VBA Loop through all worksheets in a file , find a specific value and update the cell to the right

Please help if possible.