Hello. I am trying to figure out the VBA to select specific values in a drop down list already created in my spreadsheet.

What I am doing overall: I have a drop down list of six cities (in C5) that triggers the re-calculation of cell D18 (which is sales for that particular city). I need a macro that selects the cities one at a time, then copies the sales value for each one in D18, then pastes the values in a different sheet that I named "Datos".

I recorded a macro manually and got the following VBA, but it does not differentiate when I selected something different in the drop down list. Please help!!



Sub CopyAllRegionsUnidadesActual()
'
' CopyAllRegionsUnidadesActual Macro
'

'
Range("D18").Select
Selection.Copy
Sheets("Datos").Select
Range("E46").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Herramienta").Select
Range("D18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Datos").Select
Range("E47").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Herramienta").Select
Range("D18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Datos").Select
Range("E48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Herramienta").Select
Range("D18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Datos").Select
Range("E49").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Herramienta").Select
Range("D18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Datos").Select
Range("E50").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Herramienta").Select
Range("D18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Datos").Select
Range("E51").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub