遍历下拉菜单并复制粘贴 [英] Loop through dropdown and copy-paste

问题描述

我正在尝试遍历下拉列表中的所有值，其下拉列表的来源位于"Comm O& S"工作表中，范围为A31:L31.

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.

我想从另一个工作表中复制由于下拉菜单中的选择而产生的值，并将这些值粘贴到单独工作表中的列中(从C列开始).然后，我想在下拉列表中选择下一个值，然后将下一列中的值复制粘贴到上面，等等.

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

推荐答案

尝试一下:

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

这篇关于遍历下拉菜单并复制粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！