CODEWITHSUNDEEP
excelvbaforeach
Nick
Nick

Reputation: 11

Loop through each sheets to look for name containing value, do something, loop again looking for new value

I am trying to loop through sheets in a workbook, select those with names alike, save them to a PDF, then loop again but change the name it looks for in the sheets. The sheets will have in common a number that is 4 digits like 0001 or 0243 and the four digits are important so sheets 0030 and 0003 do not get combined.

I have gotten the code to work on just one name to find (below) but cannot figure out how to make it loop through more names. Tried making the name an integer and add 1 to it after each loop, tried very briefly to make it loop through array values but that did not work how I tried. 

For Each sh In ActiveWorkbook.Worksheets

    If sh.Name Like "*0001*" Then

        Worksheets(sh.Name).Select (False)
        ActiveSheet.ExportAsFixedFormat _
            ''' pdf format and save location'''

    End If

Next sh

If it could loop through each digit from 0001 to 0300 that would be ideal so I do not need to edit code each time I run it to add the new sheet names. However if an array will work best I can make a long array for the numbers I know will be present in the file and add new ones each month as needed.

Upvotes: 1

Views: 178

Answers (2)

Jan
Jan

Reputation: 421

You could use a function to grab numbers from the tab name. Looks like you only want tabs that include 100 thru 300. How 'bout trying this:

For Each sh In ActiveWorkbook.Worksheets
    vNumName = NumOnly(sh.Name)
    If vNumName >= 100 And vNumName <= 300 Then
        Worksheets(sh.Name).Select (False)
        ActiveSheet.ExportAsFixedFormat _
            ''' pdf format and save location'''
    End If
Next sh

The function:

Function NumOnly(vName As String) As Integer
    vOut = ""
    For i = 1 To Len(vName)
        If Mid(vName, i, 1) >= "0" And Mid(vName, i, 1) <= "9" Then vOut = vOut & Mid(vName, i, 1)
    Next
    If Len(vOut) >= 4 Then
        NumOnly = vOut
    Else
        NumOnly = 0
    End If
End Function

Upvotes: 1

Clauric
Clauric

Reputation: 1886

I would use a code similar to the below:

Dim s As Worksheet, t As String
Dim i As Long, K As Long
K = Sheets.Count

For i = 1 to i = K
    t = Sheets(i).Name

    Sheets(i).activate

    ' Do your activities within this loop
Next i

Upvotes: 0

Related Questions