i'm pretty new vba , need project. need write macro reads sheet name in column c, , pastes values source workbook range in target workbook, specified in column d.
so example, needs copy data in sheet2 of myworkbook book, , paste range of theirworkbook sheet2. place range , sheet number information stored in separate workbook.
edit: i've added picture of wbopen looks like. this here.
option explicit sub pastetotargetrange() dim arrvar variant 'stores sheets copied dim arrvartarget variant 'stores names of sheets in target workbook dim rngrange range 'each sheet name in given range dim rngloop range 'range rngrange based in dim wssource worksheet 'source worksheet ranges found dim wbsource workbook 'workbook information paste dim wbtarget workbook 'workbook receive information dim strsourcefile string 'location of source workbook dim strtargetfile string 'location of source workbook dim wbopen workbook 'current open workbook(one inputs) dim wsrange range 'get information source workbook dim varrange range 'range values should pasted dim integer 'counter for loop dim wbknewsheet worksheet 'create new worksheet if target workbook doesn't have dim wstarget worksheet 'target workbook worksheet dim varnumber string 'range post set wbopen = workbooks.open("workbookwithranges.xlsx") 'open source file msgbox ("open source file") strsourcefile = application.getopenfilename if strsourcefile = "" exit sub set wbsource = workbooks.open(strsourcefile) 'open target file msgbox ("open target file") strtargetfile = application.getopenfilename if strtargetfile = "" exit sub set wbtarget = workbooks.open(strtargetfile) 'activate transfer workbook wbopen.activate set wsrange = activesheet.range("c9:c20") set arrvartarget = wbtarget.worksheets each varrange in wsrange if varrange.value = 'target workbook worksheets varnumber = varrange.offset(0, -1).value set wstarget = x.offset(0, 1) wssouce.range(wstarget).value = varnumber else wbknewsheet = worksheets.add wbknewsheet.name = varrange.value end if next end sub
something (untested should give idea)
sub pastetotargetrange() '....omitted set wsrange = wbopen.sheets(1).range("c9:c20") each c in wsrange shtname = c.offset(0, -1).value set wstarget = getsheet(wbtarget, shtname) 'get target sheet wbsource.sheets(shtname).range(c.value).copy wstarget.range(c.value) next end sub 'get reference named sheet in specific workbook ' default create sheet if not found function getsheet(wb workbook, ws string, optional createifmissing boolean = true) dim rv worksheet on error resume next 'ignore eroror if no match set rv = wb.worksheets(ws) on error goto 0 'stop ignoring errors 'sheet wasn't found, , should create if missing if rv nothing , createifmissing set rv = wb.worksheets.add(after:=wb.worksheets(wb.worksheets.count)) rv.name = ws end if set getsheet = rv end function 
Comments
Post a Comment