i'd make summary sheet that, if changed, changes source sheets pulling from. code have far aggregates of sheets on summary sheet on summary sheet's activation event. trying have of other sheets updated on deactivation event not seem working. here code working with:
private sub worksheet_deactivate() application.screenupdating = false dim tabs variant tabs = array("beld", "rmld", "pascoag", "devens", "wbmlp", "rowely", "amp", "first energy", "dynegy", "apn", "misc") j = 1 ubound(tabs) sheets(tabs(j)).select dim rng1 range dim stri string = 3 activesheet.usedrange.cells(activesheet.usedrange.cells.count).row stri = activesheet.cells(i, "a") set rng1 = worksheets("summary").range("a:a").find(stri, , xlvalues, xlwhole) if not rng1 nothing sheets("summary").range(rng1.address).entirerow.copy activesheet.range("a" & i).entirerow.select selection.insert shift:=xlleft activesheet.range("a" & + 1).entirerow.select selection.delete shift:=xlup else msgbox strsearch & " not found" end if next activesheet.range("a" & 1).select next application.screenupdating = true end sub i new vba , first post on stackoverflow if missed let me know.
when assign variant array in manner, end zero-based array. need start @ j = 0. own code is, never access beld worksheet.
dim tabs variant tabs = array("beld", "rmld", "pascoag", "devens", "wbmlp", "rowely", "amp", "first energy", "dynegy", "apn", "misc") j = 0 ubound(tabs) .... a more universal method using for j = lbound(tabs) ubound(tabs) not matter whether array 1 or 0 based let each array describe own properties through lbound function , ubound function.
a more comprehensive rewrite of routine include getting rid of .select , .activate methods , use direct worksheet , cell referencing in place.
private sub worksheet_deactivate() dim rng1 range dim stri string, lr long, j long, long dim tabs variant on error goto bm_safe_exit application.screenupdating = false application.enableevents = false tabs = array("beld", "rmld", "pascoag", "devens", "wbmlp", "rowely", _ "amp", "first energy", "dynegy", "apn", "misc") j = lbound(tabs) ubound(tabs) sheets(tabs(j)) lr = .cells.find(chr(42), after:=.cells(1, 1), searchdirection:=xlprevious).row = 3 lr stri = .cells(i, "a").value if cbool(len(stri)) on error resume next me.range("a:a") set rng1 = .find(what:=stri, after:=.cells(.rows.count), lookin:=xlvalues, lookat:=xlwhole) end on error goto bm_safe_exit if not rng1 nothing 'clearing copy/paste may better inserting, pasting , deleting old row .rows(i).clear rng1.entirerow.copy _ destination:=.range("a" & i) else 'maybe copy data sheet summary sheet if occurs msgbox stri & " on " & .name & " not found on summary" end if end if next end next bm_safe_exit: application.screenupdating = true application.enableevents = true end sub since in summary worksheet's code sheets, use of me can applied summary worksheet object. once have set rng1 range returned find, no longer necessary describe worksheet comes range .parent property carried it.
see how avoid using select in excel vba macros more methods on getting away relying on select , activate accomplish goals.
Comments
Post a Comment