excel - VBA Calculate a range of cells after copying and pasting formulas -

i have working vba function copies formulas range of cells , requests user wants paste. once function has pasted specified row(s), have included .calculate function update formulas.
however, when .calculate runs, updates range user has inputted, , not entire row.
code below, assume a2 forename, b2 surname , c2:e2 formulas. when input box comes requesting paste, user select c3:e3 , calculate everything. if lazily click c3, , formulas pasted in, update c3.
how amend this?

    sub pastemacro()  ' ' pastemacro macro ' ' keyboard shortcut: ctrl+m '     on error resume next     set ret = application.inputbox(prompt:="please select range want paste", type:=8)     on error goto 0     if not ret nothing         selection.copy          range("c2:e2").copy destination:=ret          ret.calculate              application.cutcopymode = false     end if end sub 

-------------a ------- b ----------------- c -------------------- d ----------------------- e
1 ---------------------------------------- 56 -------------------- 66 --------------------- 76
2 ------andrew ----m ----------=sum(d1:e1) -------- =sum(e1:f1) -------- =sum(f1:g1)
3 -------- john ---- s
have following excel table. run macro , click on c3 instead of typing c3:e3.
macro paste formulas c2:e2 c3:e3. problem because 1 cell (c3) clicked upon, .calculate function update cell , not range c3:e3.

in order reproduce problem, must set calculation manual. cannot reproduce problem if calculation automatic.

assuming want recalculate destination cells match copied cells, suggest minor modification in code, ensure destination calculated. also, depending on goals, might protect sheet cells in column c can selected, or use selected row destination , hard code columns.

for example:

option explicit

sub pastemacro() dim ret range dim rangetocopy range  set rangetocopy = range("c2:f2") ' ' pastemacro macro ' ' keyboard shortcut: ctrl+m '     set ret = application.inputbox(prompt:="please select range want paste", type:=8)     if not ret nothing             rangetocopy.copy destination:=ret             ret.resize(1, rangetocopy.columns.count).calculate              application.cutcopymode = false     end if end sub 

or even:

option explicit sub pastemacro() dim ret range dim rangetocopy range  set rangetocopy = range("c2:f2") ' ' pastemacro macro ' ' keyboard shortcut: ctrl+m '     set ret = application.inputbox(prompt:="please select range want paste", type:=8)     if not ret nothing          rangetocopy.copy destination:=cells(ret.row, rangetocopy.column).resize(1, rangetocopy.columns.count)          ret.resize(1, rangetocopy.columns.count).calculate              application.cutcopymode = false     end if end sub