Auto Sort

[Diesel13]

Hi everyone, back again, you guys helped me a lot yesterday.

My problem this time is in regards to auto sorting a table as data is changed in another worksheet and is auto copied within the said table.

I.E. I need the Fax page (second last), the table has to auto sort based on column " C ". This is easily done using custom sort but can it be done automatically ? I want to lock the page so nobody modifies it.

I attached a copy of my work so far, using Excel 2007.

All the sheets are locked except " Player DATA " and " Fax ".

The password is " Moose ".



Here is what I found . . .

here is another post in this forum that is exactly what I need but cannot get it to work.

http://www.excelforum.com/excel-2007...o-sorting.html

and here is a copy of the code I recorded using macro recorder ( Please be patient as I'm driving blind here )

Please Login or Register  to view this content.

so to recapitulate, I need the Fax page to update (auto sort) when the data is changed in the " Player DATA " worksheet.

[Diesel13]

does it have to be done using macros ?

[JBeaucaire]

Yes, macros would be simplest. I'll offer that.

1) Right-click on the FAX tab and select VIEW CODE
2) Paste in this sheet activation macro:

Please Login or Register  to view this content.

3) Close the editor
4) Save your sheet as a macro-enabled workbook (*.xlsm)
5) Now each time you bring up the FAX sheet onscreen it will reassert the list in ascending order.

[Diesel13]

Wow, that was amazing, that is exactly what I wanted !

now . . . how do I make it work when the page is protected ?

I assume a macro that would unprotect, make the changes then reset the protection ?

Working on it . . .

if you know it, I would appreciate.

Thanks again, 20 hours of work on this and you just popped it out !

regards, Mike.

[JBeaucaire]

When you want to let macros free to do what they want on protected sheets, give them global permission one-time when you first open the workbook, then you don't have to make every macro deal with it.

Put this in the ThisWorkbook module, it will make the Player DATA sheet protected from users, but not from macros.

Please Login or Register  to view this content.

Of course, set your own password...

[Diesel13]

Thanks a lot, was almost perfect, here is what I put in . . .

Please Login or Register  to view this content.

and it works flawlessly !

Thanks a million

here is a copy of my finished work so it may help others who may have similar needs . . .

I would personally like to thank Daddylonglegs and JBeaucaire for the great help.

Everyone is authorized to download, use and modify this for their own purpose, I would only ask that you do it for free if you get it for someone else.

So to recapitulate, this is a Pool ( Billiard ) league spreadsheet. It is made for expandability in mind. All the DATA is entered in the " Player DATA " sheet. The info to all the pages is linked from there. All the calculations are made in the " Calculations " sheet and there is a fax sheet that is made to be printed on a regular sheet of paper for faxing. There are headers and there is a background image on all the player's sheets (inserted as a header) so it prints. ( Hit Print Preview to see it).

it is made so every player has an individualized sheet printed on one side and " Player DATA " printed on the other side using standard legal paper ( 8 1/2" x 14" ).

It is made with Excel 2007 Macro's enabled so if it don't work for you, enable Macros ?

Thanks again everyone this has been a fantastic adventure as a few days ago I did not have a clue how to do this but through patience, trial and error, a whole lot of reading and some help from the above mentioned, here is the fruit of my labor.

BTW, it's in French so you may need to translate some but heh, cannot be perfect in every ways !

Thanks again,
Mike.

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

[Diesel13]

Sure thing and as for the Reputation Feedback, don't worry my friend, it's already done.

Mike.

[zhernuadi]

Yes, macros would be simplest. I'll offer that.

1) Right-click on the FAX tab and select VIEW CODE
2) Paste in this sheet activation macro:

Please Login or Register  to view this content.

3) Close the editor
4) Save your sheet as a macro-enabled workbook (*.xlsm)
5) Now each time you bring up the FAX sheet onscreen it will reassert the list in ascending order.

hi,

I have similar problem with this, only that the data source is available in another workbook instead in another worksheet..
how should I modify the code to get the result?

thanks in advance

[arlu1201]

zhernuadi,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.