Select first item from a drop down menu list in VBA

[Peter Davison]

I have a macro running and before it leaves the sheet it is on, I want it to select a cell (E3) which has a drop down menu (Range Name "List)" and then select the first item in the list.
Can you advise what the code would look like?
Thank you

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Deactivate()
    Range("E3") = Range("List").Cells(1, 1)
End Sub

 

[Peter Davison]

Thank you that was very helpful.

 

[mumps]

You are very welcome.

 

[ATBoreale]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Deactivate()
    Range("E3") = Range("List").Cells(1, 1)
End Sub

Hello this was really helpful for me but I would need to go beyond that.
I would need to have similar code but were in the code the selection of the list items goes as a string to the next one.

I need to select the first time the first item from the list, copy, paste to a new sheet and save then come back to dropdown list and do the same with the next item of the list and this until the last item of the list.

So i'm assuming I need to create it so that the number in red below changes but don't know how to do that.
The number in red needs to increase +1 everytime from the previous one.

Range("C3") = Range("FabricCode").Cells(1, 1)

Would be great if you could help me too on that.

 

[mumps]

I need to select the first time the first item from the list, copy, paste to a new sheet and save then come back to dropdown list and do the same with the next item of the list and this until the last item of the list.

Do you want to go through all the items in the drop down list when you select the first item only or will you be selecting each item in the list, one at a time. What is the name of the sheet and into which cell in that sheet do you want to paste the value? What exactly do you want to save and where do you want to save it? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[kvsrinivasamurthy]

May be this

Private Sub Worksheet_Deactivate()
Dim T As Long
Application.EnableEvents = False
On Error Resume Next

For T = 1 To Range("List").Cells.Count
Sheets.Add After:=ActiveSheet
ActiveSheet.Range("C3") = Range("List").Cells(T, 1)
Next T
Application.EnableEvents = True

End Sub