[saidtomuch]

Hi everyone,

I would imagine this question has been asked before but I can't find it.

Basically I have a worksheet which contains approx 180 rows and 60 columns, they are predominately numeric values. What I want to do is use sheet1 (s1) for data input and I want to create a sheet2 (s2) to autosort the data contained in s1 everytime I update the data in s1. I only wish to sort by 1 column value, highest at the top to lowest at the bottom.

I am using ms office 2010.

Many thanks in anticipation.

s2m.

 

[RSpecianjr]

Re: sort on separate sheet

Hey s2m,

You want to duplicate all the data and sort descending based on what column?

Regards,

Robert D Specian Jr.

 

[saidtomuch]

Re: sort on separate sheet

good morning,

I wish to sort using column N duplicating all the data on s1. The workbook consists of 4 worksheets but I am assuming I will need the 1 macro to do each one, just changing the source info for each sort sheet ?

s2m

 

[saidtomuch]

Re: sort on separate sheet

should add I wish to omit rows 1 and 2 from the sort criteria if possible.

Regards

s2m

 

[RSpecianjr]

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

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.

 

[saidtomuch]

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

 

[RSpecianjr]

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

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.

 

[saidtomuch]

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

 

[RSpecianjr]

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

 

[saidtomuch]

Re: sort on separate sheet

Apologies Robert,

Please disregard that last post. It would appear I had the destination sheet incorrectly entered.

It works fine and sorts. However (isn't there always a however), can the formats (numeric and conditional) along with the exact page layout (column widths etc) be copied from the source to the destination. Also is it possible to omit columns J, K and L from the process.

s2m

 

[saidtomuch]

Re: sort on separate sheet

And before I shoot off to bed, What time is it there ? I shall ask 1 more question ( I will probably have more lol). If I include rows 1 and 2 which are text and headers really can I do that without it affecting the sort? And finally it sorts great but each player with a zero value average (probably haven't played any games) appears at the top before those with an average, can these be moved to the bottom or omitted from the sort.

Can't express how grateful I am Robert.

s2m

 

[saidtomuch]

Re: sort on separate sheet

Hi Robert,

Well I did say there would be more questions lol. It's working a treat apart from the aforementioned glitches. I have tested it again this morning on another copy of my real spreadsheet and recognise what might be another problem. When it copies and pastes it moves all the formulas contained within s1 to s2, in effect removing them from s1. Perhaps it should be only the cell values copied and pasted ??

Regards and thankful,

s2m

 

[RSpecianjr]

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.

 

[saidtomuch]

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

 

[saidtomuch]

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

 

[RSpecianjr]

Re: sort on separate sheet

Hey s2m,

Yes, you just need to let me know how you want the data to be combined and the names of the other sheets.

Regards,

Robert

 

[saidtomuch]

Re: sort on separate sheet

Hi Robert,

The same combination has before, copy, paste and autosort. The source worksheets are Mens Premier Averages, Mens Division 1 Averages and Mens Division 2 Averages to be sent to a destination worksheet simply named "a". Column N is once again the autosort and the range is OK.

Regards

s2m

 

[RSpecianjr]

Re: sort on separate sheet

Hey s2m,

Hmm, guess I worded it wrong. Do you want all the data sorted together or are you wanting the data to still be separated but on one worksheet?

Regards,

Robert

 

[saidtomuch]

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

 

[saidtomuch]

Re: sort on separate sheet

Hi Robert,

You know I thought this would be a simple thing to do but it's got me totally flummoxed. I have searched the net and come up with arrays and other things but nothing seems to work. If I write all 3 sheetnames down it onlt copies, pastes and sorts the last. I bet for you it's easy but for me alas another brain twister.

Cheers,

s2m