excel vba - VBA Filtering Loop in Loop -
i found code, 1 column find unique values, , filter them,copy/paste in filtered values named sheet.
but need do, filter 2 columns, , name same principles, modified it.
somehow on second value in first loop, doesnt start loop in other loop.
why give me blanks in second loop?
sub datu_sagrupesana() dim x range, y range, rng range, last long, sht worksheet application.screenupdating = false 'datu vieta set sht = thisworkbook.worksheets("test") 'apgabals last = sht.cells(rows.count, "a").end(xlup).row set rng = sht.range("a1:c" & last) sht.range("a1:a" & last).advancedfilter action:=xlfiltercopy, copytorange:=range("h1"), unique:=true 'produkta filtrs sht.range("c1:c" & last).advancedfilter action:=xlfiltercopy, copytorange:=range("j1"), unique:=true 'valodas filtrs each y in range([j2], cells(rows.count, "j").end(xlup)) each x in range([h2], cells(rows.count, "h").end(xlup)) rng .autofilter .autofilter field:=3, criteria1:=y.value .autofilter field:=1, criteria1:=x.value .specialcells(xlcelltypevisible).copy sheets.add(after:=sheets(sheets.count)).name = y.value & x.value activesheet.paste end next x next y 'nonemt filtru sht.autofiltermode = false application .cutcopymode = false .screenupdating = true end end sub
solved myself
sub datu_sagrupesana() dim x long, y range, rng range, last long, sht worksheet application.screenupdating = false 'datu vieta set sht = thisworkbook.worksheets("test") 'apgabals last = sht.cells(rows.count, "a").end(xlup).row set rng = sht.range("a1:c" & last) sht.range("a1:a" & last).advancedfilter action:=xlfiltercopy, copytorange:=range("h1"), unique:=true 'produkta filtrs sht.range("c1:c" & last).advancedfilter action:=xlfiltercopy, copytorange:=range("i1"), unique:=true 'valodas filtrs pr = application.worksheetfunction.counta(sht.columns("h")) va = application.worksheetfunction.counta(sht.columns("i")) j = 2 va = 2 pr valoda = sht.cells(j, "i").value produkts = sht.cells(i, "h").value ' 'for each y in range("j2", cells(rows.count, "j").end(xlup)) ' ' 'for each x in range("h2", cells(rows.count, "h").end(xlup)) ' rng .autofilter .autofilter field:=3, criteria1:=valoda .autofilter field:=1, criteria1:=produkts .specialcells(xlcelltypevisible).copy sheets.add(after:=sheets(sheets.count)).name = valoda & produkts activesheet.paste end ' 'next x 'next y next next j 'nonemt filtru sht.autofiltermode = false application .cutcopymode = false .screenupdating = true end end sub 
Comments
Post a Comment