How write sheets array to sort multiple worksheet in the column

Faridwahidi.
Please read the Forum Rules regarding Code Tags (#3).

Please Login or Register  to view this content.

sorry, I am a new forum member. code tags done, can you help me to resolve my problem

Try this on a copy of your workbook

Please Login or Register  to view this content.

Hi,

I have copy and pasted to my VBA template but when I click to run the programme, nothing happened.


what i want to do is to sort out Range("A2:AN") with bottom limit e1 = Range("I15000").End(xlUp).ROW


for column M and N in descending order , then do numbering in column "O"

next, column N and M in descending order, then do numbering in column "P"


regards,
farid

farid
It works for me. It sorts columns A to N in descending order starting at row 2.
Are the sheets named properly, like no trailing spaces?
Don't know what you mean by "do numbering" (in column O and P)
What does "Bottom limit e1 = Range(I15000").End(xlUp).Row mean?
I interpeted as meaning that we use Column I to find the last used cell. Is that right?
John

Please Login or Register  to view this content.

Hi Jolivanes,

Yes, your programme works for the 1st sorting only, 1st key: Column("M") descending, 2nd Key: Column("N") descending


But does work for the next sorting 1st key change to: Column("N") descending, 2nd Key: Column("M") descending




After the 1st sorting, I need to fill numbering in column("O") which defined as follow
Range("O2").FormulaR1C1 = "1"
Range("O3").FormulaR1C1 = "2"
Range("O2:O3").AutoFill Destination:=Range("O2:O" & Range("I" & Rows.Count).End(xlUp).ROW)


Then, after the 2nd sorting, I need to fill numbering in column("O") which defined as follow
Range("P2").FormulaR1C1 = "1"
Range("P3").FormulaR1C1 = "2"
Range("P2:P3").AutoFill Destination:=Range("P2:P" & Range("I" & Rows.Count).End(xlUp).ROW)


I attached herewith my working files for your verification. you can run my programme sub_sort() to see what happened


Thanks for willing to assist me. really appreaciate.

Regards,
Farid

Sorry, does NOT work for the next sorting

I think I might have just got it.
You want two macros, not just one.
One for viewing "Uptime Rank" and the other macro for viewing "Useage Rank", is that right?
Let us know.

Hi jolivanes,


Yes, that is what i want to create. to avoid repeating script.

Hopefully u can assist me to simplify the script.


Thanks for your kind assistance.


Regards,
Farid.

Try the attached.
It has 2 macros, one for each scenario.
I took the array out because you have a different amount of sheets as mentioned in your first post.
Let us know if this is what you had in mind.
Good luck
John

Hi ,

Thanks, you are excellent, It works

One more question to ask,

Since you have created For i = 1 To Sheets.Count to represent all worksheets in the workbook.

If there are any worksheets in the workbook should be excluded from perform sorting, How to write the sheets array,



Thank you.

You can put all the sheets in an array but if one of the sheets is missing it will error.
If you have more sheets than what is in an array, they will not be included.

You can also write code in which it excludes certain sheets by name or by index number.
For instance all sheets except the first sheet or the first two sheets etc.
Or all sheets except a sheet named "HONK" and a sheet named "PHIL".

You could also put all the sheets in an array beforehand, with code.

Or maybe the best solution, by the sound of it, is to just run it on a selected (active) sheet.

Let us know what you want but remember, if it is hard coded it will error when it changes.

Please Login or Register  to view this content.

Hi Jolivanes,

Thanks you. good idea


What in my mind is actually to use my previous script, which has been amended from direct record, can you assist to make it works.

Please Login or Register  to view this content.

If you want to continue with code that is not working, I would suggest that Google is your best friend.
Twice you have

Please Login or Register  to view this content.

Using .End(xlUP) is obsolete (see the code I provided for a better solution)
Selecting as in

Please Login or Register  to view this content.

is frowned upon if not really required, as it is here.

Is this what you wanted?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi jolivanes,

That is what I wanted,

Thanks for your assistance & willingness to tech me how to write a good script.

I am a newbie, have no proper learning of VBA. Most of my script are edited from direct record, that is how I learnt.

again thanks,

Regards,
Farid