Loop and apply formatting

[apunc1]

I'm trying to loop through all worksheets in the active workbook, find a word in the first row, and if that word is found, format the entire column. the code I have below only works if on the active worksheet.

Sub qtyFormat()

    Dim ws As Worksheet
     Dim last_column As Integer
    For Each ws In ActiveWorkbook.Worksheets
         
        
         last_column = ws.Cells.find("*", [A1], , , xlByColumns, xlPrevious).Column
         
        'On Error Resume Next 'Will continue if an error results
        'ws.Range("A1") = ws.Name
         For iloop = 1 To last_column
            If InStr(1, ActiveSheet.Cells(1, iloop), "_qty", vbTextCompare) <> 0 Then
                ws.Columns(iloop).NumberFormat = "#,##0"
                
            End If
        Next iloop
         '***********************
         
    Next ws
End Sub

What's weird is If I uncomment 'ws.Range("A1") = ws.Name and comment out everything else, it actually loops through the worksheets and changes A1 to the sheet name. so something is off on the iloop maybe?

Thanks

[HaHoBe]

Hi, apunc1,

maybe just change

If InStr(1, ActiveSheet.Cells(1, iloop), "_qty", vbTextCompare) <> 0 Then

to

If InStr(1, ws.Cells(1, iloop), "_qty", vbTextCompare) <> 0 Then

Ciao,
Holger

[apunc1]

Thank you Holger, that works!
I also need to loop through the same worksheets and move every instance of "Volume" or "Revenue" to the end of the data, delete the first instances so that Volume and Revenue are at the end of the dataset instead of the middle. Do I need to start a new thread for that?

I have VBS that works for one sheet but when I try to make the script loop through sheets, strange things happen, so I'm trying in Excel VBA then run the macros from the Scripts. Your fix to to the excel vba also works when I call the macros with my scripts.
Thanks!

Hi, apunc1,

maybe just change

If InStr(1, ActiveSheet.Cells(1, iloop), "_qty", vbTextCompare) <> 0 Then

to

If InStr(1, ws.Cells(1, iloop), "_qty", vbTextCompare) <> 0 Then

Ciao,
Holger