Macro to Copy, Sort, Past Formats, Ignore Null

[mackensteff]

Hello,
My goal is to have a macro copy a known range (columns A:L in picture), sort them by the values typed in the yellow boxes in column M, paste the values starting in column N, ignoring null values, and then paste the format to match columns A:L.

I have include a picture of the desired for results as well as data if anyone is willing to try this out.

I appreciate your time!!

[TABLE="width: 1805"]
<tbody>[TR]
[TD]Bib[/TD]
[TD]Last, First[/TD]
[TD]Name[/TD]
[TD]DivPl[/TD]
[TD]SexPl[/TD]
[TD]Time[/TD]
[TD]Division[/TD]
[TD]School[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]TeamPos.[/TD]
[TD]TeamPlace[/TD]
[TD]Sort 1st[/TD]
[TD]Bib[/TD]
[TD]Last, First[/TD]
[TD]Name[/TD]
[TD]DivPl[/TD]
[TD]SexPl[/TD]
[TD]Time[/TD]
[TD]Division[/TD]
[TD]School[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]TeamPos.[/TD]
[TD]TeamPlace[/TD]
[/TR]
[TR]
[TD]137[/TD]
[TD]Green, Noah[/TD]
[TD]Kober, Maia[/TD]
[TD]42[/TD]
[TD]90[/TD]
[TD]18:41[/TD]
[TD]Boys: JV[/TD]
[TD]Cedar[/TD]
[TD]10[/TD]
[TD]35[/TD]
[TD]6[/TD]
[TD]4th Place[/TD]
[TD]School[/TD]
[TD]44[/TD]
[TD]Blake, Luke[/TD]
[TD]Chastain, Chara[/TD]
[TD]61[/TD]
[TD]118[/TD]
[TD]19:13[/TD]
[TD]Boys: JV[/TD]
[TD]Canyon View[/TD]
[TD]12[/TD]
[TD]NS[/TD]
[TD]10[/TD]
[TD]3rd Place[/TD]
[/TR]
[TR]
[TD]117[/TD]
[TD]Coates, Branson[/TD]
[TD]Basnett, Marilu[/TD]
[TD]43[/TD]
[TD]91[/TD]
[TD]18:41[/TD]
[TD]Boys: JV[/TD]
[TD]Cedar[/TD]
[TD]10[/TD]
[TD]36[/TD]
[TD]7[/TD]
[TD]4th Place[/TD]
[TD]Sort 2nd[/TD]
[TD]49[/TD]
[TD]Jarvis, Paul[/TD]
[TD]Tompson, Eugenie[/TD]
[TD]47[/TD]
[TD]98[/TD]
[TD]18:53[/TD]
[TD]Boys: JV[/TD]
[TD]Canyon View[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]8[/TD]
[TD]3rd Place[/TD]
[/TR]
[TR]
[TD]204[/TD]
[TD]Sandberg, Jack[/TD]
[TD]Moree, Lashonda[/TD]
[TD]44[/TD]
[TD]92[/TD]
[TD]18:41[/TD]
[TD]Boys: JV[/TD]
[TD]Desert Hills[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]9[/TD]
[TD]1st Place[/TD]
[TD]Division[/TD]
[TD]36[/TD]
[TD]Valenzuela, Eddie[/TD]
[TD]Bechard, Emerson[/TD]
[TD]52[/TD]
[TD]108[/TD]
[TD]19:07[/TD]
[TD]Boys: JV[/TD]
[TD]Canyon View[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]9[/TD]
[TD]3rd Place[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]Webster, Mic[/TD]
[TD]Keffer, Merrill[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]18:45[/TD]
[TD]Girls: Varsity[/TD]
[TD]Cedar[/TD]
[TD]11[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3rd Place[/TD]
[TD]Sort 3rd[/TD]
[TD]117[/TD]
[TD]Coates, Branson[/TD]
[TD]Basnett, Marilu[/TD]
[TD]43[/TD]
[TD]91[/TD]
[TD]18:41[/TD]
[TD]Boys: JV[/TD]
[TD]Cedar[/TD]
[TD]10[/TD]
[TD]36[/TD]
[TD]7[/TD]
[TD]4th Place[/TD]
[/TR]
[TR]
[TD]205[/TD]
[TD]Hendrix, Jaron[/TD]
[TD]Nale, Kesha[/TD]
[TD]45[/TD]
[TD]93[/TD]
[TD]18:46[/TD]
[TD]Boys: JV[/TD]
[TD]Desert Hills[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]10[/TD]
[TD]1st Place[/TD]
[TD]Last, First[/TD]
[TD]137[/TD]
[TD]Green, Noah[/TD]
[TD]Kober, Maia[/TD]
[TD]42[/TD]
[TD]90[/TD]
[TD]18:41[/TD]
[TD]Boys: JV[/TD]
[TD]Cedar[/TD]
[TD]10[/TD]
[TD]35[/TD]
[TD]6[/TD]
[TD]4th Place[/TD]
[/TR]
[TR]
[TD]292[/TD]
[TD]Dansie, Porter[/TD]
[TD]Mendoza, Juliet[/TD]
[TD]46[/TD]
[TD]94[/TD]
[TD]18:46[/TD]
[TD]Boys: JV[/TD]
[TD]Hurricane[/TD]
[TD]10[/TD]
[TD]37[/TD]
[TD]5[/TD]
[TD]5th Place[/TD]
[TD][/TD]
[TD]114[/TD]
[TD]Prince, Austin[/TD]
[TD]Gayles, Bret[/TD]
[TD]59[/TD]
[TD]116[/TD]
[TD]19:12[/TD]
[TD]Boys: JV[/TD]
[TD]Cedar[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]8[/TD]
[TD]4th Place[/TD]
[/TR]
[TR]
[TD]259[/TD]
[TD]Barker, Corbyn[/TD]
[TD]Pelley, Vonnie[/TD]
[TD]49[/TD]
[TD]95[/TD]
[TD]18:46[/TD]
[TD]Boys: Varsity[/TD]
[TD]Dixie[/TD]
[TD]10[/TD]
[TD]49[/TD]
[TD]3[/TD]
[TD]8th Place[/TD]
[TD][/TD]
[TD]205[/TD]
[TD]Hendrix, Jaron[/TD]
[TD]Nale, Kesha[/TD]
[TD]45[/TD]
[TD]93[/TD]
[TD]18:46[/TD]
[TD]Boys: JV[/TD]
[TD]Desert Hills[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]10[/TD]
[TD]1st Place[/TD]
[/TR]
[TR]
[TD]260[/TD]
[TD]Wells, George[/TD]
[TD]Pool, Jung[/TD]
[TD]50[/TD]
[TD]95[/TD]
[TD]18:46[/TD]
[TD]Boys: Varsity[/TD]
[TD]Dixie[/TD]
[TD]12[/TD]
[TD]50[/TD]
[TD]4[/TD]
[TD]8th Place[/TD]
[TD][/TD]
[TD]207[/TD]
[TD]Messel, Jordan[/TD]
[TD]Wegman, Edwina[/TD]
[TD]57[/TD]
[TD]114[/TD]
[TD]19:10[/TD]
[TD]Boys: JV[/TD]
[TD]Desert Hills[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]12[/TD]
[TD]1st Place[/TD]
[/TR]
[TR]
[TD]264[/TD]
[TD]Wilkinson, Tate[/TD]
[TD]Mcquire, Ronda[/TD]
[TD]51[/TD]
[TD]97[/TD]
[TD]18:48[/TD]
[TD]Boys: Varsity[/TD]
[TD]Dixie[/TD]
[TD]11[/TD]
[TD]51[/TD]
[TD]5[/TD]
[TD]8th Place[/TD]
[TD][/TD]
[TD]204[/TD]
[TD]Sandberg, Jack[/TD]
[TD]Moree, Lashonda[/TD]
[TD]44[/TD]
[TD]92[/TD]
[TD]18:41[/TD]
[TD]Boys: JV[/TD]
[TD]Desert Hills[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]9[/TD]
[TD]1st Place[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]Taylor, Harley[/TD]
[TD]States, Sam[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]18:52[/TD]
[TD]Girls: Varsity[/TD]
[TD]Cedar[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]3rd Place[/TD]
[TD][/TD]
[TD]254[/TD]
[TD]Carlile, Spencer[/TD]
[TD]Dolby, Tonya[/TD]
[TD]62[/TD]
[TD]119[/TD]
[TD]19:15[/TD]
[TD]Boys: JV[/TD]
[TD]Dixie[/TD]
[TD]9[/TD]
[TD]47[/TD]
[TD]4[/TD]
[TD]7th Place[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]Jarvis, Paul[/TD]
[TD]Tompson, Eugenie[/TD]
[TD]47[/TD]
[TD]98[/TD]
[TD]18:53[/TD]
[TD]Boys: JV[/TD]
[TD]Canyon View[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]8[/TD]
[TD]3rd Place[/TD]
[TD][/TD]
[TD]292[/TD]
[TD]Dansie, Porter[/TD]
[TD]Mendoza, Juliet[/TD]
[TD]46[/TD]
[TD]94[/TD]
[TD]18:46[/TD]
[TD]Boys: JV[/TD]
[TD]Hurricane[/TD]
[TD]10[/TD]
[TD]37[/TD]
[TD]5[/TD]
[TD]5th Place[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Valenzuela, Eddie[/TD]
[TD]Bechard, Emerson[/TD]
[TD]52[/TD]
[TD]108[/TD]
[TD]19:07[/TD]
[TD]Boys: JV[/TD]
[TD]Canyon View[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]9[/TD]
[TD]3rd Place[/TD]
[TD][/TD]
[TD]443[/TD]
[TD]Hall, Hunter[/TD]
[TD]Peer, Kristian[/TD]
[TD]58[/TD]
[TD]115[/TD]
[TD]19:10[/TD]
[TD]Boys: JV[/TD]
[TD]Snow Canyon[/TD]
[TD]9[/TD]
[TD]45[/TD]
[TD]1[/TD]
[TD]8th Place[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]Curtis, Nathan[/TD]
[TD]Kratky, Lilliana[/TD]
[TD]53[/TD]
[TD]109[/TD]
[TD]19:07[/TD]
[TD]Boys: JV[/TD]
[TD]Wasatch[/TD]
[TD]10[/TD]
[TD]41[/TD]
[TD]3[/TD]
[TD]6th Place[/TD]
[TD][/TD]
[TD]500[/TD]
[TD]Curtis, Nathan[/TD]
[TD]Kratky, Lilliana[/TD]
[TD]53[/TD]
[TD]109[/TD]
[TD]19:07[/TD]
[TD]Boys: JV[/TD]
[TD]Wasatch[/TD]
[TD]10[/TD]
[TD]41[/TD]
[TD]3[/TD]
[TD]6th Place[/TD]
[/TR]
[TR]
[TD]485[/TD]
[TD]Patterson, Braden[/TD]
[TD]Nystrom, Sachiko[/TD]
[TD]54[/TD]
[TD]110[/TD]
[TD]19:08[/TD]
[TD]Boys: JV[/TD]
[TD]Wasatch[/TD]
[TD]11[/TD]
[TD]42[/TD]
[TD]4[/TD]
[TD]6th Place[/TD]
[TD][/TD]
[TD]498[/TD]
[TD]Davis, McCall[/TD]
[TD]Foulds, Randell[/TD]
[TD]55[/TD]
[TD]111[/TD]
[TD]19:08[/TD]
[TD]Boys: JV[/TD]
[TD]Wasatch[/TD]
[TD]11[/TD]
[TD]43[/TD]
[TD]5[/TD]
[TD]6th Place[/TD]
[/TR]
[TR]
[TD]498[/TD]
[TD]Davis, McCall[/TD]
[TD]Foulds, Randell[/TD]
[TD]55[/TD]
[TD]111[/TD]
[TD]19:08[/TD]
[TD]Boys: JV[/TD]
[TD]Wasatch[/TD]
[TD]11[/TD]
[TD]43[/TD]
[TD]5[/TD]
[TD]6th Place[/TD]
[TD][/TD]
[TD]486[/TD]
[TD]Jarvis, Caeden[/TD]
[TD]Laureano, Linh[/TD]
[TD]56[/TD]
[TD]113[/TD]
[TD]19:09[/TD]
[TD]Boys: JV[/TD]
[TD]Wasatch[/TD]
[TD]9[/TD]
[TD]44[/TD]
[TD]6[/TD]
[TD]6th Place[/TD]
[/TR]
[TR]
[TD]505[/TD]
[TD]McCullough, Ben[/TD]
[TD]Fregoso, Stepanie[/TD]
[TD]57[/TD]
[TD]112[/TD]
[TD]19:09[/TD]
[TD]Boys: Varsity[/TD]
[TD]Wasatch[/TD]
[TD]11[/TD]
[TD]57[/TD]
[TD]7[/TD]
[TD]6th Place[/TD]
[TD][/TD]
[TD]485[/TD]
[TD]Patterson, Braden[/TD]
[TD]Nystrom, Sachiko[/TD]
[TD]54[/TD]
[TD]110[/TD]
[TD]19:08[/TD]
[TD]Boys: JV[/TD]
[TD]Wasatch[/TD]
[TD]11[/TD]
[TD]42[/TD]
[TD]4[/TD]
[TD]6th Place[/TD]
[/TR]
[TR]
[TD]486[/TD]
[TD]Jarvis, Caeden[/TD]
[TD]Laureano, Linh[/TD]
[TD]56[/TD]
[TD]113[/TD]
[TD]19:09[/TD]
[TD]Boys: JV[/TD]
[TD]Wasatch[/TD]
[TD]9[/TD]
[TD]44[/TD]
[TD]6[/TD]
[TD]6th Place[/TD]
[TD][/TD]
[TD]259[/TD]
[TD]Barker, Corbyn[/TD]
[TD]Pelley, Vonnie[/TD]
[TD]49[/TD]
[TD]95[/TD]
[TD]18:46[/TD]
[TD]Boys: Varsity[/TD]
[TD]Dixie[/TD]
[TD]10[/TD]
[TD]49[/TD]
[TD]3[/TD]
[TD]8th Place[/TD]
[/TR]
[TR]
[TD]207[/TD]
[TD]Messel, Jordan[/TD]
[TD]Wegman, Edwina[/TD]
[TD]57[/TD]
[TD]114[/TD]
[TD]19:10[/TD]
[TD]Boys: JV[/TD]
[TD]Desert Hills[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]12[/TD]
[TD]1st Place[/TD]
[TD][/TD]
[TD]260[/TD]
[TD]Wells, George[/TD]
[TD]Pool, Jung[/TD]
[TD]50[/TD]
[TD]95[/TD]
[TD]18:46[/TD]
[TD]Boys: Varsity[/TD]
[TD]Dixie[/TD]
[TD]12[/TD]
[TD]50[/TD]
[TD]4[/TD]
[TD]8th Place[/TD]
[/TR]
[TR]
[TD]443[/TD]
[TD]Hall, Hunter[/TD]
[TD]Peer, Kristian[/TD]
[TD]58[/TD]
[TD]115[/TD]
[TD]19:10[/TD]
[TD]Boys: JV[/TD]
[TD]Snow Canyon[/TD]
[TD]9[/TD]
[TD]45[/TD]
[TD]1[/TD]
[TD]8th Place[/TD]
[TD][/TD]
[TD]264[/TD]
[TD]Wilkinson, Tate[/TD]
[TD]Mcquire, Ronda[/TD]
[TD]51[/TD]
[TD]97[/TD]
[TD]18:48[/TD]
[TD]Boys: Varsity[/TD]
[TD]Dixie[/TD]
[TD]11[/TD]
[TD]51[/TD]
[TD]5[/TD]
[TD]8th Place[/TD]
[/TR]
[TR]
[TD]114[/TD]
[TD]Prince, Austin[/TD]
[TD]Gayles, Bret[/TD]
[TD]59[/TD]
[TD]116[/TD]
[TD]19:12[/TD]
[TD]Boys: JV[/TD]
[TD]Cedar[/TD]
[TD]10[/TD]
[TD]NS[/TD]
[TD]8[/TD]
[TD]4th Place[/TD]
[TD][/TD]
[TD]344[/TD]
[TD]Krzymowski, Tim[/TD]
[TD]Ramey, Stanley[/TD]
[TD]56[/TD]
[TD]107[/TD]
[TD]19:07[/TD]
[TD]Boys: Varsity[/TD]
[TD]Manti[/TD]
[TD]12[/TD]
[TD]56[/TD]
[TD]2[/TD]
[TD]9th Place[/TD]
[/TR]
[TR]
[TD]293[/TD]
[TD]Poston, Tatyn[/TD]
[TD]Goold, Barb[/TD]
[TD]60[/TD]
[TD]117[/TD]
[TD]19:12[/TD]
[TD]Boys: JV[/TD]
[TD]Hurricane[/TD]
[TD]11[/TD]
[TD]46[/TD]
[TD]7[/TD]
[TD]5th Place[/TD]
[TD][/TD]
[TD]505[/TD]
[TD]McCullough, Ben[/TD]
[TD]Fregoso, Stepanie[/TD]
[TD]57[/TD]
[TD]112[/TD]
[TD]19:09[/TD]
[TD]Boys: Varsity[/TD]
[TD]Wasatch[/TD]
[TD]11[/TD]
[TD]57[/TD]
[TD]7[/TD]
[TD]6th Place[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]Blake, Luke[/TD]
[TD]Chastain, Chara[/TD]
[TD]61[/TD]
[TD]118[/TD]
[TD]19:13[/TD]
[TD]Boys: JV[/TD]
[TD]Canyon View[/TD]
[TD]12[/TD]
[TD]NS[/TD]
[TD]10[/TD]
[TD]3rd Place[/TD]
[TD][/TD]
[TD]108[/TD]
[TD]Taylor, Harley[/TD]
[TD]States, Sam[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]18:52[/TD]
[TD]Girls: Varsity[/TD]
[TD]Cedar[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]3rd Place[/TD]
[/TR]
[TR]
[TD]374[/TD]
[TD]Finch, Jacey[/TD]
[TD]Ree, Olen[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]19:14[/TD]
[TD]Girls: Varsity[/TD]
[TD]Pine View[/TD]
[TD]12[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]1st Place[/TD]
[TD][/TD]
[TD]111[/TD]
[TD]Webster, Mic[/TD]
[TD]Keffer, Merrill[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]18:45[/TD]
[TD]Girls: Varsity[/TD]
[TD]Cedar[/TD]
[TD]11[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3rd Place[/TD]
[/TR]
[TR]
[TD]254[/TD]
[TD]Carlile, Spencer[/TD]
[TD]Dolby, Tonya[/TD]
[TD]62[/TD]
[TD]119[/TD]
[TD]19:15[/TD]
[TD]Boys: JV[/TD]
[TD]Dixie[/TD]
[TD]9[/TD]
[TD]47[/TD]
[TD]4[/TD]
[TD]7th Place[/TD]
[TD][/TD]
[TD]374[/TD]
[TD]Finch, Jacey[/TD]
[TD]Ree, Olen[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]19:14[/TD]
[TD]Girls: Varsity[/TD]
[TD]Pine View[/TD]
[TD]12[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]1st Place[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[yky]

I don't see any null values in the picture, thus, don't know what to do when there are null values.

 

[mackensteff]

I'm not sure there is a way to indicate them in the table, but if a formula returns "", they tend to be sorted to the top as blank rows. However, I can likely work around the null values by selecting only then cells with shown data. Really, any thing at this point would help.

Thanks!!

 

[yky]

Which one is the primary sorting key? I thought it was school but looking at the sorted result I think division is more like it.

Anyway, here is the code. You can modify it to suit your need.

Sub sorting()

Dim Lrow As Integer 'last non-empty row in column A
Dim rKey1 As Range
Dim rKey2 As Range
Dim rKey3 As Range

'find the last non-empty row in column A
Lrow = Range("A1:L1").Cells(1048576, 1).End(xlUp).Row

'copy column A to L and paste to N1
Range(Cells(1, 1), Cells(Lrow, 12)).Copy Destination:=Range("N1")

'find key1
Set rKey1 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M2").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey1 Is Nothing Then
MsgBox ("Cannot find " & Range("M2") & ". Exiting program.")
Exit Sub
End If

'find key2
Set rKey2 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M4").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey2 Is Nothing Then
MsgBox ("Cannot find " & Range("M4") & ". Exiting program.")
Exit Sub
End If

'find key3
Set rKey3 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M6").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey3 Is Nothing Then
MsgBox ("Cannot find " & Range("M6") & ". Exiting program.")
Exit Sub
End If

'sort by key1, key2, key3, in that order
Range(Cells(1, 14), Cells(Lrow, 25)).Sort _
    Key1:=rKey1, Order1:=xlAscending, _
    Key2:=rKey2, Order2:=xlAscending, _
    Key3:=rKey3, Order3:=xlAscending, _
    Header:=xlYes, dataoption3:=xlSortTextAsNumbers, _
    MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[mackensteff]

Hello,
You are right that Division was the one I used in the example. Your macro works, so I really appreciate that. However, just two little things if you could help on I would appreciate it. I am copying from cells that have formulas, so is there a way to paste only values and formats and not the formulas themselves. Second, is there a way to paste the formatting after the sorting so the row colors are retained from the original copied section.

Finally, and I know I am pushing your goodness, but some of the Column A cells are blank (this is not shown in the sample), but is it possible to not copy those rows that have a blank, or null value in column A.

Thanks!

 

[yky]

Try this. Although I tested it on the example you provided above, which contains no formulae, it should work and not copy formulae.

Sub sorting()

Dim Lrow As Double
Dim rKey1 As Range
Dim rKey2 As Range
Dim rKey3 As Range
Dim i As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Lrow = Cells(1048576, 1).End(xlUp).Row

'filter column A to hide nulls
ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:="<>"

'copy and paste by value
Range(Cells(1, 1), Cells(Lrow, 12)).Copy
Range("N1").PasteSpecial (-4163)

'look for sorting key's position
Set rKey1 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M2").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey1 Is Nothing Then
MsgBox ("Cannot find " & Range("M2") & ". Exiting program.")
Exit Sub
End If

Set rKey2 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M4").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey2 Is Nothing Then
MsgBox ("Cannot find " & Range("M4") & ". Exiting program.")
Exit Sub
End If

Set rKey3 = ActiveSheet.Range("N1:Y1").Find(what:=Range("M6").Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If rKey3 Is Nothing Then
MsgBox ("Cannot find " & Range("M6") & ". Exiting program.")
Exit Sub
End If

'sort
Range(Cells(1, 14), Cells(Lrow, 25)).Sort _
    Key1:=rKey1, Order1:=xlAscending, _
    Key2:=rKey2, Order2:=xlAscending, _
    Key3:=rKey3, Order3:=xlAscending, _
    Header:=xlYes, dataoption3:=xlSortTextAsNumbers, _
    MatchCase:=False, Orientation:=xlTopToBottom

'copy and paste header's format
Range(Cells(1, 1), Cells(1, 12)).Copy
Range(Cells(1, 14), Cells(1, 25)).PasteSpecial (-4122)

'reset Lrow because null rows in column A are not copied
Lrow = Cells(1048576, 14).End(xlUp).Row

'copy and paste formats
Range(Cells(2, 1), Cells(2, 12)).Copy

For i = 2 To Lrow Step 2
Range(Cells(i, 14), Cells(i, 25)).PasteSpecial (-4122)
Next

Range(Cells(3, 1), Cells(3, 12)).Copy
For i = 3 To Lrow Step 2
Range(Cells(i, 14), Cells(i, 25)).PasteSpecial (-4122)
Next

ActiveSheet.Range("$A:$A").AutoFilter
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[mackensteff]

Re: Macro to Copy, Sort, Paste Formats, Ignore Null

Thanks yky!

This almost works, however if there is a blank cell in column A or a formula in column A returns "" (like =if(A>1,"","") then an out of place row is inserted, in otherwise well sorted data.


Its hard to explain but a very simplified view of what I am seeing:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original Data[/TD]
[TD]Sorted Data[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Again any help is appreciated.

 

[yky]

Re: Macro to Copy, Sort, Paste Formats, Ignore Null

I had tested the code before posting and it had worked (for me, anyway). I inserted two blank rows to the data you posted, ran the code, and the pasted area was two rows less than the copied area. In my case, there was no formula. That might be the difference.

Would you post a sample excel workbook to any of the free file sharing site, preferably no password required to download?

 

[mackensteff]

Re: Macro to Copy, Sort, Paste Formats, Ignore Null

I have link a Workbook here https://www.dropbox.com/s/blzj8fturd583i6/Trial.xlsm?dl=0

The first Worksheet is the one I was using the macro on.

Thanks

Mackay

 

[yky]

Re: Macro to Copy, Sort, Paste Formats, Ignore Null

I don't see empty cells in column A. There are cells that contain a hyphen in column A. Are those what you called null?