title: vba function to copy a cell and pastespecial to the active cell where I write =macrofunction
body: <p>In making a vba function I want to simply copy one fixed cell and pastespecial its value to the highlighted cell(selected cell wehere I enter macro function) in the same worksheet. This works when I run the macro from within the editor but making the macro with a formula doesn't work for pasting the cell to the cell I am writing the function in. If I write in C2 =macropasteC1
  where my macro is called macropastec1
  I get #NAME and not the number in C1
  although when I run the macro with c2 selected in vba editor it did work although I lost the exact formula</p>
  
  <p>it was something like</p>
  
  <pre><code> 'Sub macropastec1()
  
     macropastec1 Macro
  
    Selection.Copy
    cell("C1").Select
   active.PasteSpecial Paste:=xlPasteValues**,Operation:=xlNone,SkipBlanks _
  :=False, Transpose:=False
  Application.CutCopyMode = False**
  End Sub`
  </code></pre>
  
  <p>I can't remember exactly how I had it like range.(C1).select 
  but it was similar to above. I started by recording it and then I did insert macro copied it and changed sub to function to get the macro function</p>
  
  <p>please help I really need this ability</p>
  
  <p>Another variation that worked in the editor for activating a cel and running from in the editor I think was</p>
  
  <pre><code> 'function macropastec1()
  
     macropastec1 Macro
  
    Selection.Copy
    range.("C1").Select
   activecell.PasteSpecial Paste:=xlPasteValues**,Operation:=xlNone,SkipBlanks _
  :=False, Transpose:=False
  Application.CutCopyMode = False**
  End function`
  </code></pre>
  
  <p>obvioulsy because you need a macro function to run it from within a cell with =function</p>
  
  <p>it is hard to replicate but I think the jist of what I am trying to achieve it clear</p>
  
  <p>sorry this doesnt really answer but it does clarify whats being asked</p>
  
  <p>the macro function in c1 if from a constantly changing cell is say c3
  so in c1 I have </p>
  
  <pre><code>Function Min_Cell2(cx34 As Range)
  'Function for defining the lowest number in a cell selected by the user
  
  If Minimum &lt;&gt; "" Then
  If Value_Range.Value &lt; Minimum Then Minimum = Value_Range.Value
  Else
  Minimum = Value_Range.Value
  End If
  
  Min_Cell = Minimum
  
  End Function
  </code></pre>
  
  <p>or the maximum equivalent. So I'd need it to paste special the value and not update it..</p>
  
  <p>I assume chancea's function is this </p>
  
  <pre><code>Function macropastec2()
  macropastec2 = Range("C1").Value
  End Function
  </code></pre>
  
  <p>gives
  upon entering in a cell =macropastec2(C1)</p>
  
  <pre><code>#VALUE
  
  Function macropastec2() As varient
  macropastec2 = Range("C1").Value
  End Function
  </code></pre>
  
  <p>gives </p>
  
  <pre><code>#NAME
  </code></pre>
  
  <p>:S
  If it helps I think the original function can be simplified to something like </p>
  
  <pre><code>'function macropastec1()
  
     macropastec1 Macro
  
    Selection.Copy
    range.("C1").Select
   activecell.PasteSpecial Paste:=xlPasteValues**,
  End function`
  </code></pre>