what want achieve copy data ws1 ws3 based on criteria.
i have 2 worksheets:
ws1 = raw data ws2 = atlas data in columns of both there unique identifiers. want create ws3=reconciliation. values in ws2 against ws1. match found want copy row(s) ws1 ws3 have reverse engineered code , came 1 below
sub copyandpaste() dim x string, cpyrng range dim mfind range, mfirst range sheets("raw data") range("a:a").select on error resume next end with sheets("atlas data") set mfind = .range("a:a").find(x, lookin:=xlvalues, lookat:=xlwhole) if not mfind nothing set cpyrng = mfind set mfirst = mfind set cpyrng = union(cpyrng, mfind) set mfind = .range("a:a").findnext(mfind) loop until mfind.address = mfirst.address cpyrng.entirerow.copy sheets("rec").range("a" & rows.count).end(xlup).offset(1) end if end end sub
based on description of problem; try this
option explicit sub copyandpaste() application.screenupdating = false dim long, j long, lastrow1 long, lastrow2 long, cnt long dim ws1 worksheet, ws2 worksheet, ws3 worksheet set ws1 = activeworkbook.sheets("raw data") set ws2 = activeworkbook.sheets("atlas data") set ws3 = activeworkbook.sheets("reconciliation") lastrow1 = ws1.range("a" & rows.count).end(xlup).row lastrow2 = ws2.range("a" & rows.count).end(xlup).row cnt = 1 = 1 lastrow1 j = 1 lastrow2 if strcomp(cstr(ws2.range("a" & j).value), _ cstr(ws1.range("a" & i).value), _ vbtextcompare) = 0 ws1.activate ws1.rows(i).select selection.copy ws3.activate ws3.range("a" & cnt).select selection.pastespecial paste:=xlpasteallusingsourcetheme application.cutcopymode = false cnt = cnt + 1 end if next j next application.screenupdating = true end sub 
Comments
Post a Comment