Hi again Reinaldo,
Your English was certainly not worst then mine, and you understood the problem very well!
I have tried your code.
It does the jobs with a very simple solution.
I will instal it in the real workbook and and compare which one is the fastest.
.....................
I had a few problems while testing, that have nothing to do with the code it-self.
The code contains references to sheet ("Source"), and sometimes to sheet ("source").
I was very surprised that I go no error break.
I started trying with SourCe, SOUrce, and so on, it always worked.
Then I tried the same with the name of the second sheet that must be excluded, using "Problem Description", instead of "Problem description".
This time, the program tried to include that sheet in the sheets to sort.
And for sure, it broke.
Not only that, but when I changed the code back to the proper "Problem description" , it kept breaking at the line:
>> Sheets(nm).Move Before:=Sheets(i)
I had to close the program (without saving), reopen, and then it worked.
Strange...
Do you know why the VBE let me play around with the spelling of "Source", and not of "Problem description"?
Well anyway, I guess it is safer to make sure that the name is always spelled the way it appears in the workbook.
...........................
But if one of my goals was to learn something new, you sure helped me too, with the line:
If Not IsError(Application.Match(sh.Name, Sheets("Source").Range("B:B"),0)) Then
This will help me in another part of my code for this (and others) workbook(s), where I used more complicated code to something similar.
Thank you very much for your attention and time,
Louise
> Hi Reinaldo,
>
> Paul Schreiner has already given me a solution that works very well, and that I have adapted to my real workbook.
> (you could look at a few posts lower).
>
> But I will try your own solution and come back to you.
>
> Thank you for the time you took to help me with this problem.
>
> Louise
>
>
>
>
>> I'm not sure if i have understood (my english is not good), but try:
>>
>>
>>
>> Sub ListSheets()
>>
>> Dim x As Integer
>>
>> x = 100
>>
>> Application.ScreenUpdating = False
>>
>> For Each sh In Sheets
>>
>> If sh.Name <> "Source" And sh.Name <> "Problem description" Then
>>
>> Sheets("source").Range("AA" & x) = sh.Name
>>
>> If Not IsError(Application.Match(sh.Name, Sheets("Source").Range("B:B"),
>> 0)) Then
>>
>> Sheets("source").Range("AB" & x) = Sheets("source").Range("H" &
>> Application.Match(sh.Name, Sheets("Source").Range("B:B"), 0)).Value
>>
>> Else
>>
>> Sheets("source").Range("AB" & x) = sh.[a1].Value
>>
>> End If
>>
>> x = x + 1
>>
>> End If
>>
>> Next
>>
>> Sheets("Source").Activate
>>
>> Sheets("Source").Range("AA100:AB" & 100 + Sheets.Count).Select
>>
>> Sheets("Source").Sort.SortFields.Clear
>>
>> Sheets("Source").Sort.SortFields.Add Key:=Range( _
>>
>> "AB100:AB" & 100 + Sheets.Count), SortOn:=xlSortOnValues,
>> Order:=xlAscending, DataOption:= _
>>
>> xlSortNormal
>>
>> With ActiveWorkbook.Worksheets("Source").Sort
>>
>> .SetRange Range("AA100:AB112")
>>
>> .Header = xlGuess
>>
>> .MatchCase = False
>>
>> .Orientation = xlTopToBottom
>>
>> .SortMethod = xlPinYin
>>
>> .Apply
>>
>> End With
>>
>> i = 3
>>
>> For j = 100 To 100 + Sheets.Count - 2
>>
>> 'For Each sh In Sheets
>>
>> nm = Sheets("Source").Range("AA" & j)
>>
>> If nm <> "" Then
>>
>> Sheets(nm).Move Before:=Sheets(i)
>>
>> i = i + 1
>>
>> End If
>>
>> Next
>>
>> Sheets("Source").Range("AA100:AB" & 100 + Sheets.Count).Clear
>>
>> Sheets("Source").Activate
>>
>> Application.ScreenUpdating = True
>>
>> End Sub
>>
>>
>>
>>
>>
>> [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
>>
>>
>>
>
>
>
> ------------------------------------
>
> ----------------------------------
> 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 (10) |
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