excel - vba to copy selected data to multiple sheets based upon criteria -


i have code below copies data 1 table 3 other existing sheets need copy selected columns of data other sheets summary , append existing data.

there 9 columns in table need copy 4 of these other sheet. column 5 holds criteria used split data , need copy columns 1,2,4 , 8. 

sub splitdata()  dim wsinput worksheet dim wsoutput worksheet dim wsnames variant dim long dim lastrow long  set wsinput = sheets("input sheet") wsnames = array("output 1", "output 2", "output 3") const filtercolumn = 7  wsinput lastrow = wsinput.range("c" & rows.count).end(xlup).row     = 0 ubound(wsnames)    set wsoutput = worksheets(wsnames(i))    wsoutput.cells.clearcontents         wsinput.range("c105:k" & lastrow)        .autofilter field:=filtercolumn, criteria1:=wsnames(i)        .offset(0, 0).copy wsoutput.range("a2")        end    next  end  end sub

any assistance appreciated.

i use code below copy other data in same workbook sheet , struggling find method of combining table of data needs copied , split multiple sheets.

sub copyexpenditure()  dim wscopyfrom worksheet dim wscopyexpto worksheet  set wscopyfrom = worksheets("input sheet") set wscopyexpto = worksheets("cash expenditure list")  lastrow = wscopyexpto.range("c" & rows.count).end(xlup).row  wscopyfrom.activate if wscopyfrom.range("f59") = 0 exit sub end if  if wscopyfrom.range("g59") > 0     msgbox ("row(s) contain description not amount. please enter amount or clear description field") exit sub end if  wscopyexpto.activate if range("c" & lastrow) = "total" rows(lastrow).entirerow.select selection.delete shift:=xlup  end if  lastcopyrow = wscopyfrom.range("c57").row  x = 37 lastcopyrow wscopyfrom.activate lastrow2 = wscopyexpto.range("c" & rows.count).end(xlup).row activesheet.range("c37:f57").select     chkvalue = cells(x, 6).value     if chkvalue > 0     cells(x, 3).resize(1, 4).copy wscopyexpto.activate activesheet.range("c" & lastrow2 + 1).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false end if next x  lastrow4 = wscopyexpto.range("a" & rows.count).end(xlup).row  wscopyfrom.activate activesheet.range("d7").select selection.copy wscopyexpto.activate activesheet.range("a" & lastrow4 + 1).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false activesheet.range("a10:a" & lastrow4 + 1).numberformat = "dd/mm/yyyy"  wscopyfrom.activate activesheet.range("f7").select selection.copy wscopyexpto.activate activesheet.range("b" & lastrow4 + 1).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false application.cutcopymode = false  lastrow3 = wscopyexpto.range("c" & rows.count).end(xlup).row  wscopyexpto.range("a" & lastrow2 + 1).select range("a" & lastrow4 + 1 & ":a" & lastrow3).filldown  wscopyexpto.range("b" & lastrow2 + 1).select range("b" & lastrow4 + 1 & ":b" & lastrow3).filldown   range("g10:ab10").select range("a10").entirerow.hidden = true selection.autofill destination:=range("g10:ab" & lastrow3), type:=xlfilldefault  wscopyexpto.cells(lastrow3 + 2, 3) = "total" wscopyexpto.cells(lastrow3 + 2, 6) = worksheetfunction.sum(range("f11:f" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 7) = worksheetfunction.sum(range("g11:g" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 8) = worksheetfunction.sum(range("h11:h" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 9) = worksheetfunction.sum(range("i11:i" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 10) = worksheetfunction.sum(range("j11:j" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 11) = worksheetfunction.sum(range("k11:k" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 12) = worksheetfunction.sum(range("l11:l" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 13) = worksheetfunction.sum(range("m11:m" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 14) = worksheetfunction.sum(range("n11:n" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 15) = worksheetfunction.sum(range("o11:o" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 16) = worksheetfunction.sum(range("p11:p" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 17) = worksheetfunction.sum(range("q11:q" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 18) = worksheetfunction.sum(range("r11:r" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 19) = worksheetfunction.sum(range("s11:s" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 20) = worksheetfunction.sum(range("t11:t" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 21) = worksheetfunction.sum(range("u11:u" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 22) = worksheetfunction.sum(range("v11:v" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 23) = worksheetfunction.sum(range("w11:w" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 24) = worksheetfunction.sum(range("x11:x" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 25) = worksheetfunction.sum(range("y11:y" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 26) = worksheetfunction.sum(range("z11:z" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 27) = worksheetfunction.sum(range("aa11:aa" & lastrow3)) wscopyexpto.cells(lastrow3 + 2, 28) = worksheetfunction.sum(range("ab11:ab" & lastrow3))  wscopyexpto.rows(lastrow3 + 2)     .font.colorindex = 1     .font.bold = true     .numberformat = "_-#,###.00_-;_-(#,###.00);0.00_-" end  range("ab10").entirecolumn.hidden = true    end sub

would best split 2 tasks; first split data onto 3 temporary sheets , second task required format?


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more