vba - How to transfer highlighted cells in Excel 2007 from one table to another in the same sheet? -

-

i create code transfer content of highlighted cells 1 table in same sheet content, use button copy content, create macro transfer content dynamically clicking on button, when user change content of highlighted cells of first table content changes automatically in second table or clicking on button again.

i use code highlight cells 

' set of highlighted cells indexed row number dim highlightedcells new collection  ' scan existing sheet cells coloured 'red' , initialise '  run-time collection of 'highlighted' cells. private sub worksheet_activate()    activesheet.unprotect password:="p@ssw0rd"     dim existinghighlights range     ' reset collection of highlighted cells ready rebuild     set highlightedcells = new collection     ' find first cell has background coloured red     application.findformat.interior.colorindex = 3     set existinghighlights = activesheet.cells.find("", _                                                     lookin:=xlvalues, _                                                     lookat:=xlpart, _                                                     searchorder:=xlbyrows, _                                                     searchdirection:=xlnext, _                                                     matchcase:=false, _                                                     searchformat:=true)     ' process long have more matches     while not existinghighlights nothing         crow = existinghighlights.row         ' add reference first coloured cell if multiple         ' exist in single row (will occur if background manually set)         err.clear         on error resume next             call highlightedcells.add(existinghighlights.address, cstr(crow))         on error goto 0         ' search cell after last match. note error in excel         '  appears prevent findnext method finding formats correctly         application.findformat.interior.colorindex = 3         set existinghighlights = activesheet.cells.find("", _                                                     after:=existinghighlights, _                                                     lookin:=xlvalues, _                                                     lookat:=xlpart, _                                                     searchorder:=xlbyrows, _                                                     searchdirection:=xlnext, _                                                     matchcase:=false, _                                                     searchformat:=true)         ' abort search if we've looped top of sheet         if (existinghighlights.row < crow)             exit         end if     loop   activesheet.protect password:="p@ssw0rd" end sub  private sub worksheet_beforedoubleclick(byval target range, cancel boolean)     activesheet.unprotect password:="p@ssw0rd"     dim hcell string     dim cellalreadyhighlighted boolean     hcell = ""      err.clear     on error resume next       hcell = highlightedcells.item(cstr(target.row))     on error goto 0      if (hcell <> "")         activesheet.range(hcell).interior.colorindex = 2         if (hcell = target.address)             call highlightedcells.remove(cstr(target.row))             target.interior.colorindex = 2         else             call highlightedcells.remove(cstr(target.row))             call highlightedcells.add(target.address, cstr(target.row))             target.interior.colorindex = 3         end if     else         err.clear         on error resume next           highlightedcells.remove (cstr(target.row))         on error goto 0         call highlightedcells.add(target.address, cstr(target.row))         target.interior.colorindex = 3     end if     cancel = true       activesheet.protect password:="p@ssw0rd" end sub

and use code copy highlighted cells:

sub copycat()   activesheet.unprotect password:="p@ssw0rd" dim lr long, long, j long dim c range j = 1 lr = range("a" & rows.count).end(xlup).row each c in worksheets("mb").range("a1:o" & lr)       if c.interior.colorindex = 3             c.copy destination:=worksheets("mb").range("j" & j)         j = j + 1         end if next c   activesheet.protect password:="p@ssw0rd"  end sub

please !!!!

instead of copying whole table , using values populate table on second page, why not (for items want update, sheet1 gets updates) leave "link" original table. either set literally cell refers to, or more robustly, use index/match. see below:

this example of sheet1 (the data want copied onto second sheet). have highlighted "salary" column, reflect user asked change these.

enter image description here

and in sheet 2, can use various ways of "linking" first sheet:

enter image description here

that way, when go in edit salary chris or john, it'll update salary in second sheet, without needing run macros. you're looking do, or overlooking/misunderstanding something?


Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more