How to copy a range to several worksheets by using a list of worksheets to copy to

VBA novice here. I have a complex spreadsheet but I’ll simplify it for my question. Let’s say I have a workbook with 20 worksheets. Each worksheet has a three-letter name such as ABC. In the first worksheet is a range called CITIES in cells A1:A10. Each cell in CITIES has the name of a city. Also in the first worksheet in cells B1:B10 is a list of 10 of the worksheet names. I change this list periodically. I need a macro to copy the range CITIES into cell C1 of each worksheet listed in B1:B10.

With my macro (shown below), I have to change the worksheet names in the macro each time I make a change in the B1:B10 list. I want the macro to use the B1:B10 list to determine which worksheets to copy to.

Thanks in advance for any help you can provide.

Sub CITIES_COPY ()
    Range("CITIES").Select
    Selection.Copy
    Sheets(Array("ABC", "DEF", "GHI", "JKL", "MNO", "PQR", "STU", "VWX", "YZA", "BCD")). _
        Select
    Sheets("ABC").Activate
    Range("C1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

Solution

My example below shows a few things that can help, especiallly when you're just starting out with VBA.

Use descriptive names for your variables and be as absolutely clear as you can.
You don't have to use Select to copy/paste. Just copy range to range.
I threw in a function to check the spelling of the worksheet, because that can certainly trip you up when trying to debug.

Option Explicit

Sub CopyCitiesToSheets()
    Dim srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Source Sheet")
    
    Dim cities As Range
    Set cities = srcWS.Range("CITIES")
    
    '--- does not assume that you have the same number of rows
    Dim destSheetNames As Range
    With srcWS
        Dim lastRow As Long
        lastRow = .Range("CITIES").Offset(0, 1).Cells(.Rows.Count, 1).End(xlUp).Row
        Set destSheetNames = srcWS.Range("CITIES").Offset(0, 1).Resize(lastRow, 1)
    End With
    
    Dim destSheetName As Variant
    For Each destSheetName In destSheetNames
        '--- establish where the cities are going, but sure to match the same
        '    size and shape of the source range
        Dim destination As Range
        Set destination = GetDestinationRange(destSheetName, cities.Rows.Count, cities.Columns.Count)
        destination.Value = cities.Value
    Next destSheetName
    
End Sub

Function GetDestinationRange(ByVal destSheetName As String, _
                             ByVal numRows As Long, _
                             ByVal numCols As Long) As Range
    Dim dstWS As Worksheet
    On Error Resume Next
    Set dstWS = ThisWorkbook.Sheets(destSheetName)
    If Err <> 0 Then
        MsgBox "ERROR: not a value worksheet name!  Did you misspell it?", _
               Buttons:=vbCritical + vbOKOnly, _
               Title:="ERROR in Worksheet Name"
    End If
    Set GetDestinationRange = dstWS.Range("C1").Resize(numRows, numCols)
End Function