SOLVED

COUNT & Copy to Last Row, Paste to another sheet to Last Row (FIRST EMPTY ROW, adding, not replacing

%3CLINGO-SUB%20id%3D%22lingo-sub-2114724%22%20slang%3D%22en-US%22%3ECOUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20replacing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114724%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20friends.%20Im%20New%20in%20VBA.%20Could%20you%20help%20me%20at%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20we%20have%20%3CSTRONG%3E2%20columns%3A%20AB%3C%2FSTRONG%3E%26nbsp%3Bwith%20data%2C%20in%20Sheet1%3CBR%20%2F%3EI%20want%20to%20%3CSTRONG%3ECOPY%20until%20Last%20Row%3C%2FSTRONG%3E%26nbsp%3Bin%20AB%20Column%20%3CSTRONG%3Efrom%20Sheet1%3C%2FSTRONG%3E%3CBR%20%2F%3Eand%20%3CSTRONG%3Epaste%20it%20to%20Sheet2%2C%20column%20CD%20in%20FIRST%20EMPTY%20ROW%3C%2FSTRONG%3E%26nbsp%3B(i%20mean%20by%20adding%20more%20data%2C%20cause%20CD%20is%20supposed%20to%20have%20previous%20data)%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%20It%20would%20be%20a%20huge%20favour%20for%20me!%20THANKS%20!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2114724%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121403%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20you%20have%20found%2C%20you%20can%20have%20only%20one%20Worksheet_Change%20event%20procedure%20in%20a%20worksheet%20module%2C%3C%2FP%3E%0A%3CP%3EYou'll%20have%20to%20add%20a%20second%20If%20...%20End%20If%20block%20in%20the%20existing%20procedure.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121072%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BYep%2C%20works%20perfect%20my%20friend.%3C%2FP%3E%3CP%3EAnd%20if%20i%20want%20to%20have%20two%20cells%20for%20example%20from%20the%20same%20sheet1%3F%20I%20tried%20adding%20another%20private%20sub%20code%2C%20down%20below%2C%20but%20the%20second%20cell%20seemed%20not%20to%20work!%20Only%20one%20worked.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20should%20they%20be%20included%20to%20one%20code.%20for%20example%2C%20i%20want%20to%20add%20another%20cell%20C3%20for%20Sheet3%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121043%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab%20of%20Sheet1.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20If%20Not%20Intersect(Range(%22B3%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20If%20Range(%22B3%22).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Worksheets(%22Sheet2%22).Range(%22A%3AA%22).Find%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(What%3A%3DRange(%22B3%22).Value%2C%20LookIn%3A%3DxlValues%2C%20LookAt%3A%3DxlWhole)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Application.Goto%20rng%2C%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20code%20will%20only%20find%20what%20you%20enter%20in%20B3%20if%20it%20is%20the%20whole%20cell%20value%20(for%20example%2C%20if%20you%20enter%20%3CSTRONG%3EExcel%3C%2FSTRONG%3E%2C%20it%20will%20find%20a%20cell%20that%20contains%20%3CSTRONG%3EExcel%3C%2FSTRONG%3E%2C%20but%20not%20a%20cell%20that%20contains%20%3CSTRONG%3EMicrosoft%20Excel%3C%2FSTRONG%3E).%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20find%20the%20latter%20too%2C%20change%20xlWhole%20to%20xlPart%20in%20the%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121013%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121013%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%20friend%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20would%20like%20a%20simple%20trick%2C%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20%3CSTRONG%3ESheet1%3C%2FSTRONG%3E%20i%20want%20to%20use%20cell%26nbsp%3B%3CSTRONG%3EB3%2C%20%3C%2FSTRONG%3Eas%20a%3CEM%3E%20search%20cell%3C%2FEM%3E.%20So%20when%20i%20enter%20certain%20text%2C%20i%20want%20a%20macro%20(by%20pressing%20a%20button)%20that%20goes%20to%20%3CSTRONG%3ESheet2%3C%2FSTRONG%3E%20in%20%3CSTRONG%3EColumnA%20%3C%2FSTRONG%3Eand%20%3CEM%3E%3CU%3Esearch%20for%20that%20text%20and%20select%20that%20cell%20!%3C%2FU%3E%3C%2FEM%3E%20if%20can't%20find%20anything%2C%20MsgBox%20%22No%20resulsts%20found!%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20be%20done%3F%3C%2FP%3E%3CP%3EVERY%20APPRECIATED!%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2118354%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Test()%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20n%20%3D%20Application.CountIf(Range(%22L%3AL%22)%2C%20%22Yes%22)%0A%20%20%20%20If%20MsgBox(%22Yes%20occurs%20%22%20%26amp%3B%20n%20%26amp%3B%20%22%20times%20in%20column%20L.%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%22Do%20you%20want%20to%20continue%3F%22%2C%20vbYesNo%20%2B%20vbQuestion)%20%3D%20vbNo%20Then%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20'%20More%20code%0A%20%20%20%20'...%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2118335%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eas%20Always%2C%20perfect!%20Thank%20you!%20And%20i%20think%20this%20is%20the%20very%20last%20one%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20%3CSTRONG%3EColumn%20L%3C%2FSTRONG%3E%20i%20have%20data.%20i%20need%20a%20code%20that%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ECounts%3C%2FSTRONG%3E%20how%20many%20%22%3CSTRONG%3EYes%3C%2FSTRONG%3E%22%20there%20are%20in%3CSTRONG%3E%20column%20L%3C%2FSTRONG%3E%20and%20shows%20the%20number%20in%20MsgBox%20and%20asking%20you%20want%20to%20continue%20or%20not%20(in%20same%20MsgBox).%20If%20%3CU%3E%3CEM%3ENo%3C%2FEM%3E%3C%2FU%3E%2C%20End%20Sub.%20(if%20%3CU%3E%3CEM%3EYes%3C%2FEM%3E%3C%2FU%3E%2C%26nbsp%3B%20dont%20worry%20i%20have%20the%20rest%20of%20code.....)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2118287%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20MoveDups()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20Range(%22A1%22).EntireColumn.Insert%0A%20%20%20%20Range(%22A1%22).Value%20%3D%20%22Count%22%0A%20%20%20%20Range(%22A2%3AA%22%20%26amp%3B%20m).Formula%20%3D%20%22%3DCOUNTIF(B%242%3AB%24%22%20%26amp%3B%20m%20%26amp%3B%20%22%2CB2)%22%0A%20%20%20%20Range(%22A1%22).CurrentRegion.Sort%20Key1%3A%3DRange(%22A1%22)%2C%20Order1%3A%3DxlDescending%2C%20Header%3A%3DxlYes%0A%20%20%20%20Range(%22A1%22).EntireColumn.Delete%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2118097%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118097%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehello%20Hans.%3C%2FP%3E%3CP%3Ei%20would%20need%20a%20little%20code%2C%20could%20you%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20find%26nbsp%3B%3CSTRONG%3Eduplications%20%3C%2FSTRONG%3Ein%20%3CSTRONG%3EColumn%20A%26nbsp%3B%3C%2FSTRONG%3Eand%3CSTRONG%3E%20bring%20%3C%2FSTRONG%3Ethe%3CSTRONG%3E%26nbsp%3Bentire%20row%20of%20duplications%20%3C%2FSTRONG%3E(found%20in%20column%20A)%3CSTRONG%3E%2C%20in%20row2%20%3C%2FSTRONG%3E(row1%20has%20headers)%3CSTRONG%3E%2C%20%3C%2FSTRONG%3Elike%3CSTRONG%3E%20listing%2Fshifting%20up%20%3C%2FSTRONG%3Ein%3CSTRONG%3E%20row2%2C%20%3C%2FSTRONG%3Ewhile%20other%20rows%2Fdata%20are%20shifted%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%26nbsp%3B%3C%2FP%3E%3CP%3E(no%20undo%20code%20necessary)%3C%2FP%3E%3CP%3EOnce%20again%2C%20billion%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117187%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerfect%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Thanks%20Master.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117174%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117174%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20FillIt()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Sheets(%22DB-Skadencave%22).Select%0A%20%20%20%20Range(%22D2%22).Select%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20_%0A%20%20%20%20%20%20%20%20%22%3DIF(OR(RC%5B-1%5D%3D%22%22%22%22%2CRC%5B-1%5D%3D%22%22-%22%22)%2C%20%22%22-%22%22%2C%20(RC%5B-1%5D-TODAY())%26amp%3B%22%22%20dit%C3%AB%20(%22%22%26amp%3BROUNDUP(((RC%5B-1%5D-TODAY())%2F30)%2C1)%26amp%3B%22%22%20muaj)%22%22)%22%0A%20%20%20%20r%20%3D%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20If%20r%20%26gt%3B%202%20Then%0A%20%20%20%20%20%20%20%20Range(%22D2%3AD%22%20%26amp%3B%20r).FillDown%0A%20%20%20%20End%20If%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E(If%20there%20is%20only%20one%20item%2C%20there%20is%20nothing%20to%20fill%20down)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117152%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%2C%20but%20with%20a%20little%20%3CSTRONG%3Eissue%3C%2FSTRONG%3E.%20when%20it%20finds%20only%20%3CSTRONG%3E1%20value%3C%2FSTRONG%3E%20from%20%3CSTRONG%3EColumn%20A%3C%2FSTRONG%3E%2C%20in%20%3CSTRONG%3ED2%3C%2FSTRONG%3E%20will%20be%20paste%20%3CSTRONG%3ED1%3C%2FSTRONG%3E%2C%20and%20not%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(this%20%3CSTRONG%3Eissue%3C%2FSTRONG%3E%20is%20only%20in%20the%20%3CSTRONG%3Efirst%20run%3C%2FSTRONG%3E.%20when%20it%20finds%20two%20or%20more%20values%20it%20runs%20perfectly.%20and%20even%20if%20it%20finds%201%20value%20on%20second%20execution%2C%20it%20runs%20perfect%20too.%20%3CEM%3Eonly%20first%20execution%2C%20with%20one%20value%2C%20it%20just%20copies%20D1%20to%20D2%3C%2FEM%3E.%20Why%20so%3F)%3C%2FP%3E%3CP%3Emodified%20just%20a%20little%2C%20but%20it%20is%20the%20same%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20FillIt()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Sheets(%22DB-Skadencave%22).Select%0A%20%20%20%20Range(%22D2%22).Select%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20_%0A%20%20%20%20%20%20%20%20%22%3DIF(OR(RC%5B-1%5D%3D%22%22%22%22%2CRC%5B-1%5D%3D%22%22-%22%22)%2C%20%22%22-%22%22%2C%20(RC%5B-1%5D-TODAY())%26amp%3B%22%22%20dit%C3%AB%20(%22%22%26amp%3BROUNDUP(((RC%5B-1%5D-TODAY())%2F30)%2C1)%26amp%3B%22%22%20muaj)%22%22)%22%0A%20%20%20%20r%20%3D%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20Range(%22D2%3AD%22%20%26amp%3B%20r).FillDown%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117102%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117102%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20this%20do%20what%20you%20want%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20FillIt()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20r%20%3D%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20Range(%22D2%3AD%22%20%26amp%3B%20r).FillDown%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117091%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorks%20Perfect!%20Thanks.%20And%20i%20im%20stuck%20at%20a%20little%20trick.%3C%2FP%3E%3CP%3EI%20have%20this%20%3CSTRONG%3Eformula%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(C2%3D%22%22%2CC2%3D%22-%22)%2C%20%22-%22%2C%20(C2-TODAY())%26amp%3B%22%20dit%C3%AB%20(%22%26amp%3BROUNDUP(((C2-TODAY())%2F30)%2C1)%26amp%3B%22%20muaj)%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20put%20it%20to%20%3CSTRONG%3ED2%26nbsp%3B%3C%2FSTRONG%3Eand%20%3CSTRONG%3Eautofill%20(below)%2C%3C%2FSTRONG%3E%20until%3CSTRONG%3E%20Last%20Used%20Row%3C%2FSTRONG%3E%26nbsp%3Bfrom%2Fbased%20on%3CSTRONG%3E%26nbsp%3BColumn%20A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20tried%20several%20ways%20but%20im%20facing%20issues%20...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117034%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20w1%20As%20Worksheet%0A%20%20%20%20Dim%20w2%20As%20Worksheet%0A%20%20%20%20Dim%20c%20As%20Range%0A%20%20%20%20Dim%20s%20As%20String%0A%20%20%20%20Dim%20r1%20As%20Long%0A%20%20%20%20Dim%20r2%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Set%20w1%20%3D%20Worksheets(%22Sheet1%22)%0A%20%20%20%20Set%20w2%20%3D%20Worksheets(%22Sheet2%22)%0A%20%20%20%20Set%20c%20%3D%20w1.Range(%22L%3AL%22).Find(What%3A%3D%22Yes%22%2C%20LookIn%3A%3DxlValues%2C%20LookAt%3A%3DxlWhole%2C%20MatchCase%3A%3DFalse)%0A%20%20%20%20If%20Not%20c%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20%20%20%20%20r2%20%3D%20w2.Range(%22A%3AC%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20%20%20%20%20s%20%3D%20c.Address%0A%20%20%20%20%20%20%20%20Do%0A%20%20%20%20%20%20%20%20%20%20%20%20r1%20%3D%20c.Row%0A%20%20%20%20%20%20%20%20%20%20%20%20r2%20%3D%20r2%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20w2.Range(%22A%22%20%26amp%3B%20r2).Value%20%3D%20w1.Range(%22B%22%20%26amp%3B%20r1).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20w2.Range(%22C%22%20%26amp%3B%20r2).Value%20%3D%20w1.Range(%22D%22%20%26amp%3B%20r1).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20Set%20c%20%3D%20w1.Range(%22L%3AL%22).Find(What%3A%3D%22Yes%22%2C%20After%3A%3Dc%2C%20LookIn%3A%3DxlValues%2C%20LookAt%3A%3DxlWhole%2C%20MatchCase%3A%3DFalse)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20c%20Is%20Nothing%20Then%20Exit%20Do%0A%20%20%20%20%20%20%20%20Loop%20Until%20c.Address%20%3D%20s%0A%20%20%20%20End%20If%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116980%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%20my%20friend%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%20in%20VBA%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIn%20Sheet1%26nbsp%3B%3C%2FSTRONG%3Ei%20have%20data.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EIf%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ein%3CSTRONG%3E%26nbsp%3BColumn%20L%2C%26nbsp%3B%3C%2FSTRONG%3Ecells%20contain%20%22%3CSTRONG%3EYes%3C%2FSTRONG%3E%22%2C%3C%2FP%3E%3CP%3Ethen%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ECopy%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CU%3EONLY%3C%2FU%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ecells%20from%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EColumn%20B%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EColumn%20D%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ESAME%20ROW%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EPaste%26nbsp%3B%3C%2FSTRONG%3Eto%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ESheet2%26nbsp%3B%3C%2FSTRONG%3Ein%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EColumn%20A%26nbsp%3B%3C%2FSTRONG%3Eand%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EColumn%20C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3Eto%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ELast%20Row%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease.%20Appreciations.%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116673%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116673%22%20slang%3D%22en-US%22%3Ei%20made%20ittt.%20no%20need.%20thanks%20a%20lot%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116647%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116647%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20with%20a%20little%20trick%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20put%20in%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ECell%20B2%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3Ea%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eformula%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3Ethrough%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EVBA%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3Eand%20then%20it%3CSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eapplies%20%2Fdropdowns%20%2F%20down%20below%3C%2FSTRONG%3E%26nbsp%3Bthrough%3CSPAN%3E%26nbsp%3BB%3C%2FSPAN%3E%3CU%3E2%3C%2FU%3E%26nbsp%3B(%20to%20B3%2C%20B4%2C%20B5%2C%20etc)%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Euntil%26nbsp%3B%3C%2FSTRONG%3E%3CSTRONG%3E%3CU%3E%3CEM%3ELast%20Used%20Row%3C%2FEM%3E%3C%2FU%3E%3C%2FSTRONG%3E%26nbsp%3Bfrom%26nbsp%3B%3CSTRONG%3EColumn%20A.%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116602%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116602%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20looks%20different%20again!%20Its%20difficult%20to%20keep%20track%20if%20the%20layout%20and%20requirements%20change%20every%20time.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20MsgBox%20%22No%20data%20to%20copy!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20Worksheets(%22Sheet2%22).Range(%22A2%3AE%22%20%26amp%3B%20m).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22A%22%20%26amp%3B%20m%20%2B%201)%0A%20%20%20%20Worksheets(%22Sheet2%22).Range(%22A2%3AB%22%20%26amp%3B%20m).Value%20%3D%20Worksheets(%22Sheet1%22).Range(%22A2%3AB%22%20%26amp%3B%20m).Value%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116601%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Here%3A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116599%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20attach%20a%20sample%20workbook%20that%20has%20formatting%20and%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116597%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116597%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELike%20this%3A%20(in%20%22earlier%20sheet2%22%20please%20just%20a%20lil%20mistake%2C%20just%20%22data%22%20not%20%22previous%20shifted%20data%22)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22final.png%22%20style%3D%22width%3A%20944px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F252965i15060F3F94557173%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22final.png%22%20alt%3D%22final.png%22%20%2F%3E%3C%2FSPAN%3E%3CSTRONG%3Ecount%20copy%20paste%3C%2FSTRONG%3E%20to%20%3CSTRONG%3Ecolumn%20a%3C%2FSTRONG%3E%20and%20%3CSTRONG%3Ecolumn%20b%3C%2FSTRONG%3E%20from%3CSTRONG%3E%20sheet1%20%3C%2FSTRONG%3Eto%3CSTRONG%3E%20sheet2%2C%20%3C%2FSTRONG%3Eand%20just%3CU%3E%20keeping%20format%3C%2FU%3E%20and%20%3CU%3Eformulas%3C%2FU%3E%20from%20%3CU%3Esheet2%3C%2FU%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116593%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Yes%20just%20a%20sec%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116592%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20provide%20a%20sample%20workbook%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116590%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Super.%20Thank%20you.%20And%20just%20one%20more%20simple%20case.%3C%2FP%3E%3CP%3Ewhat%20about%20just%20from%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EColumn%20A%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EColumn%20B%3C%2FSTRONG%3E%20from%20%3CSTRONG%3ESheet1%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eto%20%3CSTRONG%3Ecolumn%20A%3C%2FSTRONG%3E%20and%20%3CSTRONG%3Ecolumn%20B%2C%20sheet2%3C%2FSTRONG%3E%20(count%20copy%20paste)%20%3CU%3Epasted%20%3C%2FU%3Eto%20%3CSTRONG%3Erow2%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20this%20%3CU%3Ewhat%20we%20pasted%3C%2FU%3E%2C%20to%20have%20the%20%3CSTRONG%3Eformat%3C%2FSTRONG%3E%20and%20%3CSTRONG%3Eformulas%3C%2FSTRONG%3E%26nbsp%3Bof%20%3CU%3Erow3%20in%20this%20sheet2.%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(flexible%20formula%2C%20not%20fixed%2C%20like%20droping%20it%2C%20i%20mean%20to%20be%20suited%20to%20the%20current%20row%2C)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(this%20simple)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116576%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20code%20now%20looks%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AC%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20MsgBox%20%22No%20data%20to%20copy!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%3AB%22%20%26amp%3B%20m).Copy%0A%20%20%20%20Worksheets(%22Sheet2%22).Range(%22B2%3AC%22%20%26amp%3B%20m%20%2B%201).Insert%20Shift%3A%3DxlShiftDown%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22D1%22).Copy%0A%20%20%20%20Worksheets(%22Sheet2%22).Range(%22A2%3AA%22%20%26amp%3B%20m%20%2B%201).Insert%20Shift%3A%3DxlShiftDown%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116554%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116554%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20sorry%20and%20this%20is%20the%20last%20one.%20code%20works%20perfect%20and%20i%20just%20wanted%20the%20idea.%20but%20finds%20out%20cant%20do%20the%20custumization.%20The%20cells%20are%20like%20this%20in%20the%20photo%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22here.png%22%20style%3D%22width%3A%20857px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F252962i0C420718E577A34E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22here.png%22%20alt%3D%22here.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ei%20faced%20difficulties%20on%20modifying%20this.%20The%20idea%20is%20the%20same%20as%20previous%20comment%2C%20just%20the%20%3CEM%3E%3CU%3Ecells%3C%2FU%3E%3C%2FEM%3E%20are%20slighlty%20different.%20Is%20this%20possible%3F%20Last%20question.%20Thanks%20billion%20times.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116545%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116545%22%20slang%3D%22en-US%22%3ESure.%20thanks%20a%20lot%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116532%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20left%20in%20a%20couple%20of%20lines%20that%20aren't%20needed%20anymore.%20Here%20is%20a%20version%20without%20them%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20MsgBox%20%22No%20data%20to%20copy!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%3AC%22%20%26amp%3B%20m).Copy%0A%20%20%20%20Worksheets(%22Sheet2%22).Range(%22A2%3AC%22%20%26amp%3B%20m%20%2B%201).Insert%20Shift%3A%3DxlShiftDown%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22C1%22).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22C2%22).Resize(m)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116527%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116527%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Words%20are%20not%20enough%20to%20thank%20you%20my%20friend!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20You%20are%20amazing!%20God%20bless%20you!%20Thank%20you%20a%20lot%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116517%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20MsgBox%20%22No%20data%20to%20copy!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20r%20%3D%20Worksheets(%22Sheet2%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%3AC%22%20%26amp%3B%20m).Copy%0A%20%20%20%20Worksheets(%22Sheet2%22).Range(%22A2%3AC%22%20%26amp%3B%20m%20%2B%201).Insert%20Shift%3A%3DxlShiftDown%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22C1%22).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22C2%22).Resize(m)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116493%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20haha%20yes%20i%20changed%20it%20a%20little%20so%20perhaps%20it%20could%20be%20easier%20to%20understand.%3C%2FP%3E%3CP%3Eand%20if%20i%20wanted%20all%20i%20said%20in%20the%20previous%20comment%20%3CU%3E%3CSTRONG%3EBUT%3C%2FSTRONG%3E%3C%2FU%3E%20with%20the%20only%20change%20that%20to%20be%20pasted%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20%3CSTRONG%3ETo%20Last%20Row%2C%20%3C%2FSTRONG%3Ebut%20always%20in%20%3CSTRONG%3ERow2%3C%2FSTRONG%3E.%20(row%201%20has%20headers%20for%20example%20and%20dont%20wanna%20touch).%20and%20the%20other%20%3CSTRONG%3E(previous)%20data%3C%2FSTRONG%3E%20in%20those%20column%20A%20and%20B%20in%20sheet2%2C%20could%20%3CSTRONG%3Eshift%20below%3C%2FSTRONG%3E%20the%20new%20data%20pasted%20in%20row%202!%3C%2FP%3E%3CP%3ECan%20this%20happen%3F%3C%2FP%3E%3CP%3E(ps.%20That%20would%20save%20me%20a%20lot%20of%20work%20of%20second%20hands%20sheets)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116472%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116472%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20changed%20the%20question%20slightly%3A%20instead%20of%20pasting%20to%20columns%20C%20and%20D%20on%20Sheet2%2C%20you%20now%20want%20to%20paste%20to%20columns%20A%20and%20B.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20MsgBox%20%22No%20data%20to%20copy!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20r%20%3D%20Worksheets(%22Sheet2%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%20%2B%201%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20r%20%3D%201%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%3AB%22%20%26amp%3B%20m).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22A%22%20%26amp%3B%20r)%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22C1%22).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22C%22%20%26amp%3B%20r).Resize(m)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116439%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BHmm%20ok%2C%20think%20of%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20in%20%3CSTRONG%3ESheet1%3C%2FSTRONG%3E%20and%20we%20have%20data%3A%20in%20%3CEM%3E%3CU%3EColumn%20A%3C%2FU%3E%3C%2FEM%3E%20and%20%3CEM%3E%3CU%3EColumn%20B%26nbsp%3B%3C%2FU%3E%3C%2FEM%3Eand%20data%20in%20%3CU%3E%3CEM%3EC1%3C%2FEM%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%3CEM%3Ewhat%20i%20need%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ECount%2C%20Copy%2C%20Paste%3C%2FSTRONG%3E%3CU%3E%3CEM%3E%20Column%20A%20%3C%2FEM%3E%3C%2FU%3Eand%20%3CU%3E%3CEM%3EColumn%20B%3C%2FEM%3E%3C%2FU%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3Efrom%20%3CSTRONG%3ESheet1%3C%2FSTRONG%3E%26nbsp%3B%20-%20to%20%3CSTRONG%3ESheet2%20%3C%2FSTRONG%3E%3CEM%3Eto%20Last%20Row%26nbsp%3B%3C%2FEM%3Ein%3CEM%3E%20%3CU%3EColumn%20A%3C%2FU%3E%3C%2FEM%3E%20and%20%3CEM%3E%3CU%3EColumn%20B%3C%2FU%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3Eand%3C%2FP%3E%3CP%3E%3CU%3E%3CEM%3EC1%3C%2FEM%3E%3C%2FU%3E%26nbsp%3Bfrom%20%3CSTRONG%3ESheet1%3C%2FSTRONG%3E%20to%20be%20pasted%20to%20%3CU%3E%3CEM%3EColumn%20C%3C%2FEM%3E%3C%2FU%3E%3CSTRONG%3E%20Sheet2%2C%20%3C%2FSTRONG%3Eand%20to%20be%20multiplied%2Fdrop-down-cell%2Fthat%20long%2C%20that%20column%20B%20has%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20photo%3A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116412%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20sorry%2C%20I%20don't%20understand%20this%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116396%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116396%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20So%20we%20have%20our%20code%20here.%20How%20can%20we%20modify%20this%20that%3A%20we%20have%20a%20text%20in%20Sheet1%20cell%20C1%2C%20to%20be%20pasted%20in%20Sheet2%20Column%20E%2C%20that%20times%20how%20long%20is%20column%20C%20and%20D%20that%20we%20pasted%20with%20data.%20like%20pasting%20drop-down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20r%20%3D%20Worksheets(%22Sheet2%22).Range(%22C%3AD%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%20%2B%201%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20r%20%3D%201%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%3AB%22%20%26amp%3B%20m).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22C%22%20%26amp%3B%20r)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Evisually%3A%3C%2FP%3E%3CP%3Ecolumn%20__A__B__C___%20...Sheet1%3C%2FP%3E%3CP%3Erow1_____w__p__k__%3C%2FP%3E%3CP%3Erow2_____w__p_____%3C%2FP%3E%3CP%3Erow3_____w__p_____%3C%2FP%3E%3CP%3E..............................................%3C%2FP%3E%3CP%3Ecolumn__A__B__C__D__E%20Sheet2%3C%2FP%3E%3CP%3Erow1____________w__p__k%3C%2FP%3E%3CP%3Erow2____________w__p__k%3C%2FP%3E%3CP%3Erow3____________w__p__k%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(single%20step%20helped.%20i%20found%20it.%20and%20i%20have%20to%20do%20this%2C%20in%20order%20to%20remove%20that%20large%20data.%20Can%20you%20help%20please%3F)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116251%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20single-step%20through%20the%20code%20by%20pressing%20F8%20repeatedly%2C%20you%20can%20view%20the%20value%20of%20variables%20such%20as%20m11%20by%20hovering%20the%20mouse%20pointer%20over%20them.%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20suddenly%20see%20a%20very%20large%20value.%20That%20would%20provide%20a%20clue...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116092%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116092%22%20slang%3D%22en-US%22%3EHmm%2C%20do%20you%20think%20it%20may%20be%20there%2C%20at%20m11%2C%20r11%20etc%3F%20Cause%20it%20counts%2C%20and%20copy%20and%20paste%20the%20cells%20that%20have%20data%2C%20for%20example%2010cells.%20so%20it%20doesnt%20have%20to%20paste%20until%201%2C000%2C000%20row.%20im%20worried%20it%20may%20be%20somewhwere%20else.%20But%20u%20know%20best.%20So%20we%20cant%20do%20anything%20about%20it%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116088%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20hard%20to%20tell.%3C%2FP%3E%0A%3CP%3EI'd%20single-step%20through%20the%20code%20and%20inspect%20the%20values%20of%20m10%2C%20r10%2C%20m11%2C%20r11%20etc.%20to%20see%20if%20one%20of%20those%20has%20an%20unexpectedly%20large%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116042%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116042%22%20slang%3D%22en-US%22%3E%3CP%3Eremoved%20reply%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2115461%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20MsgBox%20%22There%20are%20no%20data%20to%20copy!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Err.Clear%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20r%20%3D%20Worksheets(%22Sheet2%22).Range(%22C%3AD%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%20%2B%201%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20r%20%3D%201%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%3AB%22%20%26amp%3B%20m).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22C%22%20%26amp%3B%20r)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2115373%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Hey%20friend%2C%20one%20more%20question%2C%20how%20can%20i%20modify%20that%20code%2C%20so%20if%20column%20A%20%26amp%3B%20B%20are%20empty%2C%20not%20to%20show%20an%20error%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114853%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114853%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20You%20are%20simply%20the%20BEST%20my%20friend%20!%20You%20just%20saved%20my%20life.%201%20billion%20thanks.%20Much%20love%20from%20Albania%20%3CLI-EMOJI%20id%3D%22lia_red-heart%22%20title%3D%22%3Ared_heart%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114841%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20happens%20if%20columns%20C%20and%20D%20are%20empty.%20See%20if%20this%20is%20better%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20r%20%3D%20Worksheets(%22Sheet2%22).Range(%22C%3AD%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%20%2B%201%0A%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20r%20%3D%201%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%3AB%22%20%26amp%3B%20m).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22C%22%20%26amp%3B%20r)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114838%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114838%22%20slang%3D%22en-US%22%3Eit%20says%3A%3CBR%20%2F%3E%3CBR%20%2F%3ERun-time%20error%20'91'%3A%20Object%20variable%20or%20With%20block%20variable%20not%20set%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114830%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20it%20shows%20an%20error%20my%20friend%20at%20this%20line%3A%3C%2FP%3E%3CP%3Er%20%3D%20Worksheets(%22Sheet2%22).Range(%22C%3AD%22).%20.%20.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20dont%20know%2C%20can%20you%20fix%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114822%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20the%20macro%20to%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22A%3AB%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20r%20%3D%20Worksheets(%22Sheet2%22).Range(%22C%3AD%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%20%2B%201%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%3AB%22%20%26amp%3B%20m).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22C%22%20%26amp%3B%20r)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114811%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bi%20meant%20column%20A%20and%20B.%20Sorry%20if%20was%20not%20very%20clear.%20And%20Column%20C%20and%20D%20as%20well%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114800%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20AB%2C%20did%20you%20mean%20the%2028th%20column%2C%20or%20did%20you%20mean%20columns%20A%20and%20B%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114757%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114757%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20friend%2C%20actually%20it%20does%20nothing%2C%20it%20runs%20without%20error%2C%20but%20the%20data%20are%20not%20shown%20in%20CD%20in%20Sheet2.%20They%20arent%20pasted%20anywhere.%20I%20don't%20know%20where%20the%20problem%20is...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114731%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114731%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20m%20%3D%20Worksheets(%22Sheet1%22).Range(%22AB%22%20%26amp%3B%20Worksheets(%22Sheet1%22).Rows.Count).End(xlUp).Row%0A%20%20%20%20r%20%3D%20Worksheets(%22Sheet2%22).Range(%22CD%22%20%26amp%3B%20Worksheets(%22Sheet2%22).Rows.Count).End(xlUp).Row%20%2B%201%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22AB1%3AAB%22%20%26amp%3B%20m).Copy%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22CD%22%20%26amp%3B%20r)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2132190%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2132190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHmm%20i%20tried%20to%20put%20it%20on%20one%20procedure%2C%20but%20it%20didnt%20seem%20to%20work.%20could%20you%20give%20it%20a%20try%3F%20just%20adding%20for%20Sheet3%2C%20in%20column%20A%3AA%20while%20the%20search%20cell%20is%20from%20S24%20from%20the%20current%20sheet.%20Please%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2132353%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2132353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20would%20look%20like%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20If%20Not%20Intersect(Range(%22B3%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20If%20Range(%22B3%22).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Worksheets(%22Sheet2%22).Range(%22A%3AA%22).Find%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(What%3A%3DRange(%22B3%22).Value%2C%20LookIn%3A%3DxlValues%2C%20LookAt%3A%3DxlWhole)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Application.Goto%20rng%2C%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0A%20%20%20%20If%20Not%20Intersect(Range(%22S24%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20If%20Range(%22S24%22).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Worksheets(%22Sheet3%22).Range(%22A%3AA%22).Find%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(What%3A%3DRange(%22S24%22).Value%2C%20LookIn%3A%3DxlValues%2C%20LookAt%3A%3DxlWhole)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Application.Goto%20rng%2C%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI%20simply%20copied%20the%20If%20...%20End%20If%20block%2C%20and%20changed%20B3%20to%20S24%20and%20Sheet2%20to%20Sheet3.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2132439%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2132439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eah%20this%20way.%20that%20helped.%20Thank%20you%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2181379%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2181379%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20friend%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20thinking%20about%20a%20simplicity%20move.%20I%20don't%20know%20if%20it%20can%20be%20done.%3C%2FP%3E%3CP%3ESo%2C%20suppose%20we%20are%20having%202-3%20%3CSTRONG%3Eworkbooks%3C%2FSTRONG%3E%20(or%20more)%20in%20%3CSTRONG%3EDesktop%3C%2FSTRONG%3E.%20I%20want%20a%20%3CEM%3EVBA%20code%3C%2FEM%3E%2C%20that%20when%20i%20run%20it%2C%20it%20%3CSTRONG%3Ecopies%20all%20the%20worksheets%3C%2FSTRONG%3E%20of%20%3CSTRONG%3Eevery%20workbook%3C%2FSTRONG%3E%20from%20the%20desktop-folder%20and%20%3CSTRONG%3E%3CU%3Elist%2Fpaste%26nbsp%3B%3C%2FU%3Eto%20my%20active%20workbook.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECan%20this%20simplicity%20move%20be%20done%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2181405%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2181405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopySheets()%0A%20%20%20%20Dim%20strDesktop%20As%20String%0A%20%20%20%20Dim%20strFile%20As%20String%0A%20%20%20%20Dim%20wbkSource%20As%20Workbook%0A%20%20%20%20Dim%20wshSource%20As%20Worksheet%0A%20%20%20%20Dim%20wbkTarget%20As%20Workbook%0A%20%20%20%20%0A%20%20%20%20On%20Error%20GoTo%20ErrHandler%0A%20%20%20%20%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20Set%20wbkTarget%20%3D%20ActiveWorkbook%0A%20%20%20%20strDesktop%20%3D%20CreateObject(%22WScript.Shell%22).SpecialFolders(%22Desktop%22)%20%26amp%3B%20%22%5C%22%0A%20%20%20%20strFile%20%3D%20Dir(strDesktop%20%26amp%3B%20%22*.xls*%22)%0A%20%20%20%20Do%20While%20strFile%20%26lt%3B%26gt%3B%20%22%22%0A%20%20%20%20%20%20%20%20Set%20wbkSource%20%3D%20Workbooks.Open(strDesktop%20%26amp%3B%20strFile)%0A%20%20%20%20%20%20%20%20For%20Each%20wshSource%20In%20wbkSource.Worksheets%0A%20%20%20%20%20%20%20%20%20%20%20%20wshSource.Copy%20After%3A%3DwbkTarget.Worksheets(wbkTarget.Worksheets.Count)%0A%20%20%20%20%20%20%20%20Next%20wshSource%0A%20%20%20%20%20%20%20%20wbkSource.Close%20SaveChanges%3A%3DFalse%0A%20%20%20%20%20%20%20%20strFile%20%3D%20Dir%0A%20%20%20%20Loop%0A%20%20%20%20%20%20%20%20%0AExitHandler%3A%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20%0AErrHandler%3A%0A%20%20%20%20MsgBox%20Err.Description%2C%20vbExclamation%0A%20%20%20%20Resume%20ExitHandler%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2182986%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2182986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHmm%20actually%20it%20copies%20my%20current%20sheet%20in%20my%20active%20workbook%20and%20pastes%20again%20(like%20(2))%20and%20then%20closes%20my%20workbook.%20It%20doesnt%20copy%20the%20worksheets%20of%20other%20workbooks%20from%20desktop%20and%20paste%20in%20my%20workbook!%20Could%20you%20check%20it%20once%20again%20please%3F%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22aq1.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F260682iBE694E367CE1E365%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22aq1.png%22%20alt%3D%22aq1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2183095%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20%26amp%3B%20Copy%20to%20Last%20Row%2C%20Paste%20to%20another%20sheet%20to%20Last%20Row%20(FIRST%20EMPTY%20ROW%2C%20adding%2C%20not%20r%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2183095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F955848%22%20target%3D%22_blank%22%3E%40semiro1815%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHas%20the%20active%20workbook%20been%20saved%20on%20the%20desktop%20too%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hey friends. Im New in VBA. Could you help me at this?

 

So, we have 2 columns: AB with data, in Sheet1
I want to COPY until Last Row in AB Column from Sheet1
and paste it to Sheet2, column CD in FIRST EMPTY ROW (i mean by adding more data, cause CD is supposed to have previous data)

Can anyone help? It would be a huge favour for me! THANKS !!

64 Replies

@semiro1815 

Try this:

Sub CopyData()
    Dim m As Long
    Dim r As Long
    m = Worksheets("Sheet1").Range("AB" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
    r = Worksheets("Sheet2").Range("CD" & Worksheets("Sheet2").Rows.Count).End(xlUp).Row + 1
    Worksheets("Sheet1").Range("AB1:AB" & m).Copy Destination:=Worksheets("Sheet2").Range("CD" & r)
    Application.CutCopyMode = False
End Sub

@Hans Vogelaar 

My friend, actually it does nothing, it runs without error, but the data are not shown in CD in Sheet2. They arent pasted anywhere. I don't know where the problem is...

@semiro1815 

By AB, did you mean the 28th column, or did you mean columns A and B?

@Hans Vogelaar i meant column A and B. Sorry if was not very clear. And Column C and D as well

@semiro1815 

Change the macro to

Sub CopyData()
    Dim m As Long
    Dim r As Long
    m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
    Application.CutCopyMode = False
End Sub

@Hans Vogelaar  it shows an error my friend at this line:

r = Worksheets("Sheet2").Range("C:D"). . . 

 

I dont know, can you fix it?

 

it says:

Run-time error '91': Object variable or With block variable not set
best response confirmed by semiro1815 (Contributor)
Solution

@semiro1815 

That happens if columns C and D are empty. See if this is better:

Sub CopyData()
    Dim m As Long
    Dim r As Long
    m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error Resume Next
    r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    If Err Then
        r = 1
    End If
    On Error GoTo 0
    Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
    Application.CutCopyMode = False
End Sub

@Hans Vogelaar  You are simply the BEST my friend ! You just saved my life. 1 billion thanks. Much love from Albania

@Hans Vogelaar  Hey friend, one more question, how can i modify that code, so if column A & B are empty, not to show an error?

@semiro1815 

Like this:

Sub CopyData()
    Dim m As Long
    Dim r As Long
    On Error Resume Next
    m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Err Then
        MsgBox "There are no data to copy!", vbExclamation
        Err.Clear
        Exit Sub
    End If
    r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    If Err Then
        r = 1
    End If
    On Error GoTo 0
    Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
    Application.CutCopyMode = False
End Sub

removed reply

@semiro1815 

That's hard to tell.

I'd single-step through the code and inspect the values of m10, r10, m11, r11 etc. to see if one of those has an unexpectedly large value.

Hmm, do you think it may be there, at m11, r11 etc? Cause it counts, and copy and paste the cells that have data, for example 10cells. so it doesnt have to paste until 1,000,000 row. im worried it may be somewhwere else. But u know best. So we cant do anything about it?

@semiro1815 

When you single-step through the code by pressing F8 repeatedly, you can view the value of variables such as m11 by hovering the mouse pointer over them.

Perhaps you suddenly see a very large value. That would provide a clue...

@Hans Vogelaar  So we have our code here. How can we modify this that: we have a text in Sheet1 cell C1, to be pasted in Sheet2 Column E, that times how long is column C and D that we pasted with data. like pasting drop-down.

 

 

Sub CopyData()
    Dim m As Long
    Dim r As Long
    m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error Resume Next
    r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    If Err Then
        r = 1
    End If
    On Error GoTo 0
    Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
    Application.CutCopyMode = False
End Sub

 

 

@Hans Vogelaar 

visually:

column __A__B__C___ ...Sheet1

row1_____w__p__k__

row2_____w__p_____

row3_____w__p_____

..............................................

column__A__B__C__D__E Sheet2

row1____________w__p__k

row2____________w__p__k

row3____________w__p__k

 

(single step helped. i found it. and i have to do this, in order to remove that large data. Can you help please?)

@Hans Vogelaar Hmm ok, think of this:

 

We are in Sheet1 and we have data: in Column A and Column B and data in C1

what i need:

Count, Copy, Paste Column A and Column B from Sheet1  - to Sheet2 to Last Row in Column A and Column B

and

C1 from Sheet1 to be pasted to Column C Sheet2, and to be multiplied/drop-down-cell/that long, that column B has data.

 

Here is a photo:

@semiro1815 

You changed the question slightly: instead of pasting to columns C and D on Sheet2, you now want to paste to columns A and B.

Sub CopyData()
    Dim m As Long
    Dim r As Long
    On Error Resume Next
    m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Err Then
        MsgBox "No data to copy!", vbExclamation
        Exit Sub
    End If
    r = Worksheets("Sheet2").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    If Err Then
        r = 1
    End If
    On Error GoTo 0
    Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("A" & r)
    Worksheets("Sheet1").Range("C1").Copy Destination:=Worksheets("Sheet2").Range("C" & r).Resize(m)
    Application.CutCopyMode = False
End Sub