VBA Do While Loop Looking within Worksheet Name

Question

I am trying to create some coding to be used across several workbooks. Within the workbooks I want to update certain sheets. These specific sheets are always in the same exact format and I want to update the same exact cells every time.

I am trying to create a loop and the "Do While" coding looks at the sheet need to determine if it needs to loop or not.

Below is the code I am using, and I keep getting the run time error '424': object required in vba. Where I will put the rest of my coding I have a msgbox there as a place holder just to get the code to work.

 Do While WS.Name Like "P&L - "

If Range("S306") <> 0 Then

MsgBox ("tEST GOOD")

Worksheets(ActiveSheet.Index + 1).Select

End If

Loop

I'd stop now, read [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4088852), and then start over. If you continue down your current path you'll open yourself up to a world of misery. — Comintern, Oct 11 '18 at 15:02

score 0 · Answer 1 · answered Oct 11 '18 at 15:01

Perhaps something like this?

Sub tgr()

    Dim wb As Workbook
    Dim ws As Worksheet

    'Loop through each currently open Excel workbook
    'If you instead need to loop through files in a folder, code would be different
    For Each wb In Application.Workbooks
        'loop through all sheets in one of the workbooks
        For Each ws In wb.Worksheets
            'Compare the worksheet name and cell S306 value
            If ws.Name Like "P&L - *" _
            And ws.Range("S306") <> 0 Then
                'Match found, your code would go here
                MsgBox "Workbook: " & wb.Name & Chr(10) & _
                       "Worksheet: " & ws.Name
            End If
        Next ws
    Next wb

End Sub

Thank you, this is what i was trying to accomplish. I was stuck on trying to use the do while function. — drummingexcel, Oct 11 '18 at 17:51

VBA Do While Loop Looking within Worksheet Name

1 Answers1