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:
- If the sheet name matches an array value do nothing
- If there is no sheet name for an array value, add a sheet and name it the array value
- 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