This question was never asked before.. 10. The file Cities.xlsx contains an AllC

Question

This question was never asked before.. 10. The file Cities.xlsx contains an AllCities sheet that list all cities where a company has offices. Write a sub that does the following: (1) For each city in the list, it checks whether there is a worksheet with the name of that city in the workbook, and if there isn’t one, it adds one; and (2) it deletes any city worksheet if the worksheet’s name is not the current AllCities list. The sub should be written so that it can be run at any time and will always respond with the current list of cities in AllCities sheet. (Note: Your sub should also work if the AllCities list contains exactly one city or no cities)

Hint you better consider the nested for loop. For example, for adding worksheets that exist in the list, your outer loop better uses 'for' loop to go through each city in the list and the inner loop uses 'for each' loop to visit each worksheet to compare the city name (from the list) and the worksheet name. If the city name doesn't exist in any worksheet, you add a new worksheet with the city name. For deleting worksheets that don't exist in the list, your outer loop better uses 'for each' loop to go through each worksheet name and the inner loop uses 'for' loop to go through the city names in the list. If the worksheet name doesn't exist in the list, the worksheet is deleted. Hope it helps.

The name of all the sheets are AllCities, Dallas, Denver, Salt Lake City, San Diego

Cities where a company does business Santa Fe Phoenix Kansas City Denver Sacramento Seattle San Diego Salt Lake City Las Vegas

The name of all the sheets are AllCities, Dallas, Denver, Salt Lake City, San Diego

Explanation / Answer

Sub PopulatingArrayVariable()
'PURPOSE: Dynamically Create Array Variable based on a Single Columned Table

Dim myArray() As Variant
Dim myTable As ListObject
Dim cell As Range
Dim x As Long

'Set path for Table variable
Set myTable = ActiveSheet.ListObjects("Table1")
  
'Create Array List from Table
TempArray = myTable.DataBodyRange.Columns(1)
  
'Convert from vertical to horizontal array list
myArray = Application.Transpose(TempArray)

'Loop through each item in the Table Array (displayed in Immediate Window [ctrl + g])
For x = LBound(myArray) To UBound(myArray)
Debug.Print myArray(x)
Next x

End Sub

Function sheetExists(sheetToFind As String) As Boolean
sheetExists = False
For Each sheet In Worksheets
If sheetToFind = sheet.name Then
sheetExists = True
Exit Function
End If
Next sheet
End Function

Sub sbDeleteASheet()
Sheet1.Delete
'OR You can mention the Sheet name
Sheets("Sheet2").Delete
End Sub

Sub AddWorksheet()
Worksheets.Add().Name = "MySheet"
End Sub

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.