Defining Array

[BIMguy]

I'm using the following Array to go through a specific number of open sheets to add a specific range of cells.
What I have is working but sometimes the number of open sheets is different so I have to edit the macro to define how many sheets I want to add each time.
How can I either enter a number of sheets that are open (for example, this is 13 open sheets) or make it run no mater how many sheets are open?

Please help

Sub Add()

'

' Add Macro

'



Dim myValue As String

myValue = InputBox("Job Number?")



sheetlist = Array("Time Sheet", "Time Sheet (2)", "Time Sheet (3)", "Time Sheet (4)", "Time Sheet (5)", "Time Sheet (6)", "Time Sheet (7)", "Time Sheet (8)", "Time Sheet (9)", "Time Sheet (10)", "Time Sheet (11)", "Time Sheet (12)", "Time Sheet (13)")

For i = LBound(sheetlist) To UBound(sheetlist)

Worksheets(sheetlist(i)).Activate

'

ActiveSheet.Unprotect

 

[Alex Blakenburg]

What do you mean by the sheet being "open" ?

To cycle through all the sheets you could use this, which is straight off the Microsoft support site.
(I modified the worksheet variable name)

     Sub WorksheetLoop2()

         ' Declare Current as a worksheet object variable.
         Dim ws As Worksheet

         ' Loop through all of the worksheets in the active workbook.
         For Each ws In Worksheets

            ' Insert your code here.
            ' This line displays the worksheet name in a message box.
            MsgBox ws.Name
         Next

      End Sub

 

[NdNoviceHlp]

Hi BIMguy. You can refer to sheets by there internal index location. For example Sheets(1), Sheets(2)...etc. However, your Sheet1, Sheet2 (or Time Sheet(2) in your for instance) may not be ordered the same as the internal index. HTH. Dave

 

[Fluff]

If all the sheet names start with "Time Sheet" you could use

   Dim ws As Worksheet
   
   For Each ws In Worksheets
      If ws.Name Like "Time Sheet*" Then
         Debug.Print ws.Name
      End If
   Next ws

 

[BIMguy]

What do you mean by the sheet being "open" ?

To cycle through all the sheets you could use this, which is straight off the Microsoft support site.
(I modified the worksheet variable name)

     Sub WorksheetLoop2()

         ' Declare Current as a worksheet object variable.
         Dim ws As Worksheet

         ' Loop through all of the worksheets in the active workbook.
         For Each ws In Worksheets

            ' Insert your code here.
            ' This line displays the worksheet name in a message box.
            MsgBox ws.Name
         Next

      End Sub

Sorry, should say. Number of work sheets in an open work book.

 

[Alex Blakenburg]

Do any of the solutions suggested above work for you ?
If not, can you elaborate on in what way they are missing the mark ?

 

[BIMguy]

Do any of the solutions suggested above work for you ?
If not, can you elaborate on in what way they are missing the mark ?

Still testing, but I may have to post the entire code I have for you experts to better understand my request