VBA excel, alot of flickering and a bit slow, help me optimize -
i have completed intended worksheet accomplish want do. however, code seems heavy , computer screen flickers epileptic seizure. hoping maybe there can done, need in acheiving this.
the "system" consists of 2 files, userfile (the 1 flickers) , database file.
when run either full update or "new item only" update, seems require extensive resources, didnt think necessary considering simple task , number of potential lookups. done sheet "sagsnr." in "stackoverflow_dummy.xlsm" file.
i have written code beneath, complete, sanitized, files available here: https://spaces.hightail.com/space/vskxs.
i hope guys can me optimise this.
sub worksheet_updateallitemcostdata() dim material variant dim fndentry range dim wb1 workbook, wb2 workbook dim lr long, long, j long const spos string = "pos. " application.screenupdating = false application.displayalerts = false set wb1 = activeworkbook j = 0 lr = wb1.sheets("sagsnr.").cells(rows.count, "c").end(xlup).row if lr < 21 exit sub end if workbooks.open filename:="g:\backoffice\tilbudsteam\kostdatabase\matcost.xls", readonly:=true wb1.sheets("sagsnr.").rows("1:1").hidden = false set wb2 = activeworkbook = 21 lr wb1.sheets("sagsnr.").rows("1:1").copy wb1.sheets("sagsnr.").rows(i).pastespecial paste:=xlpasteformulas, operation:=xlnone, skipblanks:=true, transpose:=false wb1.sheets("sagsnr.").rows(i).pastespecial paste:=xlpasteformats, operation:=xlnone, skipblanks:=false, transpose:=false material = wb1.sheets("sagsnr.").range("c" & i).value set fndentry = wb2.sheets("matcost").range("d:d").find(what:=material) if not material = "" j = j + 1 wb1.sheets("sagsnr.").range("a" & i).value = spos & j end if if not fndentry nothing 'if want include formatting source file, use below: 'wb2.sheets("source sheet - change me").range("source column - change me" & fndentry.row).copy destination:=wb1.sheets("destination sheet - change me").range("destination column - change me" & i) 'if want keep formatting of target file, use below: 'wb1.sheets("source sheet - change me").range("source column - change me" & i).value = wb2.sheets("destination sheet").range("destination column" & fndentry.row).value wb1.sheets("sagsnr.").range("b" & i).value = wb2.sheets("matcost").range("h" & fndentry.row).value 'product group wb1.sheets("sagsnr.").range("e" & i).value = wb2.sheets("matcost").range("q" & fndentry.row).value 'available stock wb1.sheets("sagsnr.").range("f" & i).value = wb2.sheets("matcost").range("e" & fndentry.row).value 'materiale name wb1.sheets("sagsnr.").range("h" & i).value = wb2.sheets("matcost").range("aj" & fndentry.row).value 'marked deletion wb1.sheets("sagsnr.").range("i" & i).value = wb2.sheets("matcost").range("m" & fndentry.row).value 'datasheet wb1.sheets("sagsnr.").range("k" & i).value = wb2.sheets("matcost").range("p" & fndentry.row).value 'lotsize wb1.sheets("sagsnr.").range("m" & i).value = wb2.sheets("matcost").range("f" & fndentry.row).value 'material type (fert/hawa) wb1.sheets("sagsnr.").range("n" & i).value = wb2.sheets("matcost").range("n" & fndentry.row).value 'date of cost update wb1.sheets("sagsnr.").range("o" & i).value = wb2.sheets("matcost").range("o" & fndentry.row).value 'last change of cost data wb1.sheets("sagsnr.").range("p" & i).value = wb2.sheets("matcost").range("k" & fndentry.row).value 'stock category wb1.sheets("sagsnr.").range("q" & i).value = wb2.sheets("matcost").range("l" & fndentry.row).value 'abc code wb1.sheets("sagsnr.").range("r" & i).value = wb2.sheets("matcost").range("v" & fndentry.row).value 'construction weight cu wb1.sheets("sagsnr.").range("s" & i).value = wb2.sheets("matcost").range("w" & fndentry.row).value 'construction weight al wb1.sheets("sagsnr.").range("t" & i).value = wb2.sheets("matcost").range("x" & fndentry.row).value 'sales weight cu wb1.sheets("sagsnr.").range("u" & i).value = wb2.sheets("matcost").range("y" & fndentry.row).value 'sales weight al wb1.sheets("sagsnr.").range("ac" & i).value = wb2.sheets("matcost").range("z" & fndentry.row).value 'construction weight pe wb1.sheets("sagsnr.").range("ad" & i).value = wb2.sheets("matcost").range("ad" & fndentry.row).value 'pe costs wb1.sheets("sagsnr.").range("ae" & i).value = wb2.sheets("matcost").range("aa" & fndentry.row).value 'construction weight pvc wb1.sheets("sagsnr.").range("af" & i).value = wb2.sheets("matcost").range("ae" & fndentry.row).value 'pvc costs wb1.sheets("sagsnr.").range("ag" & i).value = wb2.sheets("matcost").range("af" & fndentry.row).value 'other materials costs wb1.sheets("sagsnr.").range("ah" & i).value = wb2.sheets("matcost").range("ab" & fndentry.row).value 'variable production costs wb1.sheets("sagsnr.").range("ai" & i).value = wb2.sheets("matcost").range("ac" & fndentry.row).value 'fixed production costs end if set fndentry = wb2.sheets("matcost").range("c:c").find(what:=material) if not fndentry nothing 'if want include formatting source file, use below: 'wb2.sheets("source sheet - change me").range("source column - change me" & fndentry.row).copy destination:=wb1.sheets("destination sheet - change me").range("destination column - change me" & i) 'if want keep formatting of target file, use below: 'wb1.sheets("source sheet - change me").range("source column - change me" & i).value = wb2.sheets("destination sheet").range("destination column" & fndentry.row).value wb1.sheets("sagsnr.").range("b" & i).value = wb2.sheets("matcost").range("h" & fndentry.row).value 'product group wb1.sheets("sagsnr.").range("e" & i).value = wb2.sheets("matcost").range("q" & fndentry.row).value 'available stock wb1.sheets("sagsnr.").range("f" & i).value = wb2.sheets("matcost").range("e" & fndentry.row).value 'materiale name wb1.sheets("sagsnr.").range("h" & i).value = wb2.sheets("matcost").range("aj" & fndentry.row).value 'marked deletion wb1.sheets("sagsnr.").range("i" & i).value = wb2.sheets("matcost").range("m" & fndentry.row).value 'datasheet wb1.sheets("sagsnr.").range("k" & i).value = wb2.sheets("matcost").range("p" & fndentry.row).value 'lotsize wb1.sheets("sagsnr.").range("m" & i).value = wb2.sheets("matcost").range("f" & fndentry.row).value 'material type (fert/hawa) wb1.sheets("sagsnr.").range("n" & i).value = wb2.sheets("matcost").range("n" & fndentry.row).value 'date of cost update wb1.sheets("sagsnr.").range("o" & i).value = wb2.sheets("matcost").range("o" & fndentry.row).value 'last change of cost data wb1.sheets("sagsnr.").range("p" & i).value = wb2.sheets("matcost").range("k" & fndentry.row).value 'stock category wb1.sheets("sagsnr.").range("q" & i).value = wb2.sheets("matcost").range("l" & fndentry.row).value 'abc code wb1.sheets("sagsnr.").range("r" & i).value = wb2.sheets("matcost").range("v" & fndentry.row).value 'construction weight cu wb1.sheets("sagsnr.").range("s" & i).value = wb2.sheets("matcost").range("w" & fndentry.row).value 'construction weight al wb1.sheets("sagsnr.").range("t" & i).value = wb2.sheets("matcost").range("x" & fndentry.row).value 'sales weight cu wb1.sheets("sagsnr.").range("u" & i).value = wb2.sheets("matcost").range("y" & fndentry.row).value 'sales weight al wb1.sheets("sagsnr.").range("ac" & i).value = wb2.sheets("matcost").range("z" & fndentry.row).value 'construction weight pe wb1.sheets("sagsnr.").range("ad" & i).value = wb2.sheets("matcost").range("ad" & fndentry.row).value 'pe costs wb1.sheets("sagsnr.").range("ae" & i).value = wb2.sheets("matcost").range("aa" & fndentry.row).value 'construction weight pvc wb1.sheets("sagsnr.").range("af" & i).value = wb2.sheets("matcost").range("ae" & fndentry.row).value 'pvc costs wb1.sheets("sagsnr.").range("ag" & i).value = wb2.sheets("matcost").range("af" & fndentry.row).value 'other materials costs wb1.sheets("sagsnr.").range("ah" & i).value = wb2.sheets("matcost").range("ab" & fndentry.row).value 'variable production costs wb1.sheets("sagsnr.").range("ai" & i).value = wb2.sheets("matcost").range("ac" & fndentry.row).value 'fixed production costs end if next wb2.close wb1.sheets("sagsnr.").rows("1:1").hidden = true application.displayalerts = true application.screenupdating = true end sub sub worksheet_getnewitemcostdata() dim material string dim costingdate variant dim fndentry range, fndcostdate range dim wb1 workbook, wb2 workbook dim lr long, long, j long const spos string = "pos. " application.screenupdating = false application.displayalerts = false set wb1 = activeworkbook j = 0 lr = wb1.sheets("sagsnr.").cells(rows.count, "c").end(xlup).row if lr < 21 exit sub end if workbooks.open filename:="g:\backoffice\tilbudsteam\kostdatabase\matcost.xls", readonly:=true wb1.sheets("sagsnr.").rows("1:1").hidden = false set wb2 = activeworkbook = 21 lr wb1.sheets("sagsnr.").rows("1:1").copy wb1.sheets("sagsnr.").rows(i).pastespecial paste:=xlpasteformulas, operation:=xlnone, skipblanks:=true, transpose:=false wb1.sheets("sagsnr.").rows(i).pastespecial paste:=xlpasteformats, operation:=xlnone, skipblanks:=false, transpose:=false material = wb1.sheets("sagsnr.").range("c" & i).value costingdate = wb1.sheets("sagsnr.").range("n" & i).value if not material = "" j = j + 1 wb1.sheets("sagsnr.").range("a" & i).value = spos & j end if if not costingdate <> "" set fndentry = wb2.sheets("matcost").range("d:d").find(what:=material) if not fndentry nothing 'if want include formatting source file, use below: 'wb2.sheets("source sheet - change me").range("source column - change me" & fndentry.row).copy destination:=wb1.sheets("destination sheet - change me").range("destination column - change me" & i) 'if want keep formatting of target file, use below: 'wb1.sheets("source sheet - change me").range("source column - change me" & i).value = wb2.sheets("destination sheet").range("destination column" & fndentry.row).value wb1.sheets("sagsnr.").range("b" & i).value = wb2.sheets("matcost").range("h" & fndentry.row).value 'product group wb1.sheets("sagsnr.").range("e" & i).value = wb2.sheets("matcost").range("q" & fndentry.row).value 'available stock wb1.sheets("sagsnr.").range("f" & i).value = wb2.sheets("matcost").range("e" & fndentry.row).value 'materiale name wb1.sheets("sagsnr.").range("h" & i).value = wb2.sheets("matcost").range("aj" & fndentry.row).value 'marked deletion wb1.sheets("sagsnr.").range("i" & i).value = wb2.sheets("matcost").range("m" & fndentry.row).value 'datasheet wb1.sheets("sagsnr.").range("k" & i).value = wb2.sheets("matcost").range("p" & fndentry.row).value 'lotsize wb1.sheets("sagsnr.").range("m" & i).value = wb2.sheets("matcost").range("f" & fndentry.row).value 'material type (fert/hawa) wb1.sheets("sagsnr.").range("n" & i).value = wb2.sheets("matcost").range("n" & fndentry.row).value 'date of cost update wb1.sheets("sagsnr.").range("o" & i).value = wb2.sheets("matcost").range("o" & fndentry.row).value 'last change of cost data wb1.sheets("sagsnr.").range("p" & i).value = wb2.sheets("matcost").range("k" & fndentry.row).value 'stock category wb1.sheets("sagsnr.").range("q" & i).value = wb2.sheets("matcost").range("l" & fndentry.row).value 'abc code wb1.sheets("sagsnr.").range("r" & i).value = wb2.sheets("matcost").range("v" & fndentry.row).value 'construction weight cu wb1.sheets("sagsnr.").range("s" & i).value = wb2.sheets("matcost").range("w" & fndentry.row).value 'construction weight al wb1.sheets("sagsnr.").range("t" & i).value = wb2.sheets("matcost").range("x" & fndentry.row).value 'sales weight cu wb1.sheets("sagsnr.").range("u" & i).value = wb2.sheets("matcost").range("y" & fndentry.row).value 'sales weight al wb1.sheets("sagsnr.").range("ac" & i).value = wb2.sheets("matcost").range("z" & fndentry.row).value 'construction weight pe wb1.sheets("sagsnr.").range("ad" & i).value = wb2.sheets("matcost").range("ad" & fndentry.row).value 'pe costs wb1.sheets("sagsnr.").range("ae" & i).value = wb2.sheets("matcost").range("aa" & fndentry.row).value 'construction weight pvc wb1.sheets("sagsnr.").range("af" & i).value = wb2.sheets("matcost").range("ae" & fndentry.row).value 'pvc costs wb1.sheets("sagsnr.").range("ag" & i).value = wb2.sheets("matcost").range("af" & fndentry.row).value 'other materials costs wb1.sheets("sagsnr.").range("ah" & i).value = wb2.sheets("matcost").range("ab" & fndentry.row).value 'variable production costs wb1.sheets("sagsnr.").range("ai" & i).value = wb2.sheets("matcost").range("ac" & fndentry.row).value 'fixed production costs end if set fndentry = wb2.sheets("matcost").range("c:c").find(what:=material) if not fndentry nothing 'if want include formatting source file, use below: 'wb2.sheets("source sheet - change me").range("source column - change me" & fndentry.row).copy destination:=wb1.sheets("destination sheet - change me").range("destination column - change me" & i) 'if want keep formatting of target file, use below: 'wb1.sheets("source sheet - change me").range("source column - change me" & i).value = wb2.sheets("destination sheet").range("destination column" & fndentry.row).value wb1.sheets("sagsnr.").range("b" & i).value = wb2.sheets("matcost").range("h" & fndentry.row).value 'product group wb1.sheets("sagsnr.").range("e" & i).value = wb2.sheets("matcost").range("q" & fndentry.row).value 'available stock wb1.sheets("sagsnr.").range("f" & i).value = wb2.sheets("matcost").range("e" & fndentry.row).value 'materiale name wb1.sheets("sagsnr.").range("h" & i).value = wb2.sheets("matcost").range("aj" & fndentry.row).value 'marked deletion wb1.sheets("sagsnr.").range("i" & i).value = wb2.sheets("matcost").range("m" & fndentry.row).value 'datasheet wb1.sheets("sagsnr.").range("k" & i).value = wb2.sheets("matcost").range("p" & fndentry.row).value 'lotsize wb1.sheets("sagsnr.").range("m" & i).value = wb2.sheets("matcost").range("f" & fndentry.row).value 'material type (fert/hawa) wb1.sheets("sagsnr.").range("n" & i).value = wb2.sheets("matcost").range("n" & fndentry.row).value 'date of cost update wb1.sheets("sagsnr.").range("o" & i).value = wb2.sheets("matcost").range("o" & fndentry.row).value 'last change of cost data wb1.sheets("sagsnr.").range("p" & i).value = wb2.sheets("matcost").range("k" & fndentry.row).value 'stock category wb1.sheets("sagsnr.").range("q" & i).value = wb2.sheets("matcost").range("l" & fndentry.row).value 'abc code wb1.sheets("sagsnr.").range("r" & i).value = wb2.sheets("matcost").range("v" & fndentry.row).value 'construction weight cu wb1.sheets("sagsnr.").range("s" & i).value = wb2.sheets("matcost").range("w" & fndentry.row).value 'construction weight al wb1.sheets("sagsnr.").range("t" & i).value = wb2.sheets("matcost").range("x" & fndentry.row).value 'sales weight cu wb1.sheets("sagsnr.").range("u" & i).value = wb2.sheets("matcost").range("y" & fndentry.row).value 'sales weight al wb1.sheets("sagsnr.").range("ac" & i).value = wb2.sheets("matcost").range("z" & fndentry.row).value 'construction weight pe wb1.sheets("sagsnr.").range("ad" & i).value = wb2.sheets("matcost").range("ad" & fndentry.row).value 'pe costs wb1.sheets("sagsnr.").range("ae" & i).value = wb2.sheets("matcost").range("aa" & fndentry.row).value 'construction weight pvc wb1.sheets("sagsnr.").range("af" & i).value = wb2.sheets("matcost").range("ae" & fndentry.row).value 'pvc costs wb1.sheets("sagsnr.").range("ag" & i).value = wb2.sheets("matcost").range("af" & fndentry.row).value 'other materials costs wb1.sheets("sagsnr.").range("ah" & i).value = wb2.sheets("matcost").range("ab" & fndentry.row).value 'variable production costs wb1.sheets("sagsnr.").range("ai" & i).value = wb2.sheets("matcost").range("ac" & fndentry.row).value 'fixed production costs end if end if next wb2.close wb1.sheets("sagsnr.").rows("1:1").hidden = true application.displayalerts = true application.screenupdating = true end sub
when use
wb1.sheets("sagsnr.").range("b" & i).value = wb2.sheets("matcost").range("h" & fndentry.row).value 'product group excel has find sheets "sagsnr." , "matcost" , respective ranges in them each cell copy.
can instead, save worksheets , ranges same way workbooks before loop:
dim wsto worksheet, wsfrom worksheet set wsto = wb1.sheets("sagsnr.") set wsfrom = wb2.sheets("matcost") dim rngto range, rngfrom range then inside loop:
set rngto = wsto.range("a" & i) set rngfrom = wsfrom.range("a" & fndentry.row) rngto(, "b") = rngfrom(, "h") ' product group rngto(, "e") = rngfrom(, "q") ' available stock ' ... , add same rest of columns can speed more if can copy ranges of cells @ time instead of cell cell.
example, in case can filter source rows , copy columns:
dim materials ' variant materials = wsto.range("c21:c" & lr) materials = worksheetfunction.transpose(materials) 'flips "vertical" "horisontal" wsfrom.usedrange.autofilter 4, materials, xlfiltervalues ' 4 column d:d in "matcost" ' set copy , paste ranges set rngfrom = wsfrom.range("a2:a" & wsfrom.usedrange.rows.count) ' skips header cells set rngto = wsto.range("a21") ' paste on row 21 ' "rngto(, "b") = rngfrom(, "h") ' product group" becomes: rngfrom.columns("h").copy ' copy filtered (visible) cells in column h rngto(, "b").pastespecial ' or wsto.range("b21").pastespecial ' ... , add same rest of columns application.cutcopymode = false '"cancels cut or copy mode , removes moving border" wsto.usedrange.autofilter 4 ' optional clear filter column d:d
Comments
Post a Comment