Home

modify VBA code to include dynamic columns instead of static

%3CLINGO-SUB%20id%3D%22lingo-sub-788327%22%20slang%3D%22en-US%22%3Emodify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788327%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20code%20which%20looks%20for%20the%20word%20%22unknown%22%20in%20column%20%224%22%20(of%20sheet%20%22Raw%20Data%22)%20and%20then%20copies%20the%20corresponding%20row%20of%20data%20to%20a%20different%20sheet%20(%22undiluted%22).%20However%2C%20sometimes%20this%20data%20is%20found%20in%20different%20columns%20(column%203%2C%205%2C%206%2C%20etc).%20How%20would%20you%20code%20this%20so%20that%20it%20looks%20for%20the%20header%20(%22Sample%20Type%22)%20and%20uses%20this%20column%20instead%20of%20just%20column%20%224%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E------------------------------------%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton1_Click()%3CBR%20%2F%3Ea%20%3D%20Worksheets(%22Raw%20Data%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3C%2FP%3E%3CP%3EFor%20i%20%3D%202%20To%20a%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIf%20Worksheets(%22Raw%20Data%22).Cells(i%2C%204).Value%20%3D%20%22Unknown%22%20Then%3C%2FP%3E%3CP%3EWorksheets(%22Raw%20Data%22).Rows(i).Copy%3CBR%20%2F%3EWorksheets(%22Undiluted%22).Activate%3CBR%20%2F%3Eb%20%3D%20Worksheets(%22Undiluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3CBR%20%2F%3EWorksheets(%22Undiluted%22).Cells(b%20%2B%201%2C%201).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Activate%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20If%3CBR%20%2F%3ENext%3C%2FP%3E%3CP%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Raw%20Data%22).Cells(1%2C%201).Select%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-788327%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788420%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20do%20this%20by%20a%20simple%20algorithm%3A%3C%2FP%3E%3CUL%3E%3CLI%3ESearch%20for%20the%20header%20of%20(Sample%20Type)%20in%20the%20worksheet%20using%20the%20%3CSTRONG%3ECell.Find%3C%2FSTRONG%3Emethod%20in%20VBA%3C%2FLI%3E%3CLI%3EStore%20the%20column%20number%20of%20the%20Sample%20Type%20header%20in%20a%20variable%20called%3A%26nbsp%3B%3CSTRONG%3EheaderColumn%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3EUse%20this%20variable%20in%20place%20of%20the%20number%204%20in%20the%20IF%20statement's%20logical%20test%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20code%20looks%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20test()%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20Dim%20a%20As%20Long%0A%20%20%20%20a%20%3D%20Worksheets(%22Raw%20Data%22).Cells(Rows.Count%2C%201).End(xlUp).Row%0A%20%20%20%20%0A%20%20%20%20Dim%20headerColumn%20As%20Long%0A%20%20%20%20headerColumn%20%3D%20Cells.Find(What%3A%3D%22Sample%20Type%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20For%20i%20%3D%202%20To%20a%0A%20%20%20%20%20%20%20%20If%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn).Value%20%3D%20%22Unknown%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Raw%20Data%22).Rows(i).Copy%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Undiluted%22).Activate%0A%20%20%20%20%20%20%20%20%20%20%20%20Dim%20b%20As%20Long%0A%20%20%20%20%20%20%20%20%20%20%20%20b%20%3D%20Worksheets(%22Undiluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Undiluted%22).Cells(b%20%2B%201%2C%201).Select%0A%20%20%20%20%20%20%20%20%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Raw%20Data%22).Activate%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0A%20%20%20%20%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20ThisWorkbook.Worksheets(%22Raw%20Data%22).Cells(1%2C%201).Select%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%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788658%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788658%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eoooo.%20Nice%2C%20it%20works!%20Thanks!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20question%3F%20What%20if%20I%20also%20wanted%20to%20look%20for%20%22Quality%20Control%22%20in%20addition%20to%20%22Unknown%22....so%2C%20any%20row%20that%20had%26nbsp%3B%22Quality%20Control%22%20or%20%22Unknown%22%20in%20a%20column%20with%20%22Sample%20Type%22%20as%20the%20header%20would%20get%20moved%20to%20the%20%22Undiluted%22%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788729%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENevermind....figured%20it%20out%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20(Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn).Value%20%3D%20%22Unknown%22%20Or%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn).Value%20%3D%20%22Quality%20Control%22)%20Then%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788854%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Haytham%20Amairah%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETime%20for%20another%20question%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20did%20another%20algorithm%20to%20define%20another%20column%20header.....%22Dilution%20Factor%22%20and%20changed%20your%20original%20line%20to%3A%3C%2FP%3E%3CP%3EIf%20(Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Unknown%22%20And%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn2).Value%20%3D%20%221%22)%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%2C%20this%20works%2C%20-----%26gt%3B%20if%20%22Unknown%22%20and%20%221%22%20then%20copy%20to%20%22Undiluted%22%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20I'm%20having%20a%20hard%20time%20trying%20to%20add%20in%203%20more%20conditions%20I'd%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20%22Unknown%22%20and%20%22%26gt%3B1%22%20then%20copy%20to%20%22Diluted%22%20sheet.%3C%2FP%3E%3CP%3Eif%20%22Quality%20Control%22%20and%20%221%22%20then%20copy%20to%20%22Undiluted%22%20sheet.%3C%2FP%3E%3CP%3Eif%20%22Quality%20Control%22%20and%20%22%26gt%3B1%22%20then%20copy%20to%20%22Undiluted%22%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--or%20a%20simpler%20way%20to%20look%20at%20it%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20(%22Unknown%22%20or%20%22Quality%20Control%22)%20and%20%221%22%20then%20copy%20to%20%22Undiluted%22%20Sheet%3C%2FP%3E%3CP%3Eif%20(%22Unknown%22%20or%20%22Quality%20Control%22)%20and%20%22%26gt%3B1%22%20then%20copy%20to%20%22Diluted%22%20Sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20a%20few%20things%20and%20can't%20get%20it%20to%20work%20right.%20I'm%20not%20a%20programmer...just%20a%20chemist!%20Trying%20to%20make%20my%20life%20easier.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere's%20the%20code%20where%20I%20left%20off.....which%20works%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E------------------------%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton1_Click()%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20a%20As%20Long%3C%2FP%3E%3CP%3Ea%20%3D%20Worksheets(%22Raw%20Data%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3C%2FP%3E%3CP%3EDim%20headerColumn1%20As%20Long%3CBR%20%2F%3EheaderColumn1%20%3D%20Cells.Find(What%3A%3D%22Sample%20Type%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%3CBR%20%2F%3EDim%20headerColumn2%20As%20Long%3CBR%20%2F%3EheaderColumn2%20%3D%20Cells.Find(What%3A%3D%22Dilution%20Factor%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20i%20%3D%202%20To%20a%3CBR%20%2F%3EIf%20(Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Unknown%22%20And%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn2).Value%20%3D%20%221%22)%20Then%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Rows(i).Copy%3CBR%20%2F%3EWorksheets(%22Undiluted%22).Activate%3CBR%20%2F%3EDim%20b%20As%20Long%3CBR%20%2F%3Eb%20%3D%20Worksheets(%22Undiluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3CBR%20%2F%3EWorksheets(%22Undiluted%22).Cells(b%20%2B%201%2C%201).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Activate%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ENext%3C%2FP%3E%3CP%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Raw%20Data%22).Cells(1%2C%201).Select%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790140%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Haytham%20Amairah%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHa!!%20Got%20it!%3C%2FP%3E%3CP%3EIt%20took%20awhile%2C%20but%20got%20it%20figured%20out.%20Now....need%20to%20find%20a%20way%20to%20copy%20the%20column%20headers%20(Row%201)%20from%20%22Raw%20Data%22%20to%20the%20%22Undiluted%22%20and%20%22Diluted%22%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----------------------------------------------------------------%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton1_Click()%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20a%20As%20Long%3C%2FP%3E%3CP%3Ea%20%3D%20Worksheets(%22Raw%20Data%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3C%2FP%3E%3CP%3EDim%20headerColumn1%20As%20Long%3CBR%20%2F%3EheaderColumn1%20%3D%20Cells.Find(What%3A%3D%22Sample%20Type%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%3CBR%20%2F%3EDim%20headerColumn2%20As%20Long%3CBR%20%2F%3EheaderColumn2%20%3D%20Cells.Find(What%3A%3D%22Dilution%20Factor%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20i%20%3D%202%20To%20a%3CBR%20%2F%3EIf%20((Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Unknown%22%20Or%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Quality%20Control%22)%20And%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn2).Value%20%3D%20%221%22)%20Then%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Rows(i).Copy%3CBR%20%2F%3EWorksheets(%22Undiluted%22).Activate%3CBR%20%2F%3EDim%20b%20As%20Long%3CBR%20%2F%3Eb%20%3D%20Worksheets(%22Undiluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3CBR%20%2F%3EWorksheets(%22Undiluted%22).Cells(b%20%2B%201%2C%201).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Activate%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EIf%20((Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Unknown%22%20Or%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Quality%20Control%22)%20And%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn2).Value%20%26gt%3B%201)%20Then%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Rows(i).Copy%3CBR%20%2F%3EWorksheets(%22Diluted%22).Activate%3CBR%20%2F%3EDim%20c%20As%20Long%3CBR%20%2F%3Ec%20%3D%20Worksheets(%22Diluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3CBR%20%2F%3EWorksheets(%22Diluted%22).Cells(c%20%2B%201%2C%201).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Activate%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ENext%3C%2FP%3E%3CP%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Raw%20Data%22).Cells(1%2C%201).Select%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790519%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20about%20the%20late%20reply%20as%20I%20was%20too%20busy!%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIs%20the%20problem%20solved%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EDid%20you%20need%20anything%20else%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3EIf%20not%2C%20please%20let%20me%20know%20and%20provide%20a%20sample%20of%20the%20data%20you%20work%20on%20to%20be%20able%20to%20figure%20it%20out.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3ERegards%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790586%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790586%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20getting%20back%20to%20me.%20I%20believe%20that%20I%20have%20solved%20everything%20so%20far.%20Thanks%20for%20getting%20me%20started....I%20was%20able%20to%20make%20it%20work%20from%20there.%20Thanks%20for%20the%20offer%20to%20help%20out%20in%20the%20future.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%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-790744%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Haytham%20Amairah%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAlright...I%20have%20another%20project%20for%20you!%20This%20builds%20on%20the%20previous%20code....It%20could%20be%20on%20a%20separate%20commandbutton%2C%20but%20ultimately%20I'd%20like%20to%20just%20add%20it%20to%20the%20end%20of%20the%20previous%20code.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAnd%20if%20you%20can%20get%20me%20started%20I%20can%20likely%20hack%20my%20way%20though%20the%20rest%20to%20fill%20out%20the%20whole%20idea.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBasically%2C%20I%20need%20to%20move%20columns%20from%20one%20sheet%20to%20another%20sheet%20(%22Undiluted%22%20to%20%22UndilutedPLUS%22%20and%20%22Diluted%22%20to%20%22Diluted%20Plus%22).%20The%20columns%20will%20be%20variable%2C%20so%2C%20like%20you%20showed%20before%2C%20I%20could%20used%20cells.find%20to%20define%20all%20of%20the%20columns%20that%20I'll%20need%20(probably%2010-12%20columns%20in%20all).%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CEM%3ESo%2C%20actually%2C%20building%20on%20the%20code%20before%2C%20if%20you%20could%20show%20me%20how%20to%20move%20headercolumn1%20(i.e.%20%22Sample%20type%22)%20from%20sheet%20%22Undiluted%22%20to%20an%20empty%20sheet%20%22UndilutedPLUS%22%20and%20put%20it%20in%20cell%20A2.%3C%2FEM%3E.....then%20I%20ought%20to%20be%20able%20to%20figure%20out%20how%20to%20define%20more%20columns%20and%20line%20them%20up%20in%20B2%2C%20C2%2C%20D2%2C%20etc.%20and%20also%20move%20columns%20from%26nbsp%3B%22Diluted%22%20to%20%22Diluted%20Plus.%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20code%20as%20it%20stands%20is%20below....and%20I%20also%20attached%20the%20file%20(%22Example%20with%20VBA4.xlsm%22%20which%20has%20the%20data%20and%20code).%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(I'm%20guessing%20that%20somewhere%20you'll%20have%20to%20define%20a%20new%20worksheet%20(for%20the%20%22Undiluted%22%20sheet)%20and%20then%20use%20cells.find%20and%20define%20all%20of%20the%20columns%20there%3F)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20hope%20this%20isn't%20too%20much!%20Thanks%20for%20your%20help%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EGreg%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E----------------------------------------------------------------%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton1_Click()%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20a%20As%20Long%3C%2FP%3E%3CP%3Ea%20%3D%20Worksheets(%22Raw%20Data%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3C%2FP%3E%3CP%3EDim%20headerColumn1%20As%20Long%3CBR%20%2F%3EheaderColumn1%20%3D%20Cells.Find(What%3A%3D%22Sample%20Type%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%3CBR%20%2F%3EDim%20headerColumn2%20As%20Long%3CBR%20%2F%3EheaderColumn2%20%3D%20Cells.Find(What%3A%3D%22Dilution%20Factor%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20i%20%3D%202%20To%20a%3CBR%20%2F%3EIf%20((Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Unknown%22%20Or%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Quality%20Control%22)%20And%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn2).Value%20%3D%20%221%22)%20Then%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Rows(i).Copy%3CBR%20%2F%3EWorksheets(%22Undiluted%22).Activate%3CBR%20%2F%3EDim%20b%20As%20Long%3CBR%20%2F%3Eb%20%3D%20Worksheets(%22Undiluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3CBR%20%2F%3EWorksheets(%22Undiluted%22).Cells(b%20%2B%201%2C%201).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Activate%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EIf%20((Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Unknown%22%20Or%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Quality%20Control%22)%20And%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn2).Value%20%26gt%3B%201)%20Then%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Rows(i).Copy%3CBR%20%2F%3EWorksheets(%22Diluted%22).Activate%3CBR%20%2F%3EDim%20c%20As%20Long%3CBR%20%2F%3Ec%20%3D%20Worksheets(%22Diluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3CBR%20%2F%3EWorksheets(%22Diluted%22).Cells(c%20%2B%201%2C%201).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3EWorksheets(%22Raw%20Data%22).Activate%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Raw%20Data%22).Range(%22A1%3ACZ1%22).Copy%20Destination%3A%3DSheets(%22Undiluted%22).Range(%22A1%3ACZ1%22)%3CBR%20%2F%3ESheets(%22Raw%20Data%22).Range(%22A1%3ACZ1%22).Copy%20Destination%3A%3DSheets(%22Diluted%22).Range(%22A1%3ACZ1%22)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ENext%3C%2FP%3E%3CP%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Raw%20Data%22).Cells(1%2C%201).Select%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-792318%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792318%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20understand%2C%20you%20want%20to%20move%20specific%20entire%20columns%20to%20other%20sheets%2C%20not%20some%20rows%20based%20on%20a%20condition%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793032%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793032%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20just%20specific%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%20one%20of%20this%20automation%20is%20taking%20the%20raw%20data%20and%20sorting%20it%20by%20rows%20into%20two%20sheets%20based%20on%20conditions%20(0%20or%20%26gt%3B1).....and%20that%20code%20works%20great.%20That%20also%20gets%20rid%20of%20rows%20that%20I%20don't%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%20two%20cleans%20up%20the%20data%20even%20more%20by%20getting%20rid%20of%20columns%20that%20I%20don't%20need.....by%20copying%20specific%20columns%20to%20a%20fresh%20sheet.%20I'll%20have%20normal%20excel%20formulas%20to%20the%20right%20of%20that%20data....I%20don't%20think%20VBA%20is%20necessary%20for%20that.%20So%2C%20in%20this%20case%2C%26nbsp%3Bthere%20are%20no%20conditions%20that%20I%20need%20to%20be%20worried%20about%20(other%20than%20the%20name%20of%20the%20column%20header).%20I%20just%20need%20to%20move%20specific%20columns%20to%20new%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793397%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20code%20to%20move%20the%20needed%20columns%20from%20Undiluted%20to%20UndilutedPLUS.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3ESub%20test2()%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%200%0A%20%20%20%20%0A%20%20%20%20Sheets(%22Undiluted%22).Activate%0A%20%20%20%20Dim%20columnNamesArray()%20As%20Variant%0A%20%20%20%20columnNamesArray%20%3D%20Array(%22Analyte%20Peak%20Name%22%2C%20%22Sample%20Type%22%2C%20%22Rack%20Type%22%2C%20%22Vial%20Position%22)%0A%20%20%20%20%0A%20%20%20%20Dim%20columnName%20As%20Variant%0A%20%20%20%20For%20Each%20columnName%20In%20columnNamesArray%0A%20%20%20%20%0A%20%20%20%20Sheets(%22Undiluted%22).Activate%0A%20%20%20%20%0A%20%20%20%20Dim%20columnNumber%20As%20Integer%0A%20%20%20%20columnNumber%20%3D%20Cells.Find(What%3A%3DcolumnName%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20Cells(1%2C%20columnNumber).EntireColumn.Copy%0A%20%20%20%20%0A%20%20%20%20Sheets(%22UndilutedPLUS%22).Activate%0A%20%20%20%20Range(%22A1%22).Activate%0A%20%20%20%20ActiveCell.Offset(0%2C%20i).Select%0A%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20Next%20columnName%0A%0ARange(%22A1%22).Activate%0AApplication.CutCopyMode%20%3D%20False%0AApplication.ScreenUpdating%20%3D%20True%0AApplication.Calculation%20%3D%20xlCalculationAutomatic%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20you%20have%20to%20define%20the%20needed%20columns%20you%20want%20to%20move%20in%20the%20code.%3C%2FP%3E%3CP%3EYou%20can%20insert%20them%20into%20the%20%3CSTRONG%3EcolumnNamesArray%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793709%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793709%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20code.%20I%20like%20the%20idea%20of%20creating%20an%20array%20so%20I%20don't%20have%20to%20define%20each%20column%20individually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERight%20now%20there%20is%20a%20Runtime%20Error%201004%20(Activate%20method%20of%20Range%20Class%20failed)%20at%20the%20line%20that%20says%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange(%22A1%22).activate%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20added%20this%20code%20as%20a%20second%20command%20button.%20The%20first%20button%20does%20the%20first%20part%20of%20code%20(and%20works%20fine).%20The%20second%20button%20activates%20this%20new%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793834%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThe%20error%20is%20caused%20by%20some%20ambiguity%20because%20the%20code%20was%20run%20using%20a%20button%20embedded%20on%20the%20worksheet.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ETo%20remove%20the%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Eambiguity%2C%20%3C%2FSPAN%3E%3C%2FSPAN%3Efix%20the%20code%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3EPrivate%20Sub%20CommandButton1_Click()%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%200%0A%20%20%20%20%0A%20%20%20%20Sheets(%22Undiluted%22).Activate%0A%20%20%20%20Dim%20columnNamesArray()%20As%20Variant%0A%20%20%20%20columnNamesArray%20%3D%20Array(%22Analyte%20Peak%20Name%22%2C%20%22Sample%20Type%22%2C%20%22Rack%20Type%22%2C%20%22Vial%20Position%22)%0A%20%20%20%20%0A%20%20%20%20Dim%20columnName%20As%20Variant%0A%20%20%20%20For%20Each%20columnName%20In%20columnNamesArray%0A%20%20%20%20%0A%20%20%20%20Sheets(%22Undiluted%22).Activate%0A%20%20%20%20%0A%20%20%20%20Dim%20columnNumber%20As%20Integer%0A%20%20%20%20columnNumber%20%3D%20Cells.Find(What%3A%3DcolumnName%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20Cells(1%2C%20columnNumber).EntireColumn.Copy%0A%20%20%20%20%0A%20%20%20%20Sheets(%22UndilutedPLUS%22).Activate%0A%20%20%20%20Sheets(%22UndilutedPLUS%22).Range(%22A1%22).Select%0A%20%20%20%20ActiveCell.Offset(0%2C%20i).Select%0A%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20Next%20columnName%0A%0AActiveSheet.Range(%22A1%22).Activate%0AApplication.CutCopyMode%20%3D%20False%0AApplication.ScreenUpdating%20%3D%20True%0AApplication.Calculation%20%3D%20xlCalculationAutomatic%0A%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793990%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes...that%20mostly%20fixed%20the%20problem%2C%20thanks.%20I%20added%20in%20all%20of%20the%20exact%20headers%20that%20I%20want%20and%20it%20copies%20them%20just%20fine.%20However%2C%20it's%20picking%20data%20from%20Sheet1%20(i.e.%20%22Raw%20Data%22)%20and%20moving%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20to%20more%20specifically%20specify%20which%20sheets%20to%20copy%20from%20by%20changing%20the%20line%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ESheets(%22Undiluted%22).%3C%2FEM%3ECells(1%2C%20columnNumber).EntireColumn.Copy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20it%20moves%20everything%20correctly!%20I'm%20not%20sure%20why%20it%20was%20still%20pulling%20from%20%22Raw%20Data%22%20when%20%22Undiluted%22%20was%20activated%2C%20but%20that%20fixes%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sure%20I'll%20have%20more%20questions....but%20for%20now%2C%20I'm%20good.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%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-794085%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I'm%20having%20a%20hard%20time%20now%20figuring%20out%20how%20to%20add%20a%20second%20loop(%3F)%20to%20do%20the%20same%20thing%20for%20the%20%22Diluted%22%20and%20%22DilutedPLUS%22%20sheets.%20I%20guess%20you'd%20have%20to%20add%20%22j%20as%20an%20integer%22%20at%20the%20beginning%2C%20but%20then%20I%20get%20lost%20after%20that%2C%20lol.%20I%20can't%20figure%20out%20where%20to%20trigger%20the%20second%20loop%20with%20the%20new%20sheet%20names.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(current%20code%20below)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E----------------------------------------------------------------------%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton2_Click()%3C%2FP%3E%3CP%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20i%20As%20Integer%3CBR%20%2F%3Ei%20%3D%200%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Undiluted%22).Activate%3CBR%20%2F%3EDim%20columnNamesArray()%20As%20Variant%3CBR%20%2F%3EcolumnNamesArray%20%3D%20Array(%22Sample%20Type%22%2C%20%22Sample%20Name%22%2C%20%22Acquisition%20Date%22%2C%20%22File%20Name%22%2C%20%22Dilution%20Factor%22%2C%20%22Analyte%20Peak%20Name%22%2C%20%22Analyte%20Concentration%22%2C%20%22Calculated%20Concentration%20(%22)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20columnName%20As%20Variant%3CBR%20%2F%3EFor%20Each%20columnName%20In%20columnNamesArray%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Undiluted%22).Activate%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20columnNumber%20As%20Integer%3CBR%20%2F%3EcolumnNumber%20%3D%20Cells.Find(What%3A%3DcolumnName%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%3CBR%20%2F%3EMatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Undiluted%22).Cells(1%2C%20columnNumber).EntireColumn.Copy%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22UndilutedPLUS%22).Activate%3CBR%20%2F%3ESheets(%22UndilutedPLUS%22).Range(%22A1%22).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%20i).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3Ei%20%3D%20i%20%2B%201%3CBR%20%2F%3ENext%20columnName%3C%2FP%3E%3CP%3EActiveSheet.Range(%22A1%22).Activate%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlCalculationAutomatic%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794088%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20that%20with%20the%20newest%20releases%20of%20Office%20365%20most%20of%20the%20things%20described%20here%20can%20be%20done%20easily%20with%20worksheet%20formulas.%26nbsp%3B%20The%20points%20to%20observe%20are%20that%20all%20raw%20data%20should%20be%20entered%20into%20Excel%20Tables%20so%20that%20the%20field%20to%20be%20searched%20for%20the%20value%20%22Unknown%22%20can%20be%20identified%20by%20name%20rather%20than%20a%20static%20numeric%20index.%26nbsp%3B%20Given%20that%20tables%20adjust%20dynamically%20to%20the%20data%2C%20no%20counts%20are%20required%20to%20determine%20the%20length%20of%20the%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20key%20function%20is%20FILTER%20which%20will%20return%20all%20the%20records%20that%20match%20the%20criterion%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20FILTER(%20Table1%2C%20Table1%5Bfield%5D%3D%22Unknown%22%7D%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20advantage%20of%20names%20is%20as%20great%20when%20it%20comes%20to%20VBA.%26nbsp%3B%20A%20named%20field%20can%20be%20block-read%20into%20a%20variant%20array%20and%20then%20the%20looping%20is%20in%20memory%20rather%20than%20requiring%20continual%20exchanges%20between%20the%20VBA%20environment%20and%20the%20worksheet.%26nbsp%3B%20If%20the%20column%20changes%2C%20the%20Name%20moves%20with%20it%2C%20so%20the%20code%20is%20more%20robust.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794377%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20easy%2C%20just%20copy%20the%20code%20in%20a%20new%20button%20in%20the%20Diluted%20sheet%2C%20and%20change%20the%20sheet%20names%20in%20it.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20CommandButton1_Click()%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%200%0A%20%20%20%20%0A%20%20%20%20Sheets(%22Diluted%22).Activate%0A%20%20%20%20Dim%20columnNamesArray()%20As%20Variant%0A%20%20%20%20columnNamesArray%20%3D%20Array(%22Sample%20Type%22%2C%20%22Rack%20Type%22)%0A%20%20%20%20%0A%20%20%20%20Dim%20columnName%20As%20Variant%0A%20%20%20%20For%20Each%20columnName%20In%20columnNamesArray%0A%20%20%20%20%20%0A%20%20%20%20Dim%20columnNumber%20As%20Integer%0A%20%20%20%20columnNumber%20%3D%20Cells.Find(What%3A%3DcolumnName%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20Cells(1%2C%20columnNumber).EntireColumn.Copy%0A%20%20%20%20%0A%20%20%20%20Sheets(%22DilutedPLUS%22).Activate%0A%20%20%20%20Sheets(%22DilutedPLUS%22).Range(%22A1%22).Select%0A%20%20%20%20ActiveCell.Offset(0%2C%20i).Select%0A%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20Next%20columnName%0A%0AActiveSheet.Range(%22A1%22).Activate%0AApplication.CutCopyMode%20%3D%20False%0AApplication.ScreenUpdating%20%3D%20True%0AApplication.Calculation%20%3D%20xlCalculationAutomatic%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795165%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795165%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20yes%2C%20lol.%20I%20was%20thinking%20the%20same%20thing%20after%20I%20left%20work%20yesterday.%20I'm%20sure%20there's%20a%20way%20to%20combine%20it%20together%20into%20some%20sort%20of%20loop%2C%20but%20this%20is%20easier.%20I%20also%20created%20a%204th%20command%20button%20which%20sequentially%20runs%20each%20of%20the%20first%20three%20scripts!%20In%20the%20end%2C%20I%20think%20it'll%20be%20nice%20to%20have%20the%20option%20to%20run%20things%20separately....or%20just%20run%20everything.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795541%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20opinion%2C%20an%20embedded%20button%20on%20each%20worksheet%20is%20not%20a%20good%20idea!%3C%2FP%3E%3CP%3EYou%20can%20run%20the%20code%20from%20the%20%3CA%20href%3D%22https%3A%2F%2Fwww.excel-easy.com%2Fvba%2Fexamples%2Fadd-a-macro-to-the-toolbar.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EQuick%20Access%20Toolbar%3C%2FA%3Eor%20from%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DdmdolFcS-fI%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ea%20new%20ribbon%3C%2FA%3Etab.%3C%2FP%3E%3CP%3EOr%20you%20can%20place%20the%20buttons%20in%20one%20worksheet%20as%20follows%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20903px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126224iEED46F75A9CD8936%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Run.png%22%20title%3D%22Run.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20grouping%20all%20the%20process%20code%20in%20one%20%3CSTRONG%3ESub%20%3C%2FSTRONG%3Eis%20also%20not%20a%20good%20idea!%3C%2FP%3E%3CP%3EYou%20can%20instead%20separate%20them%20in%20three%20Subs%20so%20that%20each%20Sub%20handle%20one%20part%20of%20the%20automation.%3C%2FP%3E%3CP%3EThen%20move%20all%20%3CSTRONG%3ESubs%3C%2FSTRONG%3Ein%20a%20new%20generic%20module.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20create%20a%20sub%20called%20(Main)%20and%20call%20the%20other%20subs%20in%20it.%3C%2FP%3E%3CP%3EThis%20makes%20the%20code%20more%20readable%20and%20maintainable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20full%20%3CSTRONG%3EModule1%3C%2FSTRONG%3Ecode%20including%20all%20Subs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3ESub%20Main()%0A%20%20%20%20Call%20FilterData%0A%20%20%20%20Call%20MoveToUndilutedPLUS%0A%20%20%20%20Call%20MoveToDilutedPLUS%0AEnd%20Sub%0A%0ASub%20FilterData()%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20%0A%20%20%20%20Worksheets(%22Raw%20Data%22).Activate%0A%20%20%20%20%0A%20%20%20%20Dim%20a%20As%20Long%0A%20%20%20%20a%20%3D%20Worksheets(%22Raw%20Data%22).Cells(Rows.Count%2C%201).End(xlUp).Row%0A%20%20%20%20%0A%20%20%20%20Dim%20headerColumn1%20As%20Long%0A%20%20%20%20%20%20%20%20headerColumn1%20%3D%20Cells.Find(What%3A%3D%22Sample%20Type%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%0A%20%20%20%20Dim%20headerColumn2%20As%20Long%0A%20%20%20%20%20%20%20%20headerColumn2%20%3D%20Cells.Find(What%3A%3D%22Dilution%20Factor%22%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20For%20i%20%3D%202%20To%20a%0A%20%20%20%20%20%20%20%20If%20((Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Unknown%22%20Or%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Quality%20Control%22)%20And%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn2).Value%20%3D%20%221%22)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Raw%20Data%22).Rows(i).Copy%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Undiluted%22).Activate%0A%20%20%20%20%20%20%20%20%20%20%20%20Dim%20b%20As%20Long%0A%20%20%20%20%20%20%20%20%20%20%20%20b%20%3D%20Worksheets(%22Undiluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Undiluted%22).Cells(b%20%2B%201%2C%201).Select%0A%20%20%20%20%20%20%20%20%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Raw%20Data%22).Activate%0A%20%20%20%20%20%20%20%20End%20If%0A%0A%20%20%20%20%20%20%20%20If%20((Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Unknown%22%20Or%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn1).Value%20%3D%20%22Quality%20Control%22)%20And%20Worksheets(%22Raw%20Data%22).Cells(i%2C%20headerColumn2).Value%20%26gt%3B%201)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Raw%20Data%22).Rows(i).Copy%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Diluted%22).Activate%0A%20%20%20%20%20%20%20%20%20%20%20%20Dim%20c%20As%20Long%0A%20%20%20%20%20%20%20%20%20%20%20%20c%20%3D%20Worksheets(%22Diluted%22).Cells(Rows.Count%2C%201).End(xlUp).Row%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Diluted%22).Cells(c%20%2B%201%2C%201).Select%0A%20%20%20%20%20%20%20%20%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Raw%20Data%22).Activate%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20Sheets(%22Raw%20Data%22).Range(%22A1%3ACZ1%22).Copy%20Destination%3A%3DSheets(%22Undiluted%22).Range(%22A1%3ACZ1%22)%0A%20%20%20%20Sheets(%22Raw%20Data%22).Range(%22A1%3ACZ1%22).Copy%20Destination%3A%3DSheets(%22Diluted%22).Range(%22A1%3ACZ1%22)%0A%20%20%20%20%0A%20%20%20%20Next%0A%20%20%20%20%0A%20%20%20%20Worksheets(%22Raw%20Data%22).Activate%0A%20%20%20%20Worksheets(%22Raw%20Data%22).Range(%22A1%22).Select%0A%20%20%20%20%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20Application.Calculation%20%3D%20xlCalculationAutomatic%0A%20%20%20%20On%20Error%20GoTo%200%0AEnd%20Sub%0A%0ASub%20MoveToUndilutedPLUS()%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%200%0A%20%20%20%20%0A%20%20%20%20Sheets(%22Undiluted%22).Activate%0A%20%20%20%20Dim%20columnNamesArray()%20As%20Variant%0A%20%20%20%20columnNamesArray%20%3D%20Array(%22Analyte%20Peak%20Name%22%2C%20%22Sample%20Type%22%2C%20%22Rack%20Type%22%2C%20%22Vial%20Position%22)%0A%20%20%20%20%0A%20%20%20%20Dim%20columnName%20As%20Variant%0A%20%20%20%20For%20Each%20columnName%20In%20columnNamesArray%0A%20%20%20%20Sheets(%22Undiluted%22).Activate%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20Dim%20columnNumber%20As%20Integer%0A%20%20%20%20columnNumber%20%3D%20Cells.Find(What%3A%3DcolumnName%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20Cells(1%2C%20columnNumber).EntireColumn.Copy%0A%20%20%20%20%0A%20%20%20%20Sheets(%22UndilutedPLUS%22).Activate%0A%20%20%20%20Sheets(%22UndilutedPLUS%22).Range(%22A1%22).Select%0A%20%20%20%20ActiveCell.Offset(0%2C%20i).Select%0A%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20Next%20columnName%0A%20%20%20%20%0A%20%20%20%20Sheets(%22UndilutedPLUS%22).Range(%22A1%22).Select%0A%20%20%20%20%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20Application.Calculation%20%3D%20xlCalculationAutomatic%0A%20%20%20%20On%20Error%20GoTo%200%0AEnd%20Sub%0A%0ASub%20MoveToDilutedPLUS()%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%200%0A%20%20%20%20%0A%20%20%20%20Sheets(%22Diluted%22).Activate%0A%20%20%20%20Dim%20columnNamesArray()%20As%20Variant%0A%20%20%20%20columnNamesArray%20%3D%20Array(%22Sample%20Type%22%2C%20%22Rack%20Type%22)%0A%20%20%20%20%0A%20%20%20%20Dim%20columnName%20As%20Variant%0A%20%20%20%20For%20Each%20columnName%20In%20columnNamesArray%0A%20%20%20%20Sheets(%22Diluted%22).Activate%0A%20%20%20%20%20%0A%20%20%20%20Dim%20columnNumber%20As%20Integer%0A%20%20%20%20columnNumber%20%3D%20Cells.Find(What%3A%3DcolumnName%2C%20After%3A%3DRange(%22A1%22)%2C%20LookIn%3A%3DxlFormulas%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse).Column%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20Cells(1%2C%20columnNumber).EntireColumn.Copy%0A%20%20%20%20%0A%20%20%20%20Sheets(%22DilutedPLUS%22).Activate%0A%20%20%20%20Sheets(%22DilutedPLUS%22).Range(%22A1%22).Select%0A%20%20%20%20ActiveCell.Offset(0%2C%20i).Select%0A%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20Next%20columnName%0A%20%20%20%20%0A%20%20%20%20Sheets(%22DilutedPLUS%22).Range(%22A1%22).Select%0A%20%20%20%20%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20Application.Calculation%20%3D%20xlCalculationAutomatic%0A%20%20%20%20On%20Error%20GoTo%200%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20also%20makes%20the%20code%20reusable%20as%20the%20embedded%20buttons%20don't%20duplicate%20the%20same%20code%2C%20it%20just%20reuses%20it%20or%20recalls%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20embedded%20buttons%20code%20behind%20the%20Run%20sheet%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20883px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126225iDAFC7FF838303E7B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Code%20Behind.png%22%20title%3D%22Code%20Behind.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20all%20that%20in%20the%20attached%20worksheet.%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795567%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795567%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20advice.%20Good%20to%20know%20about%20not%20grouping%20everything%20into%20one%20sub.%20What%20you%20described%20I%20think%20is%20basically%20where%20I'm%20at%20now.%20I%20added%20a%204th%20Sub%20(CommandButton)%20which%20sequentially%20runs%20each%20of%20the%203%20subs%20so%20far.%20I%20can%20just%20modify%20things%20in%20the%20future%20as%20I%20add%20more.%20I%20can%20see%20how%20your%20code%20is%20certainly%20more%20elegant.%20I'll%20look%20into%20that%20file.%20Thank%20you%20so%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E----------------------------------%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton4_Click()%3CBR%20%2F%3ECommandButton1_Click%3CBR%20%2F%3ECommandButton2_Click%3CBR%20%2F%3ECommandButton3_Click%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E------------------------------------%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126226i9DF25CAD0817C8C7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798109%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Haytham%20Amairah%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EOk...I've%20been%20adding%20to%20the%20code%20that%20you%20cleaned%20up.%20With%20the%20new%20code%20I%20haven't%20created%20buttons...I've%20just%20added%20more%20subs%20to%20Module1%20and%20called%20them%20to%20Main%20like%20you%20did.%20For%20most%20everything%20new%20I%20simply%20recorded%20a%20macro%20and%20copied%20that%20code%20to%20Module1.%20The%20columns%20are%20static%20at%20this%20point%2C%20which%20makes%20it%20easier.%20I%20usually%20did%20some%20sort%20of%20modification%20to%20make%20it%20work%20(added%20%22%26amp%3B%20LastRow%22%20to%20autofill%20down%20to%20the%20last%20entry%20in%20the%20column%2C%20for%20example).%20I%20tested%20the%20new%20code%20by%20hitting%20F8%20through%20each%20line%20to%20make%20sure%20it%20does%20what%20it%20should.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20one%20crucial%2C%20and%20(maybe)%20tricky%20bit%20of%20code%20to%20write...and%20I%20have%20no%20idea%20how%20to%20do%20it...or%20which%20would%20be%20easiest.%20Big%20picture%3A%20I%20run%20biological%20samples%20at%20work.%20Initially%20I%20run%20the%20samples%20%22undiluted%22%20and%20see%20if%20the%20data%20falls%20within%20a%20certain%20range.%20If%20not%2C%20then%20the%20samples%20are%20run%20again%20%22diluted%22.%20Sometimes%20they%20still%20don't%20fall%20within%20the%20range%20and%20are%20diluted%20again.%20At%20some%20point%2C%20regardless%20of%20being%20within%20a%20range%2C%20the%20data%20is%20reported.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20%22UndilutedPLUS%22%20and%20%22DilutedPLUS%22%20tabs%20I've%20added%20many%20columns.%20The%20important%20ones%20to%20know%3A%26nbsp%3B%3C%2FP%3E%3CP%3EColumnA%3A%20this%20is%20a%20helper%20column....it%20gives%20the%20sample%20a%20unique%20ID%20(i.e%20for%20VLOOKUP)%3C%2FP%3E%3CP%3EColumnM%3A%20this%20says%20whether%20the%20sample%20is%20good%20(1)%20or%20not%20(0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20let%20me%20know%20if%20this%20is%20possible%3A%3C%2FP%3E%3CP%3E1)%20Copy%20Column%20A%20(helper%20column)%20from%20the%20%22UndilutedPLUS%22%20sheet%20to%20the%20%22Final%20Data%22%20sheet.%20This%20gives%20a%20definitive%20list%20of%20all%20samples%20(since%20each%20sample%20is%20run%20undiluted).%20Now%20we%20need%20data%20from%20the%20%22UndilutedPLUS%22%20or%20%22DilutedPLUS%22%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Use%20VLOOKUP%3F%20(or%20anything%20else%3F)%20to%20look%20for%20the%20corresponding%20row%20of%20data%20in%20%22UndilutedPLUS%22%20(using%20the%20entry%20in%20the%20helper%20column%20(ColumnA))%20and%3C%2FP%3E%3CP%3Eif%20Column%20M%3D1%20then%20copy%20the%20data%20to%20%22Final%20Data.%22%20(and%20you're%20done)%3C%2FP%3E%3CP%3Eif%20Column%20M%3D0%20then%20next%3C%2FP%3E%3CP%3E3)%20Look%20for%20the%20corresponding%20row%20of%20data%20in%20the%20%22DilutedPLUS%22%20sheet%20and%3C%2FP%3E%3CP%3Eif%20Column%20M%3D1%20then%20copy%20the%20row%20of%20data%20to%20%22Final%20Data%22%20(and%20you're%20done)%3C%2FP%3E%3CP%3Eif%20Column%20M%3D0%20then%20next%3C%2FP%3E%3CP%3E4)%20Look%20for%20the%20corresponding%20row%20of%20data%20in%20the%20%22DilutedPLUS%22%20sheet%20and%3C%2FP%3E%3CP%3Eif%20Column%20M%3D0%20then%20copy%20the%20row%20of%20data%20to%20%22Final%20Data%22%20(and%20you're%20done)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20way%20to%20do%20this%20using%20VLOOKUP%20for%20each%20individual%20cell%20in%20the%20spreadsheet.%20It%20works%2C%20but%20is%20clunky.%20It%20uses%203%20nested%20%22if%22%20statements%20with%20VLOOKUPs%20to%20go%20through%20the%20statements%20above%20for%20each%20cell.%20Then%20it%20gets%20modified%20for%20each%20column%20and%20then%20filled%20down%20to%20the%20last%20row%20in%20the%20%22Final%20Data%22%20sheet.%20It's%20doable%2C%20but%20not%20pretty.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20isn't%20too%20tough.%20I%20feel%20that%20doing%20complicated%20things%20with%20formulas%20becomes%20unnecessarily%20difficult%20whereas%20a%20good%20script%20can%20do%20it%20more%20easily.%20I'm%20just%20giving%20you%20extra%20detail%20in%20case%20you%20need%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20have%20a%20small%20data%20set%20in%20the%20attached%20document.%3C%2FP%3E%3CP%3E*Sample%20A1%20should%20get%20moved%20first%20thing%20and%20data%20reported%20because%20the%20value%20in%20%22Undiluted%20Plus%22%20in%20column%20M%3D1.%3C%2FP%3E%3CP%3E*Sample%20B1%2C%20shows%20Column%20M%3D0%20in%20the%20%22UndilutedPLUS%22%20sheet%20so%20you'd%20go%20the%20the%20%22DilutedPLUS%22%20sheet%20where%20one%20dilution%20gave%20Column%20M%3D0%20but%20the%20second%20one%20gave%2C%20Column%20M%3D1...so%20that%20data%20will%20be%20reported.%26nbsp%3B%3C%2FP%3E%3CP%3E*Sample%20C1%2C%20similar%20to%20B1%20but%20both%20dilutions%20gave%20Column%20M%3D0%2C%20so%20the%20second%20one%20will%20be%20reported.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20last%20tab%20in%20the%20spreadsheet%20is%20%22Final%20Data%20Example%22%20which%20shows%20what%20the%20final%20result%20%3CEM%3Eshould%3C%2FEM%3Elook%20like.%20Good%20luck%20if%20you%20feel%20like%20taking%20this%20on.%20I'll%20be%20on%20vacation%20next%20will%20but%20will%20bring%20my%20computer%20in%20case%20you%20have%20a%20chance%20to%20work%20on%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%20and%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%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-798436%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20still%20somewhat%20puzzled%20as%20to%20the%20reason%20you%20turn%20so%20readily%20to%20VBA%20for%20the%20analysis.%26nbsp%3B%20Excel%20has%20a%20huge%20amount%20of%20built-in%20functionality%20to%20support%20filtering%2C%20both%20as%20manual%20operations%20and%20automatic%2C%20using%20formulas%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20ran%20the%20code%20in%20%22Example%20with%20VBA4.xlsm%22%20this%20morning%20and%20it%20took%20%3CU%3E%3CEM%3E%3CFONT%20color%3D%22%230000ff%22%3E1min20sec%3C%2FFONT%3E%3C%2FEM%3E%3C%2FU%3Eto%20produce%20630%20rows%20for%20'undiluted'%20and%20a%20further%2030%20on%20the%20'diluted'%20sheet.%26nbsp%3B%20I%20am%20sure%20there%20is%20plenty%20of%20room%20for%20speed%20optimisation%20of%20the%20code%20but%20I%20went%20on%20to%20compare%20that%20time%20with%20that%20of%20a%20formula-based%20approach.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20happened%20to%20be%20using%20a%20machine%20with%20Office%20365%20loaded%20so%2C%20having%20converted%20the%20source%20data%20to%20a%20table%2C%20I%20used%20a%20modern%20dynamic%20array%20formula%20to%20extract%20the%20required%20records.%26nbsp%3B%20The%20formulas%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20FILTER(%20RawData%2C%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E((RawData%5BSample%20Type%5D%3D%22Unknown%22)%20%2B%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E(RawData%5BSample%20Type%5D%3D%22Quality%20Control%22))%20*%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E(RawData%5BDilution%20Factor%5D%3D1)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eand%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20FILTER(%20RawData%2C%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E((RawData%5BSample%20Type%5D%3D%22Unknown%22)%20%2B%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E(RawData%5BSample%20Type%5D%3D%22Quality%20Control%22))%20*%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E(RawData%5BDilution%20Factor%5D%26gt%3B1)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eeach%20took%20%3CU%3E%3CEM%3E%3CFONT%20color%3D%22%230000ff%22%3E0.1sec%3C%2FFONT%3E%3C%2FEM%3E%3C%2FU%3Eto%20run%20and%20gave%20identical%20output.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3Ep.s.%20Other%20options%20would%20include%20running%20the%20Advanced%20Filter%20from%20code%20(any%20version%20of%20Excel)%20or%20to%20use%20Power%20Query%20(Office%202010%20with%20add-in%2C%20native%202016%20and%20on).%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EI%20have%20now%20attached%20a%20copy%20of%20the%20file%20with%20your%20early%20VBA%2C%20with%20Power%20Query%20and%20with%20Dynamic%20Array%20formulas.%26nbsp%3B%20The%20last%20only%20works%20with%20the%20most%20up-to-date%20versions%20of%20Office365.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799058%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EIt%20seems%20to%20be%20a%20rough%20process%20and%20needs%20a%20lot%20of%20work!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EMaybe%20I%20don't%20have%20enough%20time%20to%20look%20at%20it!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI'm%20sorry!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EBut%20apparently%2C%20the%20solution%20through%20the%20code%20would%20not%20be%20better%2C%20because%20in%20the%20code%20we%20would%20consider%20using%20the%20same%20lookup%20functions%20in%20the%20worksheet%20as%20in%20the%20example%20below%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSTRONG%3E%3CSPAN%3EApplication.WorksheetFunction.VLookup%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3E%3CSPAN%20class%3D%22%22%3ESo%20I%20advise%20you%20to%20consider%20other%20solutions%20suggested%20by%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3E%3CSPAN%20class%3D%22%22%3EOr%20stick%20with%20the%20analysis%20you've%20already%20done%20using%20VLOOKUP.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3ERegards%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-802964%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Peter.%20I'll%20start%20to%20look%20into%20this.%20In%20the%20end%2C%20the%20whole%20process%20is%3A%3C%2FP%3E%3CP%3E-%20dividing%20Unknown%20and%20Quality%20Control%20samples%20between%20diluted%20and%20undiluted%20groups%20(Then%20get%20rid%20of%20columns%20that%20I%20don't%20need)%3C%2FP%3E%3CP%3E-%20Adding%20some%20columns%20to%20each%20of%20these%20sets%20to%20do%20some%20math%20do%20determine%20if%20the%20samples%20are%20in%20a%20range%3C%2FP%3E%3CP%3E-%20Then%20a%20VLOOKUP%20to%20compare%20samples%20in%20the%20diluted%20and%20undiluted%20groups%20and%20choose%20the%20proper%20one%20for%20final%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20has%20been%20a%20very%20manual%20process%20not%20just%20for%20me%2C%20but%20my%20teammates.%20I%20wanted%20to%20make%20a%20way%20for%20people%20to%20simply%20add%20in%20all%20the%20raw%20data%20into%20the%20first%20tab%20of%20a%20spreadsheet%2C%20press%20a%20button%2C%20and%20get%20final%20data.%20So%2C%20that's%20why%20I%20thought%20VBA%20would%20be%20a%20good%20way%20to%20automate%20the%20whole%20process.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20i%20can%20find%20a%20way%20to%20do%20the%20filtering%20(as%20you%20suggest)%20and%20replace%20the%20first%20part%20of%20the%20code%2C%20then%20that%20sounds%20like%20a%20good%20idea.%20As%20you%20mentioned.....it%20can%20be%20rather%20slow.%20And%2C%20its%20possible%20that%201000's%20of%20rows%20of%20data%20could%20be%20in%20a%20set.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%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-809506%22%20slang%3D%22en-US%22%3ERe%3A%20modify%20VBA%20code%20to%20include%20dynamic%20columns%20instead%20of%20static%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20and%20any%20colleagues%20that%20need%20to%20manipulate%20the%20results%20have%20Office%20365%20and%20can%20you%20set%20it%20to%20monthly%20updates%3F%26nbsp%3B%20The%20array%20methods%20I%20have%20used%20have%20only%20come%20out%20in%20the%20latest%20releases%20of%20Excel%20and%2C%20though%20it%20would%20be%20possible%20to%20transfer%20the%20data%20using%20INDEX%20it%20would%20lack%20elegance.%26nbsp%3B%3C%2FP%3E%3CP%3ERemoving%20columns%20could%20be%20achieved%20by%20being%20more%20selective%20as%20to%20the%20columns%20brought%20through%20in%20the%20first%20place%2C%20even%20down%20to%20the%20level%20of%20bring%20key%20columns%20across%20individually%20within%20the%20downstream%20calculation.%3C%2FP%3E%3C%2FLINGO-BODY%3E
gms4b
Contributor

I have the following code which looks for the word "unknown" in column "4" (of sheet "Raw Data") and then copies the corresponding row of data to a different sheet ("undiluted"). However, sometimes this data is found in different columns (column 3, 5, 6, etc). How would you code this so that it looks for the header ("Sample Type") and uses this column instead of just column "4"?

 

Thanks,


Greg

 

------------------------------------

Private Sub CommandButton1_Click()
a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a


If Worksheets("Raw Data").Cells(i, 4).Value = "Unknown" Then

Worksheets("Raw Data").Rows(i).Copy
Worksheets("Undiluted").Activate
b = Worksheets("Undiluted").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Undiluted").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Raw Data").Activate

 

End If
Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Raw Data").Cells(1, 1).Select

End Sub

25 Replies

@gms4b

 

Hi,

 

You can do this by a simple algorithm:

  • Search for the header of (Sample Type) in the worksheet using the Cell.Find method in VBA
  • Store the column number of the Sample Type header in a variable called: headerColumn
  • Use this variable in place of the number 4 in the IF statement's logical test

 

So the code looks like this:

Sub test()
    Application.ScreenUpdating = False
    
    Dim a As Long
    a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim headerColumn As Long
    headerColumn = Cells.Find(What:="Sample Type", After:=Range("A1"), LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Column
                   
    For i = 2 To a
        If Worksheets("Raw Data").Cells(i, headerColumn).Value = "Unknown" Then
            Worksheets("Raw Data").Rows(i).Copy
            Worksheets("Undiluted").Activate
            Dim b As Long
            b = Worksheets("Undiluted").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Undiluted").Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Raw Data").Activate
        End If
    Next
    
    Application.CutCopyMode = False
    ThisWorkbook.Worksheets("Raw Data").Cells(1, 1).Select
    Application.ScreenUpdating = True
End Sub

 

Hope that helps

@Haytham Amairah 

 

oooo. Nice, it works! Thanks!!

 

Another question? What if I also wanted to look for "Quality Control" in addition to "Unknown"....so, any row that had "Quality Control" or "Unknown" in a column with "Sample Type" as the header would get moved to the "Undiluted" sheet.

 

Thanks!

 

Greg

 

@gms4b 

@Haytham Amairah 

 

Nevermind....figured it out: 

 

If (Worksheets("Raw Data").Cells(i, headerColumn).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn).Value = "Quality Control") Then

 

Thanks!

@Haytham Amairah

 

Time for another question?

 

So, I did another algorithm to define another column header....."Dilution Factor" and changed your original line to:

If (Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" And Worksheets("Raw Data").Cells(i, headerColumn2).Value = "1") Then

 

And, this works, -----> if "Unknown" and "1" then copy to "Undiluted" sheet.

 

But, I'm having a hard time trying to add in 3 more conditions I'd like:

 

if "Unknown" and ">1" then copy to "Diluted" sheet.

if "Quality Control" and "1" then copy to "Undiluted" sheet.

if "Quality Control" and ">1" then copy to "Undiluted" sheet.

 

--or a simpler way to look at it

 

if ("Unknown" or "Quality Control") and "1" then copy to "Undiluted" Sheet

if ("Unknown" or "Quality Control") and ">1" then copy to "Diluted" Sheet

 

I tried a few things and can't get it to work right. I'm not a programmer...just a chemist! Trying to make my life easier.

 

Thanks for your help!

 

Greg

 

here's the code where I left off.....which works:

 

------------------------

Private Sub CommandButton1_Click()


Application.ScreenUpdating = False

Dim a As Long

a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row

Dim headerColumn1 As Long
headerColumn1 = Cells.Find(What:="Sample Type", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Dim headerColumn2 As Long
headerColumn2 = Cells.Find(What:="Dilution Factor", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column

For i = 2 To a
If (Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" And Worksheets("Raw Data").Cells(i, headerColumn2).Value = "1") Then
Worksheets("Raw Data").Rows(i).Copy
Worksheets("Undiluted").Activate
Dim b As Long
b = Worksheets("Undiluted").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Undiluted").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Raw Data").Activate
End If


Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Raw Data").Cells(1, 1).Select
Application.ScreenUpdating = True

End Sub

@Haytham Amairah 

 

Ha!! Got it!

It took awhile, but got it figured out. Now....need to find a way to copy the column headers (Row 1) from "Raw Data" to the "Undiluted" and "Diluted" sheets.

 

 

-----------------------------------------------------------------

Private Sub CommandButton1_Click()


Application.ScreenUpdating = False

Dim a As Long

a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row

Dim headerColumn1 As Long
headerColumn1 = Cells.Find(What:="Sample Type", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Dim headerColumn2 As Long
headerColumn2 = Cells.Find(What:="Dilution Factor", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column

For i = 2 To a
If ((Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Quality Control") And Worksheets("Raw Data").Cells(i, headerColumn2).Value = "1") Then
Worksheets("Raw Data").Rows(i).Copy
Worksheets("Undiluted").Activate
Dim b As Long
b = Worksheets("Undiluted").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Undiluted").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Raw Data").Activate
End If

If ((Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Quality Control") And Worksheets("Raw Data").Cells(i, headerColumn2).Value > 1) Then
Worksheets("Raw Data").Rows(i).Copy
Worksheets("Diluted").Activate
Dim c As Long
c = Worksheets("Diluted").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Diluted").Cells(c + 1, 1).Select
ActiveSheet.Paste
Worksheets("Raw Data").Activate
End If


Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Raw Data").Cells(1, 1).Select
Application.ScreenUpdating = True

End Sub

@gms4b

 

Hi,

 

Sorry about the late reply as I was too busy!

Is the problem solved?
Did you need anything else?
If not, please let me know and provide a sample of the data you work on to be able to figure it out.
 
Regards

@Haytham Amairah 

 

Thanks for getting back to me. I believe that I have solved everything so far. Thanks for getting me started....I was able to make it work from there. Thanks for the offer to help out in the future.

 

Greg

 

 

@Haytham Amairah 

 

Alright...I have another project for you! This builds on the previous code....It could be on a separate commandbutton, but ultimately I'd like to just add it to the end of the previous code.

 

And if you can get me started I can likely hack my way though the rest to fill out the whole idea.

 

Basically, I need to move columns from one sheet to another sheet ("Undiluted" to "UndilutedPLUS" and "Diluted" to "Diluted Plus"). The columns will be variable, so, like you showed before, I could used cells.find to define all of the columns that I'll need (probably 10-12 columns in all). 

 

So, actually, building on the code before, if you could show me how to move headercolumn1 (i.e. "Sample type") from sheet "Undiluted" to an empty sheet "UndilutedPLUS" and put it in cell A2......then I ought to be able to figure out how to define more columns and line them up in B2, C2, D2, etc. and also move columns from "Diluted" to "Diluted Plus."

 

The code as it stands is below....and I also attached the file ("Example with VBA4.xlsm" which has the data and code). 

 

(I'm guessing that somewhere you'll have to define a new worksheet (for the "Undiluted" sheet) and then use cells.find and define all of the columns there?)

 

 

I hope this isn't too much! Thanks for your help,

 

Greg

 

 

----------------------------------------------------------------

Private Sub CommandButton1_Click()


Application.ScreenUpdating = False

Dim a As Long

a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row

Dim headerColumn1 As Long
headerColumn1 = Cells.Find(What:="Sample Type", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Dim headerColumn2 As Long
headerColumn2 = Cells.Find(What:="Dilution Factor", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column

For i = 2 To a
If ((Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Quality Control") And Worksheets("Raw Data").Cells(i, headerColumn2).Value = "1") Then
Worksheets("Raw Data").Rows(i).Copy
Worksheets("Undiluted").Activate
Dim b As Long
b = Worksheets("Undiluted").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Undiluted").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Raw Data").Activate
End If

If ((Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Quality Control") And Worksheets("Raw Data").Cells(i, headerColumn2).Value > 1) Then
Worksheets("Raw Data").Rows(i).Copy
Worksheets("Diluted").Activate
Dim c As Long
c = Worksheets("Diluted").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Diluted").Cells(c + 1, 1).Select
ActiveSheet.Paste
Worksheets("Raw Data").Activate
End If

Sheets("Raw Data").Range("A1:CZ1").Copy Destination:=Sheets("Undiluted").Range("A1:CZ1")
Sheets("Raw Data").Range("A1:CZ1").Copy Destination:=Sheets("Diluted").Range("A1:CZ1")


Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Raw Data").Cells(1, 1).Select
Application.ScreenUpdating = True

End Sub

 

@gms4b

 

Hi,

 

As I understand, you want to move specific entire columns to other sheets, not some rows based on a condition?

@Haytham Amairah 

 

Yes, just specific columns.

 

Part one of this automation is taking the raw data and sorting it by rows into two sheets based on conditions (0 or >1).....and that code works great. That also gets rid of rows that I don't need.

 

Part two cleans up the data even more by getting rid of columns that I don't need.....by copying specific columns to a fresh sheet. I'll have normal excel formulas to the right of that data....I don't think VBA is necessary for that. So, in this case, there are no conditions that I need to be worried about (other than the name of the column header). I just need to move specific columns to new sheets.

 

Thanks,


Greg

 

@gms4b

 

Hi,

 

Please try this code to move the needed columns from Undiluted to UndilutedPLUS.

Sub test2()

Application.ScreenUpdating = False
    
    Dim i As Integer
    i = 0
    
    Sheets("Undiluted").Activate
    Dim columnNamesArray() As Variant
    columnNamesArray = Array("Analyte Peak Name", "Sample Type", "Rack Type", "Vial Position")
    
    Dim columnName As Variant
    For Each columnName In columnNamesArray
    
    Sheets("Undiluted").Activate
    
    Dim columnNumber As Integer
    columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Column
                   
    Cells(1, columnNumber).EntireColumn.Copy
    
    Sheets("UndilutedPLUS").Activate
    Range("A1").Activate
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName

Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

Please note that you have to define the needed columns you want to move in the code.

You can insert them into the columnNamesArray.

 

Hope that helps

@Haytham Amairah 

 

Thanks for the code. I like the idea of creating an array so I don't have to define each column individually.

 

Right now there is a Runtime Error 1004 (Activate method of Range Class failed) at the line that says:

 

Range("A1").activate

 

I added this code as a second command button. The first button does the first part of code (and works fine). The second button activates this new code.

 

Greg

 

@gms4b

 

The error is caused by some ambiguity because the code was run using a button embedded on the worksheet.

To remove the ambiguity, fix the code as follows:

 

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
    
    Dim i As Integer
    i = 0
    
    Sheets("Undiluted").Activate
    Dim columnNamesArray() As Variant
    columnNamesArray = Array("Analyte Peak Name", "Sample Type", "Rack Type", "Vial Position")
    
    Dim columnName As Variant
    For Each columnName In columnNamesArray
    
    Sheets("Undiluted").Activate
    
    Dim columnNumber As Integer
    columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Column
                   
    Cells(1, columnNumber).EntireColumn.Copy
    
    Sheets("UndilutedPLUS").Activate
    Sheets("UndilutedPLUS").Range("A1").Select
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName

ActiveSheet.Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Sub

 

 

Hope that helps

@Haytham Amairah 

 

Yes...that mostly fixed the problem, thanks. I added in all of the exact headers that I want and it copies them just fine. However, it's picking data from Sheet1 (i.e. "Raw Data") and moving it.

 

I had to more specifically specify which sheets to copy from by changing the line below.

 

Sheets("Undiluted").Cells(1, columnNumber).EntireColumn.Copy

 

Then it moves everything correctly! I'm not sure why it was still pulling from "Raw Data" when "Undiluted" was activated, but that fixes it.

 

I'm sure I'll have more questions....but for now, I'm good. 

 

Thanks!

 

Greg

 

 

 

@Haytham Amairah 

 

So, I'm having a hard time now figuring out how to add a second loop(?) to do the same thing for the "Diluted" and "DilutedPLUS" sheets. I guess you'd have to add "j as an integer" at the beginning, but then I get lost after that, lol. I can't figure out where to trigger the second loop with the new sheet names.

 

Thanks,


Greg

 

(current code below)

 

----------------------------------------------------------------------

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False

Dim i As Integer
i = 0

Sheets("Undiluted").Activate
Dim columnNamesArray() As Variant
columnNamesArray = Array("Sample Type", "Sample Name", "Acquisition Date", "File Name", "Dilution Factor", "Analyte Peak Name", "Analyte Concentration", "Calculated Concentration (")

Dim columnName As Variant
For Each columnName In columnNamesArray

Sheets("Undiluted").Activate

Dim columnNumber As Integer
columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column

Sheets("Undiluted").Cells(1, columnNumber).EntireColumn.Copy

Sheets("UndilutedPLUS").Activate
Sheets("UndilutedPLUS").Range("A1").Select
ActiveCell.Offset(0, i).Select
ActiveSheet.Paste
i = i + 1
Next columnName

ActiveSheet.Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

End Sub

@gms4b 

I suspect that with the newest releases of Office 365 most of the things described here can be done easily with worksheet formulas.  The points to observe are that all raw data should be entered into Excel Tables so that the field to be searched for the value "Unknown" can be identified by name rather than a static numeric index.  Given that tables adjust dynamically to the data, no counts are required to determine the length of the column.

 

The key function is FILTER which will return all the records that match the criterion

= FILTER( Table1, Table1[field]="Unknown"} )

 

The advantage of names is as great when it comes to VBA.  A named field can be block-read into a variant array and then the looping is in memory rather than requiring continual exchanges between the VBA environment and the worksheet.  If the column changes, the Name moves with it, so the code is more robust.

@gms4b

 

It's easy, just copy the code in a new button in the Diluted sheet, and change the sheet names in it.

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
    
    Dim i As Integer
    i = 0
    
    Sheets("Diluted").Activate
    Dim columnNamesArray() As Variant
    columnNamesArray = Array("Sample Type", "Rack Type")
    
    Dim columnName As Variant
    For Each columnName In columnNamesArray
     
    Dim columnNumber As Integer
    columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Column
                   
    Cells(1, columnNumber).EntireColumn.Copy
    
    Sheets("DilutedPLUS").Activate
    Sheets("DilutedPLUS").Range("A1").Select
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName

ActiveSheet.Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

@Haytham Amairah 

 

Yes, yes, lol. I was thinking the same thing after I left work yesterday. I'm sure there's a way to combine it together into some sort of loop, but this is easier. I also created a 4th command button which sequentially runs each of the first three scripts! In the end, I think it'll be nice to have the option to run things separately....or just run everything. 

 

Thanks again!

 

Greg

@gms4b

 

In my opinion, an embedded button on each worksheet is not a good idea!

You can run the code from the Quick Access Toolbar or from a new ribbon tab.

Or you can place the buttons in one worksheet as follows:

Run.png

 

Also, grouping all the process code in one Sub is also not a good idea!

You can instead separate them in three Subs so that each Sub handle one part of the automation.

Then move all Subs in a new generic module.

 

Then create a sub called (Main) and call the other subs in it.

This makes the code more readable and maintainable.

 

This is the full Module1 code including all Subs.

 

Sub Main()
    Call FilterData
    Call MoveToUndilutedPLUS
    Call MoveToDilutedPLUS
End Sub

Sub FilterData()
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Worksheets("Raw Data").Activate
    
    Dim a As Long
    a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim headerColumn1 As Long
        headerColumn1 = Cells.Find(What:="Sample Type", After:=Range("A1"), LookIn:=xlFormulas, _
                       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                       MatchCase:=False, SearchFormat:=False).Column
    Dim headerColumn2 As Long
        headerColumn2 = Cells.Find(What:="Dilution Factor", After:=Range("A1"), LookIn:=xlFormulas, _
                       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                       MatchCase:=False, SearchFormat:=False).Column
                   
    For i = 2 To a
        If ((Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Quality Control") And Worksheets("Raw Data").Cells(i, headerColumn2).Value = "1") Then
            Worksheets("Raw Data").Rows(i).Copy
            Worksheets("Undiluted").Activate
            Dim b As Long
            b = Worksheets("Undiluted").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Undiluted").Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Raw Data").Activate
        End If

        If ((Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Quality Control") And Worksheets("Raw Data").Cells(i, headerColumn2).Value > 1) Then
            Worksheets("Raw Data").Rows(i).Copy
            Worksheets("Diluted").Activate
            Dim c As Long
            c = Worksheets("Diluted").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Diluted").Cells(c + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Raw Data").Activate
        End If
        
    Sheets("Raw Data").Range("A1:CZ1").Copy Destination:=Sheets("Undiluted").Range("A1:CZ1")
    Sheets("Raw Data").Range("A1:CZ1").Copy Destination:=Sheets("Diluted").Range("A1:CZ1")
    
    Next
    
    Worksheets("Raw Data").Activate
    Worksheets("Raw Data").Range("A1").Select
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0
End Sub

Sub MoveToUndilutedPLUS()
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Dim i As Integer
    i = 0
    
    Sheets("Undiluted").Activate
    Dim columnNamesArray() As Variant
    columnNamesArray = Array("Analyte Peak Name", "Sample Type", "Rack Type", "Vial Position")
    
    Dim columnName As Variant
    For Each columnName In columnNamesArray
    Sheets("Undiluted").Activate
        
    Dim columnNumber As Integer
    columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Column
                   
    Cells(1, columnNumber).EntireColumn.Copy
    
    Sheets("UndilutedPLUS").Activate
    Sheets("UndilutedPLUS").Range("A1").Select
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName
    
    Sheets("UndilutedPLUS").Range("A1").Select
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0
End Sub

Sub MoveToDilutedPLUS()
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Dim i As Integer
    i = 0
    
    Sheets("Diluted").Activate
    Dim columnNamesArray() As Variant
    columnNamesArray = Array("Sample Type", "Rack Type")
    
    Dim columnName As Variant
    For Each columnName In columnNamesArray
    Sheets("Diluted").Activate
     
    Dim columnNumber As Integer
    columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Column
                   
    Cells(1, columnNumber).EntireColumn.Copy
    
    Sheets("DilutedPLUS").Activate
    Sheets("DilutedPLUS").Range("A1").Select
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName
    
    Sheets("DilutedPLUS").Range("A1").Select
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0
End Sub

 

 

This also makes the code reusable as the embedded buttons don't duplicate the same code, it just reuses it or recalls it.

 

This is the embedded buttons code behind the Run sheet:

Code Behind.png

 

Please find all that in the attached worksheet.

Regards

@Haytham Amairah 

 

Thank you for the advice. Good to know about not grouping everything into one sub. What you described I think is basically where I'm at now. I added a 4th Sub (CommandButton) which sequentially runs each of the 3 subs so far. I can just modify things in the future as I add more. I can see how your code is certainly more elegant. I'll look into that file. Thank you so much!

 

----------------------------------

Private Sub CommandButton4_Click()
CommandButton1_Click
CommandButton2_Click
CommandButton3_Click
End Sub

------------------------------------

 

clipboard_image_0.png

@Haytham Amairah 

 

Ok...I've been adding to the code that you cleaned up. With the new code I haven't created buttons...I've just added more subs to Module1 and called them to Main like you did. For most everything new I simply recorded a macro and copied that code to Module1. The columns are static at this point, which makes it easier. I usually did some sort of modification to make it work (added "& LastRow" to autofill down to the last entry in the column, for example). I tested the new code by hitting F8 through each line to make sure it does what it should.

 

I have one crucial, and (maybe) tricky bit of code to write...and I have no idea how to do it...or which would be easiest. Big picture: I run biological samples at work. Initially I run the samples "undiluted" and see if the data falls within a certain range. If not, then the samples are run again "diluted". Sometimes they still don't fall within the range and are diluted again. At some point, regardless of being within a range, the data is reported. 

 

In the "UndilutedPLUS" and "DilutedPLUS" tabs I've added many columns. The important ones to know: 

ColumnA: this is a helper column....it gives the sample a unique ID (i.e for VLOOKUP)

ColumnM: this says whether the sample is good (1) or not (0)

 

So, let me know if this is possible:

1) Copy Column A (helper column) from the "UndilutedPLUS" sheet to the "Final Data" sheet. This gives a definitive list of all samples (since each sample is run undiluted). Now we need data from the "UndilutedPLUS" or "DilutedPLUS" sheets. 

2) Use VLOOKUP? (or anything else?) to look for the corresponding row of data in "UndilutedPLUS" (using the entry in the helper column (ColumnA)) and

if Column M=1 then copy the data to "Final Data." (and you're done)

if Column M=0 then next

3) Look for the corresponding row of data in the "DilutedPLUS" sheet and

if Column M=1 then copy the row of data to "Final Data" (and you're done)

if Column M=0 then next

4) Look for the corresponding row of data in the "DilutedPLUS" sheet and

if Column M=0 then copy the row of data to "Final Data" (and you're done)

 

I have a way to do this using VLOOKUP for each individual cell in the spreadsheet. It works, but is clunky. It uses 3 nested "if" statements with VLOOKUPs to go through the statements above for each cell. Then it gets modified for each column and then filled down to the last row in the "Final Data" sheet. It's doable, but not pretty.

 

I hope this isn't too tough. I feel that doing complicated things with formulas becomes unnecessarily difficult whereas a good script can do it more easily. I'm just giving you extra detail in case you need it. 

 

So, I have a small data set in the attached document.

*Sample A1 should get moved first thing and data reported because the value in "Undiluted Plus" in column M=1.

*Sample B1, shows Column M=0 in the "UndilutedPLUS" sheet so you'd go the the "DilutedPLUS" sheet where one dilution gave Column M=0 but the second one gave, Column M=1...so that data will be reported. 

*Sample C1, similar to B1 but both dilutions gave Column M=0, so the second one will be reported. 

 

The last tab in the spreadsheet is "Final Data Example" which shows what the final result should look like. Good luck if you feel like taking this on. I'll be on vacation next will but will bring my computer in case you have a chance to work on it. 

 

Cheers and thanks,

 

Greg

 

 

 

@gms4b 

I am still somewhat puzzled as to the reason you turn so readily to VBA for the analysis.  Excel has a huge amount of built-in functionality to support filtering, both as manual operations and automatic, using formulas,

 

I ran the code in "Example with VBA4.xlsm" this morning and it took 1min20sec to produce 630 rows for 'undiluted' and a further 30 on the 'diluted' sheet.  I am sure there is plenty of room for speed optimisation of the code but I went on to compare that time with that of a formula-based approach.

 

I happened to be using a machine with Office 365 loaded so, having converted the source data to a table, I used a modern dynamic array formula to extract the required records.  The formulas

= FILTER( RawData,

((RawData[Sample Type]="Unknown") +

(RawData[Sample Type]="Quality Control")) *

(RawData[Dilution Factor]=1) )

and

= FILTER( RawData,

((RawData[Sample Type]="Unknown") +

(RawData[Sample Type]="Quality Control")) *

(RawData[Dilution Factor]>1) )

each took 0.1sec to run and gave identical output.

 

p.s. Other options would include running the Advanced Filter from code (any version of Excel) or to use Power Query (Office 2010 with add-in, native 2016 and on).

 

I have now attached a copy of the file with your early VBA, with Power Query and with Dynamic Array formulas.  The last only works with the most up-to-date versions of Office365.

@gms4b

 

It seems to be a rough process and needs a lot of work!
Maybe I don't have enough time to look at it!
I'm sorry!
But apparently, the solution through the code would not be better, because in the code we would consider using the same lookup functions in the worksheet as in the example below:
Application.WorksheetFunction.VLookup

 

So I advise you to consider other solutions suggested by @Peter Bartholomew.

Or stick with the analysis you've already done using VLOOKUP.

 

Regards

@Peter Bartholomew 

 

Thanks Peter. I'll start to look into this. In the end, the whole process is:

- dividing Unknown and Quality Control samples between diluted and undiluted groups (Then get rid of columns that I don't need)

- Adding some columns to each of these sets to do some math do determine if the samples are in a range

- Then a VLOOKUP to compare samples in the diluted and undiluted groups and choose the proper one for final data. 

 

This has been a very manual process not just for me, but my teammates. I wanted to make a way for people to simply add in all the raw data into the first tab of a spreadsheet, press a button, and get final data. So, that's why I thought VBA would be a good way to automate the whole process. 

 

If i can find a way to do the filtering (as you suggest) and replace the first part of the code, then that sounds like a good idea. As you mentioned.....it can be rather slow. And, its possible that 1000's of rows of data could be in a set. 

 

Thanks,

 

Greg

 

 

@gms4b 

Do you and any colleagues that need to manipulate the results have Office 365 and can you set it to monthly updates?  The array methods I have used have only come out in the latest releases of Excel and, though it would be possible to transfer the data using INDEX it would lack elegance. 

Removing columns could be achieved by being more selective as to the columns brought through in the first place, even down to the level of bring key columns across individually within the downstream calculation.