VBA to select specific Text in Drop Down List

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

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.
http://www.excelforum.com/excel-prog...down-list.html
Thread Closed.