excel - Worksheet as a variable? -

excel - Worksheet as a variable? -

i have created glorified re-create , paste macro copies imported info "template" worksheet worksheets of specific locations. (so have worksheets named location1 location2 location3 location4 , template)

currently, code looks this: (but lot longer , more repetitive)

sheets("template").select range("p24:q30").select selection.copy sheets("location1").select range("p24").select activesheet.paste

i want "location1" worksheet variable.

i need way changed without having alter code every different template. far can see there 2 solutions.

there cell in template holds value of name of sheet info supposed pasted into. if set equal worksheet variable can write instead of "location1" have user take location worksheet list upon running macro.

i don't know how either of these , don't know better.

first, removing select thing:

sheets("template").range("p24:q30").copy sheets("location1").range("p24")

if tab name in cell `b1' of sheet template, can use:

dim stabname string stabname=sheets("template").[b1] sheets("template").range("p24:q30").copy sheets(stabname).range("p24")

you can create real variable worksheet, e.g.

dim shtdest worksheet set shtdest =sheets(sheets("template").[b1]) sheets("template").range("p24:q30").copy shtdest.range("p24")

excel vba excel-vba