excel - Create new worksheet based on text in coloured cells, and copy data into new worksheet -


i have large data set need manipulate , create individual worksheets. within column b cells coloured green make new worksheet for. please see screen shot.

have characters somewhere...

for example create worksheets titled "shopping" & "retail". once worksheet created, copy data between "worksheet title" (green cells) columns ("b:c") & ("ai:bh") please see screen shot below expected output;

enter image description here

the code have far below can see not complete not know how go extracting data between "green cells". 

sub wrksheetadd()  dim r range dim long dim lr long worksheets("ring phased").select  lr = range("b65536").end(xlup).row set r = range("b12:b" & (lr))  = r.rows.count 1 step -1     r.cells(i, 1)         if .displayformat.interior.colorindex = 35         msgbox         worksheets.add(after:=worksheets(worksheets.count)).name = cells (i,1).value         worksheets("ring phased").select         end if     end next  end sub

any around appreciated.

sorry taking while this, i've been busy last few days, haven't had time on stackoverflow.

anyway, way i'd go store found values in array, , loop through array in order find distance between them.

the following code works me, using simplified data, think principle sound:

option explicit option base 0  sub wrksheetadd()    dim r range, c range   dim long: = 0   dim cells_with_color() range: redim cells_with_color(1)    worksheets("ring phased")     ' since doesn't seem first cell want copy colored, hardcode location here.     ' saves having test if array empty later.     set cells_with_color(i) = .range("b12")     = + 1     set r = range(.range("b13"), .range("b" & .cells.rows.count).end(xlup))      ' put cells color in defined range array     each c in r       if c.displayformat.interior.colorindex = 35         if > ubound(cells_with_color)           redim preserve cells_with_color(ubound(cells_with_color) + 1)         end if         set cells_with_color(i) = c         = + 1       end if     next      ' loop through array, , copy previous range value current 1 new worksheet     ' reset counter first, start @ 1, since first range-value (0 in array) start of started checking     ' (hmm, reusing variables may bad practice >_>)     = 1     while <= ubound(cells_with_color)       worksheets.add(after:=worksheets(worksheets.count)).name = cells_with_color(i).value       ' set range copy - in copy-statement, makes easier read       set r = .rows(cstr(cells_with_color(i - 1).row) + 1 & ":" & cstr(cells_with_color(i).row))       ' change destination whereever want on new sheet. think has in column one, though, since copy entire rows.       ' if want refine bit, change whatever set r in previous statement.       r.copy destination:=worksheets(cstr(cells_with_color(i).value)).range("a1")       = + 1     wend   end end sub

it lacks error-checking ought in there, i'll leave exercise figure out. believe functional. luck!


Comments

Post a Comment

Popular posts from this blog

javascript - Google App Script ContentService downloadAsFile not working -

i have web app developed using google app script htmlservice , html form, populating excel sheet in google drive using spreadsheetapp . , 1 section calling contentservice download data excel file. function doget(e) { // read excel sheet //getappfile(); // render application html template return htmlservice.createtemplatefromfile('index').evaluate() .settitle('go smart') .setsandboxmode(htmlservice.sandboxmode.iframe); } function downloaddoublequatecsvfile() { var sheetid = propertiesservice.getscriptproperties().getproperty('sheetid'); var ss = spreadsheetapp.openbyid(sheetid).getactivesheet(); //var ss = spreadsheetapp.getactivespreadsheet().getactivesheet(); var maxcolumn = ss.getlastcolumn(); var maxrow = ss.getlastrow(); var data = ss.getrange(1, 1, maxrow, maxcolumn).getvalues(); if (data.length > 1) { var csv = ""; (var row = 0; row < data.length; row++) { (var c...
Read more

javascript - Function overwritting -

is there way overwrite function or event handling in function? using addeventlistener, can have many event handlers on element want var test=document.getelementbyid("div"); test.addeventlistener("click",function(){alert("im first");},false); test.addeventlistener("click",function(){alert("im second");},false); // alerts "im first" , after "im second" but if want overwrite function/event example based on client width example this function one() { alert("first"); } function two() { alert("second"); } window.onresize = function() { var test = document.getelementbyid("div"); if (window.innerwidth > 500) { test.onclick = 1 } else { test.onclick = two; } } is possible in javascript? in case use effective little known approach. addeventlistener can accept object property handleevent event handler. in case it's easy overwr...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more