Loop each sheet between two sheets

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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:

Code:
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
 
Last edited:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi,
to do what you want you can use the sheets Index property

Code:
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
 
Upvote 0
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
 
Last edited:
Upvote 0
You can ignore hidden sheets like
Code:
   If Sheets(i).Visible = xlSheetVisible Then MsgBox Sheets(i).Name
 
Upvote 0
Code:
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

Code:
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?
 
Upvote 0
i got a workaround, but probably isn't the most effective way

Code:
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
 
Upvote 0
Is it only the visible sheets that have names starting with 'Vendor...' that you want to run the code on?
 
Upvote 0
Try
Code:
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.
 
Upvote 0
Try...
Or you could omit the array and just put the "do stuff" code inside the first loops IF.



i get type mismatch on
Code:
Set visibleSheets(visibleCount) = Worksheets(i)
i am running excel 2016 on windows 10
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top