Excel Macro to select the each values from drop down list and copy-paste the values

Question

I have excel sheet which has lot of calculation based on the values available in drop-down list in 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"

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"

user3588043 user3588043 · Accepted Answer · 2014-05-14T06:04:01

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

Excel Macro to select the each values from drop down list and copy-paste the values

1 Answers