Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am working on a piece of code that creates an array and populates it based on the contents of a column in an Excel Sheet. I would then like to use this array to add or delete Excel Sheets.

Actions I'd like the Macro to do:

  1. If the sheet name matches an array value do nothing
  2. If there is no sheet name for an array value, add a sheet and name it the array value
  3. If there is a sheet that does not exist in the array, delete the sheet.

I can populate the array with the values, but I am having a difficult time adding/deleting sheets based on the array values. I have noted the spot I am stuck in my code.

Sub CheckCities()

'Declare Variable
Dim rngCities As Range
Dim rngCityName As Range
Dim ws As Worksheet
Dim arrCityName() As String
Dim counter As Integer
Dim intWsCount As Integer

'Reset and erase array at start of program.  Allows for proper data in array
Erase arrCityName

'initialize counter variable
counter = 0

'Set Range Name for wsData Customers
With wsAllCities1.Range("A2")
    Set rngCities = Range(.Offset(0, 0), .End(xlDown))
End With

''''''''''''''''''''''''''''''''''''''''''''
' For Loop through Each City in rngCities
' adds current rngCities cell value to array
''''''''''''''''''''''''''''''''''''''''''''
For Each rngCityName In rngCities.Cells
    'Debug.Print rngCityName.Value ' Print the values of each cell

    counter = counter + 1 'Step up counter variable by 1

    ReDim Preserve arrCityName(0 To rngCities.Count)
    arrCityName(counter) = rngCityName.Value 'use the counter variable to create Array(#)

Next rngCityName

'''''''''''''''''''''''''''''''''''''''''''''''''''
'Test to verify Array was populated with City Names
'''''''''''''''''''''''''''''''''''''''''''''''''''

'wsAllCities1.Range("E2").Value = arrCityName(0)
'wsAllCities1.Range("E3").Value = arrCityName(1)
'wsAllCities1.Range("E4").Value = arrCityName(2)
'wsAllCities1.Range("E5").Value = arrCityName(3)
'wsAllCities1.Range("E6").Value = arrCityName(4)
'wsAllCities1.Range("E7").Value = arrCityName(5)
'wsAllCities1.Range("E8").Value = arrCityName(6)
'wsAllCities1.Range("E9").Value = arrCityName(7)
'wsAllCities1.Range("E10").Value = arrCityName(8)
'wsAllCities1.Range("E11").Value = arrCityName(9)



''''''''''''''''''''''''''''''''''''''''''''
' Loop statement to check sheet names
' adds or deletes sheets via arrCityName values
''''''''''''''''''''''''''''''''''''''''''''

''''STUCK ON CODE BELOW''''''''''''''''
''''STUCK ON CODE BELOW''''''''''''''''
''''STUCK ON CODE BELOW''''''''''''''''
''''STUCK ON CODE BELOW''''''''''''''''
''''STUCK ON CODE BELOW''''''''''''''''
''''STUCK ON CODE BELOW''''''''''''''''

intWsCount = ThisWorkbook.Worksheets.Count 'Count Number of Worksheets in this workbook

For Each ws In ThisWorkbook.Worksheets
    counter = 0 'set variable
    Do
        ws.Activate 'activate the next worksheet in the look
        If ws.Name <> "AllCities" Then
            For Each arrayItem In arrCityName
                If arrCityName = ws.Name Then
                    Debug.Print "City Name Found!"

                ElseIf arrCityName <> ws.Name Then


                End If

            Next


            Debug.Print "This city, " & ws.Name & ", does not exist in city list"

        End If

    Loop Until intWsCount 'Loop (x) number of times.  X is determinted by variable intWsCount

Next


End Sub
share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

You can run two separate loops. One loop to add sheets. One loop to delete sheets:

Sub dural()
    Dim DesiredSheets(1 To 3) As String
    Dim KillIt As Boolean, AddIt As Boolean
    DesiredSheets(1) = "Sheet1"
    DesiredSheets(2) = "Sheet2"
    DesiredSheets(3) = "Whatever"

    For Each sh In Sheets
        KillIt = True
        v = sh.Name
        For Each a In DesiredSheets
            If v = a Then
            KillIt = False
            End If
        Next a
        If KillIt Then sh.Delete
    Next sh

    For Each a In DesiredSheets
        AddIt = True
        For Each sh In Sheets
            If a = sh.Name Then
                AddIt = False
            End If
        Next sh
        If AddIt Then
            Sheets.Add
            ActiveSheet.Name = a
        End If
    Next a
End Sub
share|improve this answer
    
This is ultimately what I ended up doing was to run to different loops using a boolean value. While I did not use your exact code, it did help me figure out what to do. Thanks! –  user3512783 Apr 9 at 21:45
    
You are quite welcome! –  Gary's Student Apr 9 at 21:47
add comment

Untested:

Sub CheckCities()

    'Declare Variable
    Dim rngCities As Range
    Dim rngCityName As Range
    Dim ws As Worksheet
    Dim arrCityName() As String
    Dim counter As long
    Dim x as long, nm as string

    With wsAllCities1
        Set rngCities = .Range(.Range("A2").Offset(0, 0), _
                               .Range("A2").End(xlDown))
    End With
    ReDim Preserve arrCityName(1 To rngCities.Cells.Count)

    counter=0
    For Each rngCityName In rngCities.Cells
        counter = counter + 1 
        arrCityName(counter) = rngCityName.Value 
    Next rngCityName

    for x=1 to counter

        nm = arrCityName(x)
        set ws = nothing
        on error resume next 'ignore error if no sheet found
        set ws = thisworkbook.sheets(nm)
        on error goto 0       'stop ignoring errors

        if ws is nothing then
            set ws = thisworkbook.worksheets.add()
            ws.Name = nm
            debug.print "Added sheet '" & nm & "'"
        else
            debug.print "Sheet '" & nm & "' already exists"
        end if

    next x

End Sub
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.