add to array but unsure how to fill it

[KDS14589]

Each of my worksheets have a two part name (Division).(Purpose), such as Domain.Switchboard

I've been trying to get the first part of the name (Domain) before the separator "." and separately the second part (Purpose) after the separator ".".

I've tried formulas I have for this purpose; they work fine on individual worksheets but the VBA debugger gets mad at 'filename'.

My latest attempt is to increase the array from 4 to 6 as in SbG.Cells(rG, CodeNameClmG).Resize(, 4).Value = Array(WsG.CodeName, WsG.Name, WsG.Index, WsG.Visible, WsG.Division, WsG.Purpose) but I don’t know what to use in WsG.Division & WsG.Purpose

With this latest attempt I even Dim As String both Domain & Purpose but NNNNNNOOOOOO

Help will be appreciated with this or point me in correct path.

This code I'm sending doesn’t include some of these changes because with those changes the results disappear. The picture is from before those changes.

 Cells.Range("D5:G104").ClearContents
    
    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"}]
        [l1] = [{"Restricted Worksheets"}]
        [H4] = [{"Division"}]
        [I4] = [{"Purpose"}]
        
    ''''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

 

[johnnyL]

Does this help you out?

Sub TestSplitSheetName()
'
    Dim Division            As String
    Dim Purpose             As String
    Dim ThisSheetName       As String
'
    ThisSheetName = ActiveSheet.Name                    ' Get sheet name
'
    If InStr(ThisSheetName, ".") > 0 Then               ' If "." found in sheet name then ...
        Division = Split(ThisSheetName, ".")(0)         ' Save first part of sheet name into variable
        Purpose = Split(ThisSheetName, ".")(1)          ' Save second part of sheet name into variable
    End If
'
    MsgBox Division & " & " & Purpose
End Sub

 

[KDS14589]

Does this help you out?

Sub TestSplitSheetName()
'
    Dim Division            As String
    Dim Purpose             As String
    Dim ThisSheetName       As String
'
    ThisSheetName = ActiveSheet.Name                    ' Get sheet name
'
    If InStr(ThisSheetName, ".") > 0 Then               ' If "." found in sheet name then ...
        Division = Split(ThisSheetName, ".")(0)         ' Save first part of sheet name into variable
        Purpose = Split(ThisSheetName, ".")(1)          ' Save second part of sheet name into variable
    End If
'
    MsgBox Division & " & " & Purpose
End Sub

i tried it but now i get the results of the second worksheet in all rows.
i included the new code and a picture to show you wwat i get

'    '
    Dim Division            As String
    Dim Purpose             As String
    Dim ThisSheetName       As String

    ThisSheetName = ActiveSheet.Name                    ' Get sheet name

   ' If InStr(ThisSheetName, ".") > 0 Then               ' If "." found in sheet name then ...
'        Division = Split(ThisSheetName, ".")(0)         ' Save first part of sheet name into variable
'        Purpose = Split(ThisSheetName, ".")(1)          ' Save second part of sheet name into variable
    'End If
    
'''''''''     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
    
    
        For Each ws In ThisWorkbook.Worksheets
         If InStr(ThisSheetName, ".") > 0 Then
         Division = Split(ThisSheetName, ".")(0)         ' Save first part of sheet name into variable
        Purpose = Split(ThisSheetName, ".")(1)          ' Save second part of sheet name into variable
         
            'If InStr(1, ws.Name, Flt, vbTextCompare) = 1 Then
            Sb.Cells(r, NameClm).Resize(, 6).Value = Array(ws.Index, ws.Name, ws.CodeName, ws.Visible, Division, Purpose)
            r = r + 1
            End If
           Next ws

 

[Rick Rothstein]

I don't think your latest picture actually shows what you want, rather, it looks to me like what the code you have is producing. Let's see if we can get you to tell us directly what you want. Are you trying to take the values in Column E and split them out into Column H and I?

 

[johnnyL]

Are you trying to take the values in Column E and split them out into Column H and I?

@Rick Rothstein, It would appear so, that is why I provided an example of how to split a tab name into two pieces. I think @KDS14589 mistook it as an all around code for all tabs.

 

[Rick Rothstein]

If that is really all the OP wants to do, then this two-line macro will do that...

Sub SplitAtTheDotInColumnE()
  Intersect(Columns("H:I"), Rows("1:" & Rows.Count)).Clear
  Range("E5", Cells(Rows.Count, "E").End(xlUp)).TextToColumns Range("H5"), xlDelimited, , , False, False, False, False, True, "."
End Sub

 

[johnnyL]

I was thinking replacing the OP code of:

        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

with:

        For Each WS In ThisWorkbook.Worksheets
            If InStr(1, WS.Name, Flt, vbTextCompare) = 1 Then
                If InStr(WS.Name, ".") > 0 Then                     ' If "." found in sheet name then ...
                    Division = Split(WS.Name, ".")(0)         ' Save first part of sheet name into variable
                    Purpose = Split(WS.Name, ".")(1)          ' Save second part of sheet name into variable
'
                    Sb.Cells(r, NameClm).Resize(, 6).Value = Array(WS.Index, WS.Name, WS.CodeName, WS.Visible, Division, Purpose)
                End If
            Else
                Sb.Cells(r, NameClm).Resize(, 4).Value = Array(WS.Index, WS.Name, WS.CodeName, WS.Visible)
                r = r + 1
            End If
        Next WS

But I have not tested it.

 

[Rick Rothstein]

I think I am confused again. Your code is visiting each worksheet and splitting the name... I was under the impression Column E was already populated and the OP just needed to split those values to Columns H:I. I really don't have the time to try and digest the OP's posted code... I am guessing you did so your interpretation is probably correct (meaning my posted code won't help the OP at all).

 

[johnnyL]

I am confused also @Rick Rothstein. I think I was using the approach of the code. You are approaching the results. Who knows who is right. The OP code does not have code for the column H & I. That is what I addressed. The 2nd pic however had those columns filled in, that is what you addressed, I think that is our confusion.

 

[johnnyL]

Ahh I see @Rick Rothstein you were going off of column E results and using that approach, yeah, I can see that approach. Probably a better, simpler, approach. Would save a line of code.