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]

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

I ran the code on the spreadsheet you uploaded. The pasted area has three rows less than the copied area.

 

[yky]

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

Remember the primary key is school, not division. There are three rKeys in the code. You can change them to set the priority of the keys.

 

[mackensteff]

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

I wonder if using a Mac is giving me different results. I got rid of the hyphen in the first column, but originally some of them may appear blank, but had a formula that returned "". Below is what I get, you can see row 31 in the returned results isn't sorted correctly. The bottom image is what I see happen at the bottom of the spreadsheet.

 

[yky]

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

OK. I see what the mistake was. Try this:

Sub test()

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
ActiveSheet.Range(Cells(1, 1), Cells(Lrow, 12)).Copy
ActiveSheet.Range("N1").PasteSpecial (-4163)
ActiveSheet.Range("$A:$A").AutoFilter

'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


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[mackensteff]

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

WOW! Works just as I was hoping. I really appreciate it!!!