Loop each sheet between two sheets

[DataBlake]

so i'm trying to loop between sheets in between my sheets "Program Start" and "ID check"
and if hidden sheets matter it would be between "Program Start" and "Master Image"
i.e the loop logic would be:

for each sheet between ProgramStart to IDcheck
'do a thing
else 'do a thing
next sheet

the reason being as these sheets contain the data i work with but the sheets aren't consistant day to day.
so some days it will be vendor1, vendor 3, vendor 4.
other days it will be all 6 vendors etc

any help would be appreciated

 

[dmt32]

Hi,
to do what you want you can use the sheets Index property

Dim i As Integer
For i = Worksheets("ProgramStart").Index + 1 To Worksheets("IDcheck").Index - 1
   MsgBox Worksheets(i).Name
Next i

Hopefully, this will do what you want

Dave

 

[DataBlake]

Hopefully, this will do what you want

Dave

Thank you Dave!
You learn something new about VBA every day.

Note: this method does not ignore hidden sheets

 

[Fluff]

You can ignore hidden sheets like

   If Sheets(i).Visible = xlSheetVisible Then MsgBox Sheets(i).Name

 

[DataBlake]

Worksheets("ProgramStart").Index + 1 To Worksheets("IDcheck").Index - 1

how would i count this?
so lets say i have 3 sheets between program start and idcheck.
I want to define an array size like

ReDim shtNAME(Worksheets("Program Start").Index + 1 To Worksheets("Master Image").Index - 1).Count

but thats the incorrect syntax

if i take away .count it grabs 2 and 4 for the LBound and UBound of the array because i guess the index is the position of the sheet (i.e sheet 2 to sheet 4)
so how do i count it from 2 to 4 (being 3) instead?

 

[DataBlake]

i got a workaround, but probably isn't the most effective way

Sub Macro1()
Dim shtNAME As Variant
Dim ws As Worksheet
Dim sheeter As Long
Dim i As Long
Dim x As Long

' Measure Sheet Distance & Assign Array Size
For sheeter = Worksheets("Program Start").Index + 1 To Worksheets("Master Image").Index - 1
ReDim shtNAME(sheeter - (Worksheets("program start").Index + 1))
Next sheeter

' Define Array Values
For i = Worksheets("Program Start").Index + 1 To Worksheets("Master Image").Index - 1
   shtNAME(i - (Worksheets("program start").Index + 1)) = Worksheets(i).Name
Next i

' test prints
For x = LBound(shtNAME) To UBound(shtNAME)
    With ActiveSheet
    Cells(1, x + 1).Value = shtNAME(x)
    End With
    
  Next x
End Sub

if anyone wants to amend this code PLEASE DO

 

[Norie]

Is it only the visible sheets that have names starting with 'Vendor...' that you want to run the code on?

 

[mikerickson]

Try

Dim i As Long
Dim visibleCount as Long
Dim visibleSheets() as worksheets

Redim visibleSheets(1 to Worksheets.Count)

For i = Worksheets("ProgramStart").Index + 1 To Worksheets("IDcheck").Index - 1
    With Worksheets(i)
        If .Visible = xlSheetVisible Then
            VisibleCount = VisibleCount +1
            Set visibleSheets(VisibleCount) = Worksheets(i)
        End If
    End With
Next i

If 0 < VisibleCount Then
    ReDim Preserve VisibleSheets(1 to VisibleCount)
    For Each oneSheet in VisibleSheets
        onesheet.Cells(1, x + 1).Value = oneSheet.Name
    Next oneSheet
End If

Or you could omit the array and just put the "do stuff" code inside the first loops IF.

 

[DataBlake]

Try...
Or you could omit the array and just put the "do stuff" code inside the first loops IF.

i get type mismatch on

Set visibleSheets(visibleCount) = Worksheets(i)

i am running excel 2016 on windows 10

 

[DataBlake]

Is it only the visible sheets that have names starting with 'Vendor...' that you want to run the code on?

the names aren't actually "vendor 1" etc
it has to be dynamic in the sense that it won't index or count sheets by name, but rather whats in between the two sheets.