Louise,
I've done something similar.
I have workbooks with from 2 to 130 sheets.
Some of the sheets are "History" sheets, while other sheets are different form
templates.
I wanted the history sheets to be placed so that the LATEST sheet is first and
the rest are sorted (in decreasing sheet numbers) at the end of the rest of the
sheets.
Then, the other sheets are sorted by sheet number.
I did it by loading (2) arrays.
If the sheet name already was in the first array, then it was NOT loaded into
the second array.
I then sorted the arrays and moved the sheets in the order that they appeared in
the array.
Let me take a look and I'll get back to you.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: Louise Gariépy <garilou@cgocable.ca>
To: ExcelVBA@yahoogroups.com
Sent: Wed, May 1, 2013 7:05:35 AM
Subject: Re: [ExcelVBA] Problem: sort sheets according to two sets of criteria
Hi, David,
Thank you for trying.
But this does not solve my problem.
Or in other words, yes this list that you suggest was generated in the sheet
"Problem description". (Columns M and N)
I was hoping to find a method to go directly from the columns B and C, (Criteria
1) and E and F (criteria 2) to the sheet sorting process.
I guess if the whole cannot be done in one step, I will keep on with my not too
elegant program that I have started, generating all columns that were
illustrated in that sheet.
From there on, it will not be too difficult with the:
sheets(s).move after.
to move the sheets with the largest numbers to the end.
I guess that if I could not find the solution while googling for so long, it was
because there was no way to do it.
Thanks anyway.
This gave me the opportunity to say hello to everyone ;-)
Louise
Le --27042013 à 20:29, David Grugeon <yahoo@grugeon.com.au> a écrit :
> Hi Louise
>
> I think the easiest way to do this is to create a list of the sheet names
> in order, then step through this list in order moving each sheet to the
> end. I think you would use something like
>
> Sheets(s).move after:=Sheets(Sheets.Count)
>
> Put this in a loop which picks up the next sheetname as s.
>
>
> On 28 April 2013 09:27, garilou <garilou@cgocable.ca> wrote:
>
>> Hi group, and all the genius programmers!
>>
>> I have not been asking much for quite a long time, but I keep programming
>> almost every day.
>>
>> David has put on the group page a sample sheet that I have prepared to
>> better explain what I am trying to do.
>> As I told David, I have Googled for days, and all what I found were
>> programs to sort alphabetically, which is not what I need.
>> ==============================
>>
>> I have a workbook with more almost 40 data sheets.
>>
>> I must rearrange those sheets at least once a day: when there were only 5
>> to 10, I made it manually, but it took a long time, and I made so many
>> mistakes that the rest of the tasks did not work.
>>
>> The sheets must be ordered according to 2 sets of criteria.
>>
>> Between 1 and maximum 10 sheets must be ordered first, in an order
>> determined on another sheet (« source »). (Criteria 1)
>>
>> The other ones must be ordered according to a number (between 20 and 100)
>> that I have set in cell A1 from every sheet of the sample book. (Criteria 2)
>>
>> For the sake of this sample, I have created those values with a random
>> formula.
>>
>> "In real life", this could be more complicated, because some sheets could
>> have the same value in cell A1, but those could be placed side by side,
>> with no special order.
>>
>> The names on sheet «Source » are different every day, as well as the value
>> in Cell A1 of the other sheets.
>>
>> I do not expect any one to write the whole program for me, but if I could
>> get some methods to follow, steps to go through, some keywords that I could
>> Google that would help me find a solution.
>>
>> Then I might try and come back with more specific questions.
>>
>> Thank you to all who will be so kind to look at this.
>>
>> Louise
>>
>>
>>
>>
>> ------------------------------------
>>
>> ----------------------------------
>> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
>> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
>> the ARCHIVES for EXCEL VBA.
>>
>> ----------------------------------
>> Visit our ExcelVBA group home page for more info and support files:
>> http://groups.yahoo.com/group/ExcelVBA
>>
>> ----------------------------------
>> More free tutorials and resources available at:
>> http://www.mousetrax.com
>>
>> ----------------------------------Yahoo! Groups Links
>>
>>
>>
>>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here:
>http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES
>for EXCEL VBA.
>
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar