SOLVED
Home

How can I tell macro to perform operations below "= vbNo Then" ?

%3CLINGO-SUB%20id%3D%22lingo-sub-740798%22%20slang%3D%22en-US%22%3EHow%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740798%22%20slang%3D%22en-US%22%3E%3CP%3ESub%20OPTIMIZE()%3CBR%20%2F%3EIf%20MsgBox(%22OPTIMIZE%20AWSORT%20FOR%20A%20MINIMUM%20OF%20TWENTY-FOUR%20INSTRUMENTS.%20HAVE%20YOU%20COMPLETED%20OPTIMIZATION%3F%22%2C%20vbQuestion%20%2B%20vbYesNo%2C%20%22%22)%20%3D%20vbYes%20Then%3CBR%20%2F%3ECall%20Combo_MacroLessThan4k%3CBR%20%2F%3E%3CBR%20%2F%3E%22%3D%20vbNo%20Then%22%20(How%20can%20I%20tell%20macro%20to%20perform%20operations%20below)%3CBR%20%2F%3E%3CBR%20%2F%3EActiveWindow.ScrollRow%20%3D%20624%3CBR%20%2F%3ERange(%22V13%3AV14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22V640%3AV641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22W13%3AW14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22W640%3AW641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22X13%3AX14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22X640%3AX641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22Y13%3AY14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22Y640%3AY641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22Z13%3AZ14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22Z640%3AZ641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22AA13%3AAA14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22AA640%3AAA641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22V13%3AZ14%22).Select%3CBR%20%2F%3ESelection.ClearContents%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlAutomatic%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EActiveSheet.Range(%22%24BB%2421%3A%24BD%24629%22).AutoFilter%20Field%3A%3D1%3C%2FP%3E%3CP%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.CLEAR%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20Key%3A%3DRange(%20_%3CBR%20%2F%3E%22aw30%3Aaw629%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlDescending%2C%20DataOption%3A%3D%20_%3CBR%20%2F%3ExlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E.SetRange%20Range(%22B30%3ABA629%22)%3CBR%20%2F%3E.Header%20%3D%20xlGuess%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-740798%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740900%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740900%22%20slang%3D%22en-US%22%3E%3CP%3EHey%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20use%20an%20Else%20statement%20and%20indent%20what%20you%20have%20written%20that%20must%20happen%20when%20%22%3DvbNo%22%3C%2FP%3E%3CP%3E.......%3C%2FP%3E%3CP%3ECall%20ComboMacroLessThan4k%3C%2FP%3E%3CP%3EElse%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20ActiveWindow.ScrollRow%20%3D%20624%3C%2FP%3E%3CP%3E........%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20does%20not%20work%2C%20just%20use%20an%20ElseIf%20statement%26nbsp%3B%3C%2FP%3E%3CP%3E........%3C%2FP%3E%3CP%3E%3CSPAN%3ECall%20Combo_MacroLessThan4k%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EElseIf%26nbsp%3BIf%20MsgBox(%22OPTIMIZE%20AWSORT%20FOR%20A%20MINIMUM%20OF%20TWENTY-FOUR%20INSTRUMENTS.%20HAVE%20YOU%20COMPLETED%20OPTIMIZATION%3F%22%2C%20vbQuestion%20%2B%20vbYesNo%2C%20%22%22)%20%3D%20vb%3CSTRONG%3ENo%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FSTRONG%3EActiveWindow.ScrollRow%20%3D%20624%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E........%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%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-740961%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367553%22%20target%3D%22_blank%22%3E%40Excel_World_Champion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20Thank%20you%20for%20your%20assistence.%20I%20attempted%20your%20suggestion%20with%20the%20following%20code%2C%20adding%20a%20%22cancel%22%20option.%20However%20I%20get%20compile%20errors.%20Please%20examine%20and%20correct%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20OPTIMIZE()%3CBR%20%2F%3EIf%20MsgBox(%22OPTIMIZE%20AWSORT%20FOR%20A%20MINIMUM%20OF%20TWENTY-FOUR%20INSTRUMENTS.%20HAVE%20YOU%20COMPLETED%20OPTIMIZATION%3F%22%2C%20vbQuestion%20%2B%20vbYesNoCancel%2C%20%22HonorSystem24%22)%20%3D%20vbYes%20Then%3CBR%20%2F%3E%3CBR%20%2F%3ECall%20Combo_MacroLessThan4k%3C%2FP%3E%3CP%3EElse%3CBR%20%2F%3E%3CBR%20%2F%3EActiveWindow.ScrollRow%20%3D%20624%3CBR%20%2F%3ERange(%22V13%3AV14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22V640%3AV641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22W13%3AW14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22W640%3AW641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22X13%3AX14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22X640%3AX641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22Y13%3AY14%22).SelectSelection.CopyRange(%22Y640%3AY641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22Z13%3AZ14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22Z640%3AZ641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22AA13%3AAA14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22AA640%3AAA641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22V13%3AZ14%22).Select%3CBR%20%2F%3ESelection.ClearContents%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740985%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740985%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20all%20you%20have%20got%20to%20do%20now%20is%20add%20and%20%22End%20If%22%20on%20a%20line%20before%20the%20%22End%20Sub%22%20statement%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740992%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740992%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20than%20Helped.%20But%20%22Cancel%22%20and%20%22No%22%20appear%20to%20both%20be%20running%20the%20%22Call%22%26nbsp%3B%20macro.%20How%20can%20I%20make%20%22Cancel%20simply%20abort%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367553%22%20target%3D%22_blank%22%3E%40Excel_World_Champion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740994%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740994%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20what%20you%20have%20to%20do%20is%20to%20make%20an%20%22ElseIf%22%20statement%20for%20vbNo%2C%20and%20and%20%22Else%22%20statement%20for%20the%20when%20you%20press%20cancel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20code%20I%20used%20(I%20just%20used%20%3CSTRONG%3E%22a%22%26nbsp%3B%3C%2FSTRONG%3Eas%20a%20variable%20for%20the%20Messagebox)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20OPTIMIZE()%3C%2FP%3E%3CP%3Ea%20%3D%20MsgBox(%22OPTIMIZE%20AWSORT%20FOR%20A%20MINIMUM%20OF%20TWENTY-FOUR%20INSTRUMENTS.%20HAVE%20YOU%20COMPLETED%20OPTIMIZATION%3F%22%2C%20vbQuestion%20%2B%20vbYesNoCancel%2C%20%22HonorSystem24%22)%3C%2FP%3E%3CP%3EIf%20a%20%3D%20vbYes%20Then%3CBR%20%2F%3ECall%20Combo_MacroLessThan4k%3C%2FP%3E%3CP%3EElseIf%20a%20%3D%20vbNo%20Then%3C%2FP%3E%3CP%3EActiveWindow.ScrollRow%20%3D%20624%3CBR%20%2F%3ERange(%22V13%3AV14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22V640%3AV641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22W13%3AW14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22W640%3AW641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22X13%3AX14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22X640%3AX641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22Y13%3AY14%22).SelectSelection.CopyRange(%22Y640%3AY641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22Z13%3AZ14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22Z640%3AZ641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22AA13%3AAA14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22AA640%3AAA641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22V13%3AZ14%22).Select%3CBR%20%2F%3ESelection.ClearContents%3CBR%20%2F%3E%3CBR%20%2F%3EElse%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%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-740997%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740997%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20Your%20code%20ran%20perfectly.%20One%20final%20question%3A%20Why%20do%20I%20get%20and%20%22Else%22%20Compile%20error%20when%20I%20place%20the%20following%20code%20just%20above%20your%20last%20%22Else%22%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20OPTIMIZE()%3C%2FP%3E%3CP%3Ea%20%3D%20MsgBox(%22OPTIMIZE%20AWSORT%20FOR%20A%20MINIMUM%20OF%20TWENTY-FOUR%20INSTRUMENTS.%20HAVE%20YOU%20COMPLETED%20OPTIMIZATION%3F%22%2C%20vbQuestion%20%2B%20vbYesNoCancel%2C%20%22HonorSystem24%22)%3C%2FP%3E%3CP%3EIf%20a%20%3D%20vbYes%20Then%3CBR%20%2F%3ECall%20Combo_MacroLessThan4k%3C%2FP%3E%3CP%3EElseIf%20a%20%3D%20vbNo%20Then%3C%2FP%3E%3CP%3EActiveWindow.ScrollRow%20%3D%20624%3CBR%20%2F%3ERange(%22V13%3AV14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22V640%3AV641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22W13%3AW14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22W640%3AW641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22X13%3AX14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22X640%3AX641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22Y13%3AY14%22).SelectSelection.CopyRange(%22Y640%3AY641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22Z13%3AZ14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22Z640%3AZ641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22AA13%3AAA14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22AA640%3AAA641%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ERange(%22V13%3AZ14%22).Select%3CBR%20%2F%3ESelection.ClearContents%3C%2FP%3E%3CP%3EApplication.Calculation%20%3D%20xlAutomatic%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EActiveSheet.Range(%22%24BB%2421%3A%24BD%24629%22).AutoFilter%20Field%3A%3D1%3C%2FP%3E%3CP%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.CLEAR%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20Key%3A%3DRange(%20_%3CBR%20%2F%3E%22aw30%3Aaw629%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlDescending%2C%20DataOption%3A%3D%20_%3CBR%20%2F%3ExlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E.SetRange%20Range(%22B30%3ABA629%22)%3CBR%20%2F%3E.Header%20%3D%20xlGuess%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EElse%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367553%22%20target%3D%22_blank%22%3E%40Excel_World_Champion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740998%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740998%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20probably%20because%20you%20have%20not%20added%20an%20%22End%20With%22%20statement%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-741002%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741002%22%20slang%3D%22en-US%22%3E%3CP%3EEverything%20works%20perfectly.%20Thank%20you%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367553%22%20target%3D%22_blank%22%3E%40Excel_World_Champion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-741003%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20tell%20macro%20to%20perform%20operations%20below%20%22%3D%20vbNo%20Then%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741003%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367553%22%20target%3D%22_blank%22%3E%40Excel_World_Champion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHappy%20to%20Help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Greg Bonaparte
Contributor

Sub OPTIMIZE()
If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNo, "") = vbYes Then
Call Combo_MacroLessThan4k

"= vbNo Then" (How can I tell macro to perform operations below)

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents

Application.Calculation = xlAutomatic


ActiveSheet.Range("$BB$21:$BD$629").AutoFilter Field:=1

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"aw30:aw629"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B30:BA629")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End If
End Sub

9 Replies

Hey@Greg Bonaparte 

Just use an Else statement and indent what you have written that must happen when "=vbNo"

.......

Call ComboMacroLessThan4k

Else

      ActiveWindow.ScrollRow = 624

........

 

If that does not work, just use an ElseIf statement 

........

Call Combo_MacroLessThan4k

ElseIf If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNo, "") = vbNo

       ActiveWindow.ScrollRow = 624

........

 

 

 

 

@Excel_World_Champion 

Hello, Thank you for your assistence. I attempted your suggestion with the following code, adding a "cancel" option. However I get compile errors. Please examine and correct:

 

Sub OPTIMIZE()
If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24") = vbYes Then

Call Combo_MacroLessThan4k

Else

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents
End Sub

Hey @Greg Bonaparte 

 

I think all you have got to do now is add and "End If" on a line before the "End Sub" statement

 

Thank you than Helped. But "Cancel" and "No" appear to both be running the "Call"  macro. How can I make "Cancel simply abort?

 

@Excel_World_Champion 

Hey @Greg Bonaparte 

 

So, what you have to do is to make an "ElseIf" statement for vbNo, and and "Else" statement for the when you press cancel. 

 

Here is the code I used (I just used "a" as a variable for the Messagebox):

 

Sub OPTIMIZE()

a = MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24")

If a = vbYes Then
Call Combo_MacroLessThan4k

ElseIf a = vbNo Then

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents

Else
Exit Sub
End If
End Sub

 

 

Thank you, Your code ran perfectly. One final question: Why do I get and "Else" Compile error when I place the following code just above your last "Else":

 

Sub OPTIMIZE()

a = MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24")

If a = vbYes Then
Call Combo_MacroLessThan4k

ElseIf a = vbNo Then

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents

Application.Calculation = xlAutomatic


ActiveSheet.Range("$BB$21:$BD$629").AutoFilter Field:=1

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"aw30:aw629"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B30:BA629")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

 


Else
Exit Sub
End If
End Sub

@Excel_World_Champion 

Solution

Hey @Greg Bonaparte 

 

Its probably because you have not added an "End With" statement

Everything works perfectly. Thank you@Excel_World_Champion 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies