Ranking 1000 Sheets?

[kestefon]

I have about 1000 sheets I need to rank in a workbook. Does anyone know if there's a quick way to do this with VBA? I want each row sorted high-to-low based on the "2012" column...and I'm trying to get the entire rows to move, not just the selection of cells underneath the 2012 column.

I've attached a before/after example. Any help would be greatly appreciated:

RankingExample.xlsx

[micope21]

Please click on attachment

Good luck

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[kestefon]

Thanks, guys.

Any idea how to make that automatically run for all of the sheets at once?

[JOHN H. DAVIS]

The code provided in Post 3 should do it for all sheets in the activeworkbook.

[kestefon]

Hm...it doesn't seem to be working. I even tried selecting the specific sheets I wanted, but it only did it for the current one. Are there any adjustments that have to be made in order for it to run for multiple sheets?

[Alf]

John's code will loop through all sheets in your workbook and sort and rank every sheet.

Alf

[micope21]

John code is better than my code.
My advice is use John code

micope21

[JOHN H. DAVIS]

Hm...it doesn't seem to be working. I even tried selecting the specific sheets I wanted, but it only did it for the current one. Are there any adjustments that have to be made in order for it to run for multiple sheets?

Can you post the code you are using?

[Alf]

Sorry my bad, try this modification to John's code and see if this works better.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Alf is right. I forgot to activate the ws's.

[kestefon]

I've attached a new example file with multiple sheets, along with the code you provided (I changed the name, but haven't modified the code itself).

When I run it it only ranks the sheet I'm currently working on, and the others are unchanged...

RankingExample - MULTIPLE SHEETS.xlsm

Can you try the attachment and let me know whether you're having the same problem?

[JOHN H. DAVIS]

This works on the sample you provided and so does the modification that Alf provided.

Please Login or Register  to view this content.

[kestefon]

Perfect! Thanks for the fix. Out of curiosity, do you know why it wasn't working before?

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. I added ws.activate at the beginning of the loop. Each iteration of the loop you have to activate the spreadsheet. Please comply with Forum Rule No. 9.

Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.