vba Excel宏从下拉列表中选择每个值并复制粘贴值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23645933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Macro to select the each values from drop down list and copy-paste the values
提问by Chito
I have excel sheet which has lot of calculation based on the values available in drop-down list in B1.
我有一个 excel 表，它根据 B1 中下拉列表中的可用值进行了大量计算。
So far what i have done is, 1. Created a drown down list in cell B1 in sheet "ACCM Configurator" based on the range from sheet "Analysis - Template" $A:$A (ignored blanks) 2. Based on the value selected the down list in cell B1, I have created many calculations using formula in places those in A6:J33 in sheet "ACCM Configurator"
到目前为止，我所做的是， 1. 根据工作表“分析 - 模板”$A:$A（忽略空白）的范围，在工作表“ACCM 配置器”中的单元格 B1 中创建了一个下拉列表 2. 基于值选择了单元格 B1 中的下拉列表，我在“ACCM Configurator”表中的 A6:J33 中使用公式创建了许多计算
What I want to achieve now is, 1. select each values in drop down list in B1 and copy the calculated values for each drop-down values from the range A6:J33 and paste in a new sheet called as "ACCM Append" 2. Ignore any blank rows 3. A5:J5 has headers, so expected this to be paste as a header in sheet "ACCM Append"
我现在想要实现的是， 1. 选择 B1 下拉列表中的每个值，然后复制 A6:J33 范围内每个下拉值的计算值，然后粘贴到名为“ACCM Append”的新工作表中 2.忽略任何空白行 3. A5:J5 有标题，因此希望将其作为标题粘贴到“ACCM Append”表中
回答by user3588043
if paste as new range
如果粘贴为新范围
Sub DropDown1_Change()
'A6:J33 range
rangeCopy = Sheets("ACCM Configurator").range(Cells(6, 1), Cells(33, 10)).value
lastR = Sheets("ACCM Append").range("a:f").Find(What:="*", After:=Sheets("ACCM Append").Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlFormulas).row
'paste after last used range
Sheets("ACCM Append").Cells(lastR + 1, "A").Resize(UBound(rangeCopy, 1), UBound(rangeCopy, 2)).value = rangeCopy
End sub
if paste in same range
如果粘贴在相同范围内
Sub DropDown1_Change()
'A6:J33 range
rangeCopy = Sheets("ACCM Configurator").range(Cells(6, 1), Cells(33, 10)).value
'paste after last used range
Sheets("ACCM Append").Cells(2, "A").Resize(UBound(rangeCopy, 1), UBound(rangeCopy, 2)).value = rangeCopy
End sub
Note what on Sheets("ACCM Append") in first row must be headers values
注意第一行的 Sheets("ACCM Append") 必须是标题值
