Macro to Copy, Sort, Past Formats, Ignore Null

M

mackensteff

Board Regular
Joined
Feb 9, 2010
Messages
74
Office Version
  1. 365
Platform
  1. MacOS
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!!


Untitled.png



BibLast, FirstNameDivPlSexPlTimeDivisionSchoolGradeScoreTeamPos.TeamPlaceSort 1stBibLast, FirstNameDivPlSexPlTimeDivisionSchoolGradeScoreTeamPos.TeamPlace
137Green, NoahKober, Maia429018:41Boys: JVCedar103564th PlaceSchool44Blake, LukeChastain, Chara6111819:13Boys: JVCanyon View12NS103rd Place
117Coates, BransonBasnett, Marilu439118:41Boys: JVCedar103674th PlaceSort 2nd49Jarvis, PaulTompson, Eugenie479818:53Boys: JVCanyon View10NS83rd Place
204Sandberg, JackMoree, Lashonda449218:41Boys: JVDesert Hills10NS91st PlaceDivision36Valenzuela, EddieBechard, Emerson5210819:07Boys: JVCanyon View10NS93rd Place
111Webster, MicKeffer, Merrill4418:45Girls: VarsityCedar11413rd PlaceSort 3rd117Coates, BransonBasnett, Marilu439118:41Boys: JVCedar103674th Place
205Hendrix, JaronNale, Kesha459318:46Boys: JVDesert Hills10NS101st PlaceLast, First137Green, NoahKober, Maia429018:41Boys: JVCedar103564th Place
292Dansie, PorterMendoza, Juliet469418:46Boys: JVHurricane103755th Place114Prince, AustinGayles, Bret5911619:12Boys: JVCedar10NS84th Place
259Barker, CorbynPelley, Vonnie499518:46Boys: VarsityDixie104938th Place205Hendrix, JaronNale, Kesha459318:46Boys: JVDesert Hills10NS101st Place
260Wells, GeorgePool, Jung509518:46Boys: VarsityDixie125048th Place207Messel, JordanWegman, Edwina5711419:10Boys: JVDesert Hills10NS121st Place
264Wilkinson, TateMcquire, Ronda519718:48Boys: VarsityDixie115158th Place204Sandberg, JackMoree, Lashonda449218:41Boys: JVDesert Hills10NS91st Place
108Taylor, HarleyStates, Sam5518:52Girls: VarsityCedar11523rd Place254Carlile, SpencerDolby, Tonya6211919:15Boys: JVDixie94747th Place
49Jarvis, PaulTompson, Eugenie479818:53Boys: JVCanyon View10NS83rd Place292Dansie, PorterMendoza, Juliet469418:46Boys: JVHurricane103755th Place
36Valenzuela, EddieBechard, Emerson5210819:07Boys: JVCanyon View10NS93rd Place443Hall, HunterPeer, Kristian5811519:10Boys: JVSnow Canyon94518th Place
500Curtis, NathanKratky, Lilliana5310919:07Boys: JVWasatch104136th Place500Curtis, NathanKratky, Lilliana5310919:07Boys: JVWasatch104136th Place
485Patterson, BradenNystrom, Sachiko5411019:08Boys: JVWasatch114246th Place498Davis, McCallFoulds, Randell5511119:08Boys: JVWasatch114356th Place
498Davis, McCallFoulds, Randell5511119:08Boys: JVWasatch114356th Place486Jarvis, CaedenLaureano, Linh5611319:09Boys: JVWasatch94466th Place
505McCullough, BenFregoso, Stepanie5711219:09Boys: VarsityWasatch115776th Place485Patterson, BradenNystrom, Sachiko5411019:08Boys: JVWasatch114246th Place
486Jarvis, CaedenLaureano, Linh5611319:09Boys: JVWasatch94466th Place259Barker, CorbynPelley, Vonnie499518:46Boys: VarsityDixie104938th Place
207Messel, JordanWegman, Edwina5711419:10Boys: JVDesert Hills10NS121st Place260Wells, GeorgePool, Jung509518:46Boys: VarsityDixie125048th Place
443Hall, HunterPeer, Kristian5811519:10Boys: JVSnow Canyon94518th Place264Wilkinson, TateMcquire, Ronda519718:48Boys: VarsityDixie115158th Place
114Prince, AustinGayles, Bret5911619:12Boys: JVCedar10NS84th Place344Krzymowski, TimRamey, Stanley5610719:07Boys: VarsityManti125629th Place
293Poston, TatynGoold, Barb6011719:12Boys: JVHurricane114675th Place505McCullough, BenFregoso, Stepanie5711219:09Boys: VarsityWasatch115776th Place
44Blake, LukeChastain, Chara6111819:13Boys: JVCanyon View12NS103rd Place108Taylor, HarleyStates, Sam5518:52Girls: VarsityCedar11523rd Place
374Finch, JaceyRee, Olen7719:14Girls: VarsityPine View12731st Place111Webster, MicKeffer, Merrill4418:45Girls: VarsityCedar11413rd Place
254Carlile, SpencerDolby, Tonya6211919:15Boys: JVDixie94747th Place374Finch, JaceyRee, Olen7719:14Girls: VarsityPine View12731st Place

<tbody>
</tbody>
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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.

Image2.png



Image3.png
 
Last edited:
Upvote 0
Re: Macro to Copy, Sort, Paste Formats, Ignore Null

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

Code: 
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
 
Last edited:
Upvote 0
Re: Macro to Copy, Sort, Paste Formats, Ignore Null

WOW! Works just as I was hoping. I really appreciate it!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Multiple IF with individual criteria for rate with results return value 'place' in header
Replies
7
Views
597
Greenbehindthecells
G
R
  • Question
Code to copy then paste certain columns as values
Replies
4
Views
303
Peter_SSs
Peter_SSs
G
  • Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
219
glaccounting
G
P
  • Solved
How to copy and paste the next row VBA Help.
Replies
8
Views
352
James006
James006
P
  • Question
Copying and transposing data from one worksheet to another
Replies
1
Views
348
jdellasala
J

Forum statistics

Threads
1,214,932
Messages
6,122,323
Members
449,077
Latest member
jmsotelo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top