Copying to another sheet and missing out lines

[rireland]

My main sheet has the results of a competition in rows 1-100. 75% of the results won't count for the jumpoff ( its a horsey competition) and I want to automatically copy the successful details to another sheet. I've worked out how to to omit the unsuccessful results by using an IF statement ( IF cellx is > x then copy ) but I still end up with 100 rows - with 25 results and 75 blank rows. How can I copy but compact it down to 25 rows.

As a second question I need to sort the results. I know how to do a manual sort & I even worked out a macro so one keystroke can do the sort. It woruld be better if the sort would be automatic whenever there is a change in the scores.

 

[JB_Scotland]

Hi Richard I've sent you a private message.

If you wan't to switch off the macro delete it, draw a button on your scores sheet by going to menu View / Toolbars / Control Toolbox - Command Button draw it onto your sheet with the mouse. Double click the button you have just drawn, and paste the code below in to the button click macro between Private Sub CommandButton1_Click() and the End Sub lines. Ive updated the code with your new sort ranges.

Worksheets("Results").Range("A3:N50").Sort Key1:=Worksheets("Results").Range("F3"), Order1:=xlAscending, Key2:=Range("A3"), Order2:=xlAscending, Header:= xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

This way the code will run once when you click the button, not each time you change a cell.

JB.

 

[JB_Scotland]

Hi Richard,

Oops, just seen an error in my code. Second sort key range was wrong and should have been prefixed with Worksheets("Results"). This would have caused the error you were talking about.

New code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Results").Range("A3:N50").Sort Key1:=Worksheets("Results").Range("F3"), Order1:=xlAscending, Key2:=Worksheets("Results").Range("A3"), Order2:=xlAscending, Header:= xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

Put that in your Scores Worksheet code. Hope tht now works.

JB.