Running Macro Across Multiple Sheets

[MCoster]

Hi,

I have a macro on the attached workbook that looks at one of the "Draft" sheets and runs the macro to look through the individual team tabs and returns what you see in Draft20 in Column F and G (if that player is in one of the tabs, that information from the tabs he's in).

In the past, I have just been manually moving each individual draft tab to the first tab in the workbook and then running the macro with the name of the sheet in it but is there some way I can just run all my draft tabs all at once?

Hopefully it's an easy solution.

(Skimmed down the draft tabs to comply with the rules of the forum)

Thanks for the help.

[gijsmo]

Without changing too much of your code, try the attached with updated "Draft" macro.

This will update all the sheets with the name "Draft" in them.

[jolivanes]

Try

Sub Maybe()
Dim a As Long, i As Long, j As Long
For a = 1 To 6
    For i = 2 To Sheets(a).Cells(Sheets(a).Rows.Count, 5).End(xlUp).Row
        For j = 7 To ThisWorkbook.Sheets.Count
            If Application.WorksheetFunction.CountIf(Sheets(j).UsedRange, Sheets(a).Cells(i, 5).Value) > 0 Then
                Sheets(a).Cells(i, 5).Offset(, 1).Value = Sheets(j).Range("A1").Value
                Sheets(a).Cells(i, 5).Offset(, 2).Value = Sheets(j).Cells(Sheets(j).UsedRange.Find(Sheets(a).Cells(i, 5).Value).Row, 1).Value
                Exit For
            End If
        Next j
    Next i
Next a
End Sub

[jindon]

Are you trying to do like this?
Order of the worksheets doesn't matter.

Sub Draft()
    Dim ws As Worksheet, sh As Worksheet
    Dim player As String, team As String
    Dim r As Range, c As Range
    For Each ws In Worksheets
        If ws.Name Like "Draft#*" Then
            For Each r In ws.Range("e2", ws.Range("e" & Rows.Count).End(xlUp))
                player = r.Value: r.Range("b1:c1").ClearContents
                For Each sh In Worksheets
                    If Not sh.Name Like "Draft*" Then
                        team = sh.[a1]
                        Set c = sh.Cells.Find(player, , , 1)
                        If Not c Is Nothing Then
                            r(, 2).Value = team
                            r(, 3).Value = c.EntireRow.Range("a1").Value
                            Exit For
                        End If
                    End If
                Next
            Next
        End If
    Next
End Sub