Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACRO

[e4excel]

Dear Forum,

I need to create an Array which contains Sheet Names dynamically as I need to exclude a few Sheet Names from this array and include the rest...

This Sheet Array is to be used in a Macro..

I am not able to actually create an Array dynamically which can hold the Sheet Names.

So can someone guide me on this please..

Thanks in advance.
e4excel

[mehmetcik]

Click on the button.

[HaHoBe]

Hi, e4excel,

could you please make clear how the array should be created? Do you know which sheets will not be part of the array (always the same) or is the user free in her/his choice to do so? If so I would go with a ListBox but allow MultiSelect and then build the array from the selected items.

Ciao,
Holger

[xlbiznes]

Hi @e4excel,

Try this :

Dim Str_Names As String

Dim my_array() As String

' or create a public variable my_array() so that it can be referred anywhere in your code. Put this variable in a module and initialize it when you open this workbook.

'this variable can be used to store the sheet names

Str_Names = "one,two,three,four,five"

'Convery the str_names into an array

my_array = Split(Str_Names, ",")

'This is a zero based array - Hence you will get a count of 4.

'loop through the array

For x = 0 To UBound(my_array)

Debug.Print my_array(x)

Next

[e4excel]

Thanks all of you for extending help in this project..

But what I need is to automatically fill the Array variable with all the Sheet Names except a few sheets which I can provide..

Ex: There are seven Sheets and 2 Template Sheets Template 1 and Template 2.

So Five Sheet names need to go in the array or better is I may have more number of sheets but just need to exclude 2-3 sheets which I can mention using the LIKE operator.

But I want the remaining sheet names to get in the SHeet Array variable.

@ mehmetcik : Liked your approach though..

@ xlbiznes : I want the array to self fill with the names

e4excel

[HaHoBe]

Hi, e4excel,

maybe something like this - you would need to adapt the sheets not to include to suit:

Sub E969018()
Dim ws As Worksheet
Dim strSheets As String
Dim my_array

For Each ws In Worksheets
  Select Case ws.Name
    Case "Template 1", "Template 2"
    Case Else
      strSheets = strSheets & ws.Name & ","
  End Select
Next ws

my_array = Split(Left(strSheets, Len(strSheets) - 1), ",")

Sheets(my_array).Select

End Sub

Ciao,
Holger

[Norie]

Try this.

Dim ws As Worksheet 
Dim arrsheets()
Dim I As Long 

    For Each ws In ActiveWorkbook.Worksheets 
        If Not ws.Name Like "Template*" Then
            ReDim Preserve arrSheets(I)
            arrSheets(i) = ws.Name 
            I = I + 1
        End If
    Next ws

[e4excel]

Dear Hahobe,

Thanks for the code but please can you please explain it to me...Mybe I might want to use the LIKE operator to exlcude sheets which start with a particular name like "Temp*"

I would be using this array to store the names of all these sheets and then use in some other code to copy only these sheets..

Regards
e4excel

[HaHoBe]

Hi, e4excel,

I think this has been answered with Norie´s code.

Ciao,
Holger

[e4excel]

Abolsutely...

Thanks a lot everyone for the wonderful solution ..This is very helpful.



Regards
e4excel

[e4excel]

Dear Norie,

Please explain what Data tyoe is this "Dim arrsheets()"

and what does the statement do ?

            ReDim Preserve arrSheets(I)
            arrSheets(i) = ws.Name

Thanks in advance
Regards
e4excel

[HaHoBe]

Hi, e4excel,

Dim arrsheets()

is not specified as DataType and thus is handled as a Variant (like all other variables which are not exclusively being of a certain DataType are also).

ReDim Preserve arrSheets(I)

ReDim marks the scope of the array (usually extend it). Keyword Preserve is for holding the data which is present in the array, not using it would create a new array in the given measures. I being the counter for the array, when starting the value for I is 0 so the array will hold only one value. After each loop the value for I is increased by one to repeat the process.

arrSheets(i) = ws.Name

Assign the worksheet name to the postion in the array.

HTH
Holger

[Norie]

Holger

Couldn't have put it better myself.

[e4excel]

Thanks a lot for the explanatio Hahohobe