# Create Worksheet array omitting sheets



## daverunt (Dec 19, 2022)

Hi 

I'm using the following code to create a dynamic worksheet array but have no idea how to omit 2 sheets by name from the array?
Any help appreciated.


```
ReDim wsList(1 To Sheets.Count)
    For i = 1 To Sheets.Count
        wsList(i) = ActiveWorkbook.Sheets(i).Name
    Next
```


----------



## Fluff (Dec 19, 2022)

How about
	
	
	
	
	
	



```
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
   Select Case Ws.Name
      Case "ABC", "XYZ"
      Case Else
         i = i + 1
         wsList(i) = Ws.Name
   End Select
Next Ws
```


----------



## Domenic (Dec 19, 2022)

Or, maybe something like this...


```
Dim sheetsToExclude As Variant
    sheetsToExclude = Array("Sheet1", "Sheet2") 'change accordingly

    ReDim wslist(1 To ActiveWorkbook.Sheets.Count)
    
    Dim sheetCount As Long
    sheetCount = 0

    Dim i As Long
    For i = 1 To Sheets.Count
        If IsError(Application.Match(ActiveWorkbook.Sheets(i).Name, sheetsToExclude, 0)) Then
            sheetCount = sheetCount + 1
            wslist(sheetCount) = ActiveWorkbook.Sheets(i).Name
        End If
    Next i
    
    If sheetCount = 0 Then
        MsgBox "No sheets found!", vbExclamation
        Exit Sub
    End If
    
    ReDim Preserve wslist(1 To sheetCount)
```

Hope this helps!


----------



## daverunt (Dec 19, 2022)

Fluff said:


> How about
> 
> 
> 
> ...


Hi Fluff,

thanks for the code.
It came up with subscript out of range even though i was 1 and the first ws.Name was corrcect.

I needed to add a Redim in there to get it to work as well as adding Option Base 1.
Got there in the end though.

Cheers


```
Option Base 1

For Each Ws In Workbooks(ReportFile).Worksheets
   Select Case Ws.Name
      Case "Profile", "Summary"
      Case Else
         i = i + 1
         ReDim Preserve wsList(i)
         wsList(i) = Ws.Name
   End Select
        
Next Ws
```


----------



## Fluff (Dec 19, 2022)

Glad you sorted it & thanks for the feedback.


----------



## daverunt (Dec 19, 2022)

Domenic said:


> Or, maybe something like this...
> 
> 
> ```
> ...


Hi Domenic, that works just fine but I'm going with the slightly more readable code (in my view). Cheers for your time though.
Now who do I mark as solving the problem?


----------



## Fluff (Dec 19, 2022)

daverunt said:


> Now who do I mark as solving the problem?


Which ever you prefer.


----------



## Domenic (Dec 19, 2022)

That's great, glad you have the solution you want.  And thanks for the feedback.

Cheers!


----------

