Pase Special while recording a Macro

**ktnal** · 05-16-2011, 10:37 PM

In one sheet a I have a drop down box that has options {a,b,c}. Depending on which I pick There is another sheet that does calculations based on these options. I want to create a macro that will select each option and then past the results in a new table. I have tried recording a macro selecting each option and then using paste special - values for the new table. when I run the macro each value in the table corresponds to option c. How can I make the values for a and b to be the correct value for their individual calculation?

Thanks in advance for your help.

**abousetta** · 05-16-2011, 11:14 PM

Hi,

Could you post what you have so far and we can see if someone can help from there?

abousetta

**ktnal** · 05-17-2011, 09:44 AM

I have attached an example sheet for what I am trying to do. The top has a drop down menue with three options. Below it is the calculation that I want to perform based on the drop down selection. There is a macro button to run the macro to paste the value from each selection into the combined results table. When I run the macro it only past the results from the final selection into the table.

**Roger Govier** · 05-17-2011, 10:02 AM

Hi

The following is one way to do what you want.


Option Explicit

Sub results_macro()
'
' results_macro Macro
'
    Dim dest As Long
    dest = WorksheetFunction.Match(Range("B2"), Range("B13:B15"), 0)
    Range("B7").Copy
      
    Range("C12").Offset(dest, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

**ktnal** · 05-17-2011, 12:05 PM

Thanks for the help. That code helps for part of the issue, but I want the macro button to select each option from the drop down menue and then input the results in the combined results chart. I want to see the results for each option in the results chart while only selecting the macro button once.

thanks in advance for your help.

**Roger Govier** · 05-17-2011, 12:43 PM

Hi

Then use the following code

Sub results_macro()
    Dim dest As Long, count As Long, i As Long
    count = WorksheetFunction.CountA(Range("I:I"))
    For i = 2 To count
    Range("B2") = Cells(i, "I")
        
    dest = WorksheetFunction.Match(Range("B2"), Range("B13:B15"), 0)
    Range("B7").Copy
      
    Range("C12").Offset(dest, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Next i
    
End Sub