Loop each sheet between two sheets

DataBlake

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

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,917
Office Version
2019
Platform
Windows
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
 

DataBlake

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,564
Office Version
365
Platform
Windows
You can ignore hidden sheets like
Code:
   If Sheets(i).Visible = xlSheetVisible Then MsgBox Sheets(i).Name
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
779
Office Version
2016
Platform
Windows
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?
 

DataBlake

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,652
Office Version
365
Platform
Windows
Is it only the visible sheets that have names starting with 'Vendor...' that you want to run the code on?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,628
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.
 

DataBlake

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

DataBlake

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

Watch MrExcel Video

Forum statistics

Threads
1,102,210
Messages
5,485,359
Members
407,498
Latest member
LindaW

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top