tri onglet selectif

[archi]

Bonjour,

J'aimerai réaliser une macro qui puisse me permettre de trier par ordre alphabétique certains onglets d'un classeur (sauf ceux citer dans la macro "Données").
j'ai ici deux codes qui peuvent servir de base de travail, mais je n'arrive pas a les fusionner pour obtenir le code adéquate !!

merci de m'aider
bye

Sub Données()

Dim ws As Worksheet

For Each ws In Worksheets
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then
'en rouge a supprimer
[color=red]ws.Range("A70:H70").AutoFill Destination:=ws.Range("A4:H70"), Type:=xlFillDefault
ws.Range("J70:M70").AutoFill Destination:=ws.Range("J6:M70"), Type:=xlFillDefault[/color]
End If
Next ws
End Sub

Sub tri_onglet()
   Dim I As Integer, J As Integer, K As Integer
    Application.ScreenUpdating = False
    For I = 1 To Sheets.Count
        J = I
        For K = I + 1 To Sheets.Count
            If Sheets(K).Name < Sheets(J).Name Then J = K
        Next K
        If J <> I Then Sheets(J).Move Sheets(I)
    Next I
    Application.ScreenUpdating = True
End Sub

 

[archi]

Re : tri onglet selectif

personne pour m'aider ?
bye

 

[pierrejean]

Re : tri onglet selectif

A tester

[/COLOR]
[COLOR=black][/COLOR] 
 
Sub tri_onglet()
 
Dim I As Integer, J As Integer, K As Integer
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then

J = I
For K = I + 1 To Sheets.Count
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then
'en rouge a supprimer

If Sheets(K).Name < Sheets(J).Name Then J = K
Next K
If J <> I Then Sheets(J).Move Sheets(I)
Next I
end if
end if
Application.ScreenUpdating = True
End Sub

 

[archi]

Re : tri onglet selectif

Merci PierreJean,

je viens de constater un bug:

erreur de compilation: Next sans for (en ligne Next K)

bye

 

[pierrejean]

Re : tri onglet selectif

re

exact !!

a nouveau a tester

Sub tri_onglet()
 
Dim I As Integer, J As Integer, K As Integer
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then
 
J = I
For K = I + 1 To Sheets.Count
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then
'en rouge a supprimer
If Sheets(K).Name < Sheets(J).Name Then J = K
end if
Next K
If J <> I Then Sheets(J).Move Sheets(I)
Next I
end if
Application.ScreenUpdating = True
End Sub

 

[archi]

Re : tri onglet selectif

toujours un bug de compilation: Next sans for (mais en ligne Next I)
bye

 

[pierrejean]

Re : tri onglet selectif

re

on progresse !!

Sub tri_onglet()
 
Dim I As Integer, J As Integer, K As Integer
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then
 
J = I
For K = I + 1 To Sheets.Count
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then
'en rouge a supprimer
If Sheets(K).Name < Sheets(J).Name Then J = K
end if
Next K
If J <> I Then Sheets(J).Move Sheets(I)
end if
Next I
Application.ScreenUpdating = True
End Sub

de l'utilité des fichiers exemples !!!!!

 

[archi]

Re : tri onglet selectif

ok voici un fichier allégé correspondant
par ailleurs toujours un bug
bye

 

[pierrejean]

Re : tri onglet selectif

re

Sub tri_onglet()
 
Dim I As Integer, J As Integer, K As Integer, ws As Worksheet
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
[COLOR=red]Set ws = Sheets(I)[/COLOR]
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then
 
J = I
For K = I + 1 To Sheets.Count
If Not ws.Name = "Tables" And Not ws.Name = "Base" And Not ws.Name = "Individuel" And Not ws.Name = "Tableau" _
And Not ws.Name = "Perf et Contre" And Not ws.Name = "Brulage" And Not ws.Name = "Eq1" And Not ws.Name = "Eq2" _
And Not ws.Name = "Eq3" And Not ws.Name = "Eq4" And Not ws.Name = "Eq5" And Not ws.Name = "Eq6" And Not ws.Name = "Eq7" _
And Not ws.Name = "Eq8" And Not ws.Name = "Feuil6 Eq1" And Not ws.Name = "Feuil6 Eq2" And Not ws.Name = "Feuil6 Eq3" _
And Not ws.Name = "Feuil6 Eq4" And Not ws.Name = "Feuil6 Eq5" And Not ws.Name = "Feuil3 Eq6" _
And Not ws.Name = "Feuil3 Eq7" And Not ws.Name = "Feuil4 Eq8" And Not ws.Name = "Exemple" Then
If Sheets(K).Name < Sheets(J).Name Then J = K
End If
Next K
If J <> I Then Sheets(J).Move Sheets(I)
End If
Next I
Application.ScreenUpdating = True
End Sub

 

[archi]

Re : tri onglet selectif

merci pour cette macro PierreJean...cependant tu peux télécharger mon fichier complet à l'adresse suivante:


pour constater une erreur de tri des onglets. En effet, la macro tri meme les onglets non concernés ??? ou est le prob ??

bye

 

[pierrejean]

Re : tri onglet selectif

re

je crois qu'il n'y a pas erreur de tri !!

en effet la macro n'intervient pas sur certaines feuilles (elles n'evoluent pas)

ce que tu souhaiterais peut-etre (et alors il aurait fallu le preciser ou bien le faire manuellement ) c'est leur assigner une place determinée ?

quant a celle qui sont concernées , si tu les permutes la macro les reclasse bien

j'ai tout de même un peu simplifié l'ecriture

http://pierrejean6.free.fr/Progressions

 

[archi]

Re : tri onglet selectif

oui justement les onglets concerné par le tri, j'aimerai les positionner en dernier
de liste (possible?)
par ailleurs je n'arrive pas ouvrir ton fichier !!

bye

 

[pierrejean]

Re : tri onglet selectif

re

voila une version qui classe a partir de la 24eme feuille et renvoie les feuilles a ne pas classer au debut

teste et dis-moi (si probleme on doit pouvoir remplacer 24 par 1)

http://pierrejean6.free.fr/Progressions.zip

 

[archi]

Re : tri onglet selectif

impeccable Pierrejean tu as trouvé la bonne solution...super !!
dernière chose (je t'embête encore un peu)
comment intégrer cette macro à celle intitulé "Remplacer joueur" ??

merci d'avance
bye

 

[pierrejean]

Re : tri onglet selectif

bonsoir

le plus simple est de mettre dans la macro Remplacer joueur

 call tri_onglet