excel - VBA copying from various cells from various worksheets to one worksheet -

excel - VBA copying from various cells from various worksheets to one worksheet -

i trying re-create values few worksheets, different cells in these worksheets. wondering if there way simplify code instead of normal re-create , paste. code have been using , rather long.

i thinking if there function allows me tell vba re-create , paste. define matching copy-paste cells. e.g. b2 - d3 b4 - d4 etc

worksheets(stockcode & "_is_" & marketcode).range("b2").copy worksheets(stockcode & "_stock ratio_" & marketcode).range("d3").pastespecial worksheets(stockcode & "_is_" & marketcode).range("b4").copy worksheets(stockcode & "_stock ratio_" & marketcode).range("d4").pastespecial worksheets(stockcode & "_is_" & marketcode).range("b15").copy worksheets(stockcode & "_stock ratio_" & marketcode).range("d5").pastespecial

this might help.

dim ws worksheet set ws = worksheets(stockcode & "_stock ratio_" & marketcode) worksheets(stockcode & "_is_" & marketcode) .range("b2").copy destination:=ws.range("d3") .range("b4").copy destination:=ws.range("d4") .range("b15").copy destination:=ws.range("d5") end set ws = nil

if value need carry across alternative method may sufficient.

dim ws worksheet set ws = worksheets(stockcode & "_is_" & marketcode) worksheets(stockcode & "_stock ratio_" & marketcode) .range("d3") = ws.range("b2").value .range("d4") = ws.range("b4").value .range("d5") = ws.range("b15").value end set ws = nil

the first uses with ... end with reference is worksheet. sec uses same method reference stock ratio worksheet. when within with ... end with prefix references period (aka full stop) gain parent relationship.

it worthwhile utilize with ... end with reference 1 worksheet , assign sec variable. edited above note sec method.

excel vba excel-vba