[SOLVED] sort on separate sheet

Re: sort on separate sheet

Hey s2m,

Sorry for the delay, I had some stuff pop up at work.

You will want to use a macro for it due to the amount of data.

Once you have the workbook open you can hit ALT+F11. This will open the VBA Editor. You need to click Insert at the top then New Module. Paste the following code in there:

Sub autosort()

Worksheets("s1").Range("A3:BH183").Copy
Worksheets("s1").Range("A3").PasteSpecial Paste: xlPasteValues
ActiveWorkbook.Worksheets("s2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("s2").Sort.SortFields.Add Key:=Range("N3"), SortOn _
:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("s2").Sort
.SetRange Range("A3:BH183")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

Click to expand...

To test it, hit the play button at the top. It will copy all your data from s1 to s2 then sort s2 descending based on column N. This also assumes there are no headers in row 3.

You can then set the macro to a keyboard shortcut if you want.

View>Macros>View Macros

Select autosort then hit options and put in the shortcut you want. I usually use Ctrl+Q because that isn't used for anything else.

Regards,

Robert D. Specian Jr.

Re: sort on separate sheet

Hi RS and many thanks for getting back to me,

Copied, pasted and changed sheet headers to relevant names. I then clicked on run (continue, F5, i believe they are the same) and it came up with a compile error: Invalid use of property with the xlPastevalues highlighted.

I have 4 sheets to sort, will I have to create a macro for each ?

Regards

s2m

Re: sort on separate sheet

oops sorry, thats what I get for writing it from memory haha

Sub autosort()

Worksheets("s1").Range("A3:BH183").Copy
Worksheets("s1").Range("A3").PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Worksheets("s2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("s2").Sort.SortFields.Add Key:=Range("N3"), SortOn _
:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("s2").Sort
.SetRange Range("A3:BH183")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Click to expand...

That should work.

If each worksheet has another worksheet for it, i would use different macros for each one. Just easiest that way.

Regards,

Robert D. Specian jr.

Re: sort on separate sheet

Well I think we are getting there Robert,

When I tried to run the macro it came up with a run time error 9 which when "debugged" highlighted the line

ActiveWorkbook.Worksheets("Mens Premier Averages Sorted").Sort.SortFields.Clear

However, when I exited and returned to excel the macro seems to indentify, highlight and copy the data within sheet1 (Mens Premier Averages), however it doesn't seem to paste it to sheet2 (Mens Premier Averages Sorted) automatically. It asked me to choose destination and press enter. When I did this all the data was copied but not sorted for some reason, any ideas my friend.

Much obliged to you for your very kind assistance.

s2m

Re: sort on separate sheet

Okay, try deleting that line of code. The sorting happens after that line.

Also, Worksheets("s1").Range("A3").PasteSpecial Paste:=xlPasteValues

should be

Worksheets("s2").Range("A3").PasteSpecial Paste:=xlPasteValues

My mistake again. Just one of those days haha

Re: sort on separate sheet

Hey s2m,

I live in Louisiana, USA so it is Central time zone -6 GMT.

Well, the xlpastevalues should only paste values and not do anything with the formulas. Perhaps someone else with excel 2010 can debug it? I have 2007 and it works a charm for me haha.

Here is what I would do for formatting, if you want it the same for both. Right click s1 tab and click copy, it should create a new sheet called s1 (1). Just delete s2 and rename s1(1) to s2. It will be an exact copy of s1. Then clear out the data from rows 3 down... just select them and press delete (do NOT right click and delete or else this will remove formating too). Not sure if the conditional formats will transfer.

It is possible to remove J, K, and L from the process but would be easier if you just hid those columns. If you truly need them removed, let me know and I'll work on it.

Are the zero's formulas? How are they formatted? Descending order should put numeric zeros at the bottom of the list.

Regards,

Robert D. Specian Jr.

Re: sort on separate sheet

Hi Robert,

Well if I say so myself, I gave myself a big pat on the back because you are correct, the pastevalues are correct has you point out. Anyway while you slept I copied the s1 sheet and hid the columns has you suggest. So all is good.
The only thing is these zero values being at the top before the positive values. Has you suggest they are formula generated with 1 of the formulas being:

=IF(SUM(P9:AQ9)=0,"",SUM(P9:AQ9)/H9)

So very nearly there my friend.

s2m

Re: sort on separate sheet

Robert I've done it !!

Just changed the formula to return 1 instead of a blank cell, now when they sort all the 1s are at the bottom. GREAT STUFF !!

Now 1 final question, can I collate 4 source sheets to have 1 sheet destination in 1 macro to carry out the same copy and sort ?

A great start to the weekend and it's all thanks to you for your kind help !!

Much obliged

s2m

Re: sort on separate sheet

Hi Robert,

Yes I want the 3 sheets all sorted together. I have set up sheets for the individual divisions and want 1 to sort all 3 together. Information is as the previous post.

One other question, macro security ?? Under macro settings I have it set at Enable all macros which isn't recommended, any suggestions ??

Regards,

s2m