Vba code to loop through all selected sheets-CodePudding

im trying to create a VBA code to search for a a persons code in column "B" and return the offset of the column in column "D" and replace the value of the cell with a new input i managed to do so but i need to do it across all selected sheets i dont know what im doing wrong because when i select several sheets it just executes the code on the first sheet and ignores the rest.

https://drive.google.com/file/d/1Kdol7qnfgstSc0mWV5hN9X0O1PcVwsjx/view?usp=sharing

Sub RunCode()
    Dim rg As Range, c As Range
    Dim str As String
    Dim A As Variant
    Dim ws As Worksheet

    Set rg = ActiveSheet.Columns("B")
    str = "PEC-00" & Application.InputBox(Prompt:="ID: ")
    A = Application.InputBox(Prompt:="New Value: ")
    With rg
        Set c = .Find(str, , xlValues)
        
        Application.ScreenUpdating = False
        For Each ws In ActiveWindow.SelectedSheets
             ws.Select       
             c.Offset(, 2) = A
        Next ws
        Application.ScreenUpdating = True
        
    End With
End Sub

i tried to loop the code to all selcted sheets but the codes still executes on only the first sheet selected

CodePudding user response：

Sub RunCode()
    Dim rg As Range, c As Range
    Dim str As String
    Dim A As Variant
    Dim ws As Worksheet

    Application.ScreenUpdating = False

    str = "PEC-00" & Application.InputBox(Prompt:="ID: ")
    A = Application.InputBox(Prompt:="New Value: ")
        
    For Each ws In ActiveWindow.SelectedSheets
        Set rg = ws.Columns("B")
        Set c = rg.Find(str, , xlValues)
        c.Offset(, 2) = A
    Next ws
        
    Application.ScreenUpdating = True
End Sub

Note: this is untested