Excel/vba: Run macro on all sheets instead of only the active sheet

Asked Jan 03 '20 at 18:36

Active Jan 03 '20 at 18:36

Viewed 146 times

I'm having this macro to find "HFM" from the sheet and if it exist then having some names written after it. No problem there. But how do I get this to run on all sheets at once?

This is what I have now but it only runs on the active sheet.

Sub test()
Dim ws As Worksheet

For Each ws In Worksheets

' Search for a text
Set MyObjectvie = Cells.Find(What:="HFM", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

    If Not MyObjectvie Is Nothing Then
        ' if the text is found do the task
        Cells.Find(What:="HFM", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "name1"
    End If

Next ws
End Sub

asked Jan 03 '20 at 18:36

user12646795

1

Step 1: put `ws.` before each instance of `Cells` inside the loop. – BigBen Jan 03 '20 at 18:37
1

you need to append `Cells...` with `ws.` so `ws.Cells...` – Scott Craner Jan 03 '20 at 18:37
2

Step 2 change everything in you IF to `MyObjectvie.Offset(0,1).Value = "name1"` – Scott Craner Jan 03 '20 at 18:38
1

It would be useful to [read this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Jan 03 '20 at 18:42

Excel/vba: Run macro on all sheets instead of only the active sheet

0 Answers0