Copy data to another sheet based on selection from dropdown

Question

I am looking to copy/paste values to another sheet based on selection from a drop down list in Excel. When I select from drop down list, other cells in the sheet gets populated. I would like to copy/paste these populated values to another sheet in the same workbook. And I would like the copied/pasted value to remain the same when selection is changed.

Looking for a formula that can be applied to many cells. I tried an IF formula but the value changes when the selection is changed from drop down list.

I think you will need VBA for this. Formulas will keep recalculating each time you change the trigger. You want to define a range as the range to copy, set up a change event associated with the drop down, when the drop down changes, copy the copyrange to the next available row in another sheet. Try coding some of that and come back with any questions. — QHarr

fali fali · Accepted Answer · 2018-03-17T01:07:21

I was able to find this code on an other thread. I modified to work with my sheet. I need to figure out how to add headings for the data being printed to OutSheet.

Sub SpitValues()
 Dim dvCell As Range
 Dim inputRange As Range
 Dim c As Range
 Dim i As Long

'Cell that contains data validation list
'Cell where Dropdown list is
Set dvCell = Worksheets("InSheet").Range("C3")

'Determine where validation comes from
Set inputRange = Evaluate(dvCell.Validation.Formula1)

i = 1

'Begin loop
 Application.ScreenUpdating = False
 For Each c In inputRange
   dvCell = c.Value

   Worksheets("OutSheet").Cells(i, "A").Value = Worksheets("InSheet").Range("B10").Value
   Worksheets("OutSheet").Cells(i, "B").Value = Worksheets("InSheet").Range("G10").Value
   Worksheets("OutSheet").Cells(i, "C").Value = Worksheets("InSheet").Range("H10").Value

 i = i + 1

Next c
Application.ScreenUpdating = True

End Sub

Copy data to another sheet based on selection from dropdown

1 Answers