Salve os dois arquivos na mesma pasta(qualquer), inclusive, no caso do arquivo CIDADES, habilitado para Macros (xlsm).
Insira um Módulo e cole o código abaixo.

Faça um teste com arquivos de backup e reporte erros ou inconsistências.

Sub Importar()

Dim pasta As String
Dim i As Integer
Dim f As Integer

pasta = ThisWorkbook.Path
i = 1
f = Sheets("LISTA").Range("A65536").End(xlUp).Row

Workbooks.Open Filename:=pasta & "BASE.xlsx"

Do While i <= f
Windows("CIDADES.xlsm").Activate
guia = Sheets("LISTA").Cells(i, 1).Value
Windows("BASE.xlsx").Activate
Sheets(guia).Copy After:=Workbooks("CIDADES.xlsm").Sheets(i)
Windows("BASE.xlsx").Activate
i = i + 1
Loop
Windows("BASE.xlsx").Activate
ActiveWindow.Close

End Sub

Edson, muito obrigado pela rápida resposta.
Seu código atingiu a maior parte do meu objetivo, mas se eu digitar, por exemplo, RECIFE na célula A1 e NATAL na célula A3 (pulei uma linha) a macro dá um erro (Erro em tempo de execução '9': subscrito fora do intervalo).
Além disso, baseado neste código, o nome do arquivo CIDADES.xlsm terá que ficar fixo. Gostaria que o código funcionasse com qualquer nome que eu atribua a este arquivo sem ter que alterar no código.
O meu arquivo BASE.xlsx terá sempre esse nome, mas o arquivo CIDADES.xlsm variará.

Obrigado novamente.

Testa assim agora, Daniel.

Sub Importar()

Dim pasta As String
Dim i As Integer
Dim f As Integer
Dim j As String
Dim n As Long

j = ThisWorkbook.Name
pasta = ThisWorkbook.Path
i = 1
f = Sheets("LISTA").Range("A65536").End(xlUp).Row

Workbooks.Open Filename:=pasta & "BASE.xlsx"

Do While i <= f
Windows(j).Activate
guia = Sheets("LISTA").Cells(i, 1).Value
n = Sheets.Count
If guia = "" Then
i = i + 1
Else
Windows("BASE.xlsx").Activate
Sheets(guia).Copy After:=Workbooks(j).Sheets(n)
Windows("BASE.xlsx").Activate
i = i + 1
End If
Loop
Windows("BASE.xlsx").Activate
ActiveWindow.Close

End Sub

Muitíssimo obrigado Edson. Era isso mesmo que eu queria.

Valeu!