I'm using the following code to produce a list of worksheets in a workbook I'm working on, it lists the properties I'm interested in. I even got a Replacement Code further down the code to replace Visibility numbers with text., I'm trying to get a restricted list with the same results but for ws.codenames starting with "ShGE##", right now there are only 3 (ShGE01, ShGE02, ShGE03) but a lot is in the works.
List would populate: K4 = index (list will be K5:K200)
L4=name (list will be L5:L200)
M4=codename (list will be M5:M200)
N4=visibility (list will be N5:N200)
I would list my failures but not enough time or space (I even tried LIKE)
Any Help or suggestions would be appreciated.
List would populate: K4 = index (list will be K5:K200)
L4=name (list will be L5:L200)
M4=codename (list will be M5:M200)
N4=visibility (list will be N5:N200)
I would list my failures but not enough time or space (I even tried LIKE)
Any Help or suggestions would be appreciated.
VBA Code:
Const SwitchBoardName As String = "general.misc"
Const FilterCell As String = "b5"
Const OutputRow As Long = 5
Const IndexClm As String = "c"
Const NameClm As String = "d"
Const VisibleClm As String = "h"
Const CodeNameClm As String = "e"
Dim Sb As Worksheet
Dim Flt As String
Dim TabNames() As String
Dim r As Long
Dim Ws As Worksheet
Dim Rng As Range
Set Sb = ThisWorkbook.Worksheets(SwitchBoardName)
Flt = Sb.Range(FilterCell).Cells(1).Value
ReDim TabNames(ThisWorkbook.Worksheets.Count)
r = OutputRow
[e4] = [{"Name"}]
[f4] = [{"CodeName"}]
[d4] = [{"Index"}]
[g4] = [{"Visibility"}]
''''order by [index] accending by Fluff @ Mr Excell
For Each Ws In ThisWorkbook.Worksheets
If InStr(1, Ws.Name, Flt, vbTextCompare) = 1 Then
Sb.Cells(r, NameClm).Resize(, 4).Value = Array(Ws.Index, Ws.Name, Ws.CodeName, Ws.Visible)
r = r + 1
End If
Next Ws
If r Then
Set Rng = Sb.Range(Sb.Cells(OutputRow, NameClm), Sb.Cells(r - 1, NameClm))
With Sb.Sort
With .SortFields
.Clear
.Add Key:=Rng.Cells(1), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
End With
.SetRange Rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'''''General worksheets list'''''