Pozdrav,
Zanima me kako sortirati excel worksheeto-ove by name? Imam ih oko 30, i sve mi je razbacano!
Hvala
Zanima me kako sortirati excel worksheeto-ove by name? Imam ih oko 30, i sve mi je razbacano!
Hvala
Kako sortirati excel worksheet po imenu?
Zaključano
Zakačeno
Ok, kopirao sam CODE koji je postao "brz na tastaturi" i code. Znaci pritisnio sam ALT + F11 i kopirao code! Kad sam to napravio sjevao sam i ponovno pokrenio file, ali nije se nista desilo?
U temi koju timmy preporučuje radi se Sort ali nad imenima listova prethodno upisanih u ćelije.
Code u nastavku, preuzet sa Ozgrid.com/Sort-Sheets
Code u nastavku, preuzet sa Ozgrid.com/Sort-Sheets
Code:
Sub SortSheets()
Dim lCount As Long, lCounted As Long
Dim lShtLast As Long
Dim lReply As Long
lReply = MsgBox("To sort Worksheets ascending, select 'Yes'. " _
& "To sort Worksheets descending select 'No'", vbYesNoCancel, "Ozgrid Sheet Sort")
If lReply = vbCancel Then Exit Sub
lShtLast = Sheets.Count
If lReply = vbYes Then 'Sort ascending
For lCount = 1 To lShtLast
For lCount2 = lCount To lShtLast
If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then
Sheets(lCount2).Move Before:=Sheets(lCount)
End If
Next lCount2
Next lCount
Else 'Sort descending
For lCount = 1 To lShtLast
For lCount2 = lCount To lShtLast
If UCase(Sheets(lCount2).Name) > UCase(Sheets(lCount).Name) Then
Sheets(lCount2).Move Before:=Sheets(lCount)
End If
Next lCount2
Next lCount
End If
End Sub
Sub SortSheets()
Dim lCount As Long, lCounted As Long
Dim lShtLast As Long
Dim lReply As Long
lReply = MsgBox("To sort Worksheets ascending, select 'Yes'. " _
& "To sort Worksheets descending select 'No'", vbYesNoCancel, "Ozgrid Sheet Sort")
If lReply = vbCancel Then Exit Sub
lShtLast = Sheets.Count
If lReply = vbYes Then 'Sort ascending
For lCount = 1 To lShtLast
For lCount2 = lCount To lShtLast
If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then
Sheets(lCount2).Move Before:=Sheets(lCount)
End If
Next lCount2
Next lCount
Else 'Sort descending
For lCount = 1 To lShtLast
For lCount2 = lCount To lShtLast
If UCase(Sheets(lCount2).Name) > UCase(Sheets(lCount).Name) Then
Sheets(lCount2).Move Before:=Sheets(lCount)
End If
Next lCount2
Next lCount
End If
End Sub
Potpis
Have you found helpful examples, solutions or ideas-in-work, please consider the
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!
@3okc, ovaj code sa Ozgrid.com javlja neku grešku.
ovaj code funkcionira i sortira sheets
( http://randombroad.wordpress.c...1/how-to-sort-sheets-in-excel/ )
ovaj code funkcionira i sortira sheets
( http://randombroad.wordpress.c...1/how-to-sort-sheets-in-excel/ )
Code:
Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub
Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub
Ne znam koju to tačno grešku javlja, evo, kod mene radi bez greške!
Sem toga, ovaj listing koji si postavio je skoro pa identičan: postoje minorne razlike u sintaksi ali je logika ista. U drugom primeru su promenljive tipa Integer u prvom su Long i drugi primer ima jedan prolazak manje kroz unutrašnju petlju što je sve 'plus' za uštedu na resursima i optimizovanje brzine, ali je, opet kažem, suštinski ovo jedan isti program, sa sve upitnikom YES/NO/CANCEL na početku, za vrstu sortiranja.
Takođe, oba programa rade i nad drugim otvorenim sveskama, bez problema, tako da su dobri kandidati za ADDIN ili za ubacivanje u kolekciju makroa. U tom slučaju, istakao bih i još jednu razliku: prvi program uvek ostavlja aktivan 'prvi list sa leva' dok je u drugom programu to uvek drugi po redu. Ovo sigurno ima veze sa prolaskom manje u petlji, što, iako možda jeste 'školskije' i tačnije, ipak jednako iritira, tako da bih ja ovde samo dodao red u kome bi se aktivirao list iz kog smo pokrenuli sortiranje.
Sem toga, ovaj listing koji si postavio je skoro pa identičan: postoje minorne razlike u sintaksi ali je logika ista. U drugom primeru su promenljive tipa Integer u prvom su Long i drugi primer ima jedan prolazak manje kroz unutrašnju petlju što je sve 'plus' za uštedu na resursima i optimizovanje brzine, ali je, opet kažem, suštinski ovo jedan isti program, sa sve upitnikom YES/NO/CANCEL na početku, za vrstu sortiranja.
Takođe, oba programa rade i nad drugim otvorenim sveskama, bez problema, tako da su dobri kandidati za ADDIN ili za ubacivanje u kolekciju makroa. U tom slučaju, istakao bih i još jednu razliku: prvi program uvek ostavlja aktivan 'prvi list sa leva' dok je u drugom programu to uvek drugi po redu. Ovo sigurno ima veze sa prolaskom manje u petlji, što, iako možda jeste 'školskije' i tačnije, ipak jednako iritira, tako da bih ja ovde samo dodao red u kome bi se aktivirao list iz kog smo pokrenuli sortiranje.
Potpis
Have you found helpful examples, solutions or ideas-in-work, please consider the
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!
Citat:
3okc: Ne znam koju to tačno grešku javlja, evo, kod mene radi bez greške!
Zašto nemam pojma, kada sam neki dan probavao javljao mi je grešku u ovom dijelu koda
Code:
lReply = MsgBox("To sort Worksheets ascending, select 'Yes'. " _
& "To sort Worksheets descending select 'No'", vbYesNoCancel, "Ozgrid Sheet Sort")
lReply = MsgBox("To sort Worksheets ascending, select 'Yes'. " _
& "To sort Worksheets descending select 'No'", vbYesNoCancel, "Ozgrid Sheet Sort")
Stoga još jednom izvinjenje, u pravu si
