Attribute VB_Name = "createSheets"
Sub createOrUpdateArticles()
Dim cellValue As String
Dim articles As Variant
Dim ws As Worksheet
Dim keepSheets As Object

' Initialize the collection for sheets to keep
    Set keepSheets = CreateObject("Scripting.Dictionary")

' Static sheets to keep
    keepSheets.Add "Setup Data", True
    'keepSheets.Add "8oz data", True
    'keepSheets.Add "10oz data", True
    'keepSheets.Add "COA Needs", True
    'keepSheets.Add "Approved COA's", True
    'keepSheets.Add "Forecast", True
    'keepSheets.Add "Truck list", True
    'keepSheets.Add "planningNumbers", True
    'keepSheets.Add "productionNumbers", True
    'keepSheets.Add "stockNumbers", True
    'keepSheets.Add "Delivered", True

'get the av data based on what is in cell b24

cellValue = ThisWorkbook.Sheets("Setup Data").Range("B4").Value
articles = Split(cellValue, ",")
count = UBound(articles) - LBound(articles) + 1

' clear the table so when we run the setup it only shows us clean data.
sheets("Setup Data").range("A19:G" & 19 + count + 20).ClearContents

' create the table start for the av we will be utlizeing.
dim i as Integer

For i = LBound(articles) To UBound(articles)
' Get a trimmed version of the article name for easier use
        sheetName = "Article " & Trim(articles(i))
        keepSheets.Add sheetName, True
        ' Debug output for tracking
        Debug.Print "Article to be placed is: " & Trim(articles(i))
        
        ' Place the articles in the Setup Data Sheet at specific range
        'ThisWorkbook.Sheets("Setup Data").Range("A" & 19 + i).Value = Trim(articles(i))
        
        ' Initialize the ws object to Nothing before checking existence
        Set ws = Nothing
        
        ' Attempt to set ws to an existing sheet with the desired name
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(sheetName)
        On Error GoTo 0
        
        ' If the sheet doesn't exist, add it
        If ws Is Nothing Then
            On Error Resume Next
            Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            ws.Name = sheetName
            call formatArticleSheet.formatArticleSheet(Trim(articles(i)), sheetName)
            On Error GoTo 0
        Else
            ws.Visible = xlSheetVisible
        End If

Next i

' Clean up the sheets so we only ever have the correct data.
    For Each ws In ThisWorkbook.Worksheets
        wsName = ws.Name
        If keepSheets.exists(wsName) Then
            Debug.Print wsName & " is inside the keepSheets Dictionary"
        Else
            Debug.Print wsName & " is not inside the keepSheets Dictionary"
            Application.DisplayAlerts = False
            ' Uncomment to delete or hide based on your preference
            ws.Delete
            ' ws.Visible = xlSheetHidden
            Application.DisplayAlerts = True
        End If
    Next ws

call getArticleData.getArticleData
Exit Sub
    
ErrorHandler:
    ' Handle specific error for connection issues
    If Err.Number = -2147012867 Then
        MsgBox "Please make sure you are connected to vpn, and or make sure the server is online.", vbCritical
    ElseIf Err.Number = -2147012894 Then
        MsgBox "Server is not reachable please try again later."
    
        ' Handle other errors
        MsgBox "An unexpected error occurred: " & Err.Description, vbCritical
    End If


End Sub