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
Comments
Post a Comment