Loop through dropdown and copy-paste

Question

I'm trying to loop through all values in a dropdown list, the source for which is in sheet "Comm O & S", range A31:L31.

I want to copy values from another sheet that result from the selection in the dropdown and paste these values in a column in a separate sheet (starting at column C). Then, I want to select the next value in the dropdown and copy-paste the values in the next column over, etc.

I am having trouble with the copy-paste within the dropdown loop.

Sheets("Scenario by Payer").Activate
For Each rngCell In wb.Worksheets("Comm O & S").Range("A31:L31")
    ' Set the value of dd_comm
    ws.Range("D14").Value = rngCell.Value

    Sheets("Detailed Outputs").Select
    Range("T52:t60").Select
    Application.CutCopyMode = False
    Selection.Copy

    Sheets("Comm O & S").Activate

    For Each c In ActiveSheet.Range("C7:L7").Cells
        c.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False

    Next rngCell
Next c

Read this, and change your code accordingly. That said, it's not clear what you're asking. Are you running into any error? What's happening? What's the specific problem? — Mathieu Guindon
The problem is you are pasting over your results on each iteration. I'm guessing you want the 1st iteration's results in C7, 2nd in D7, 3rd in E7, etc. Is that correct? — tigeravatar
Yes, tigeravatar - I'm trying to paste the results in C7, then do the next dropdown item, paste those results in D7, etc. The problem is that the same results get pasted in each of the columns; that is, it's not iterating through the dropdown list. Thank you for your response and help! — justtrying

tigeravatar tigeravatar · Accepted Answer · 2017-10-04T13:30:40

Give this a try:

Sub tgr()

    Dim wb As Workbook
    Dim wsScen As Worksheet
    Dim wsComm As Worksheet
    Dim wsOuts As Worksheet
    Dim rDDList As Range
    Dim rDDCell As Range
    Dim rDDValue As Range
    Dim rCopy As Range
    Dim rDest As Range

    Set wb = ActiveWorkbook
    Set wsScen = wb.Sheets("Scenario")
    Set wsComm = wb.Sheets("Comm O & S")
    Set wsOuts = wb.Sheets("Detailed Outputs")

    Set rDDList = wsComm.Range("A31:L31")
    Set rDDValue = wsScen.Range("D14")
    Set rCopy = wsOuts.Range("T52:T60")
    Set rDest = wsComm.Range("C7")

    For Each rDDCell In rDDList.Cells
        rDDValue.Value = rDDCell.Value
        rDest.Resize(rCopy.Rows.Count, rCopy.Columns.Count).Value = rCopy.Value
        Set rDest = rDest.Offset(, rCopy.Columns.Count)
    Next rDDCell

End Sub

Loop through dropdown and copy-paste

1 Answers