Defining Array

BIMguy

New Member
Joined
Mar 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,205
Office Version
  1. 365
Platform
  1. Windows
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)

VBA Code:
     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

Well-known Member
Joined
Nov 9, 2002
Messages
2,791
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,325
Office Version
  1. 365
Platform
  1. Windows
If all the sheet names start with "Time Sheet" you could use
VBA Code:
   Dim ws As Worksheet
   
   For Each ws In Worksheets
      If ws.Name Like "Time Sheet*" Then
         Debug.Print ws.Name
      End If
   Next ws
 
Solution

BIMguy

New Member
Joined
Mar 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)

VBA Code:
     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

Well-known Member
Joined
Feb 23, 2021
Messages
1,205
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,137,154
Messages
5,679,914
Members
419,862
Latest member
Bluewings666

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
Top