XL 2013 VBA - Mise à jour des onglets selon une liste

[AntoineM]

Bonjour le forum,

J'ai un fichier qui contient plusieurs onglets (cf. Pièce jointe). Dans ce fichier, il y a plusieurs onglet dont le nom peut être : de type numérique, date ou texte.

J'ai une liste de nom dans ma colonne 1 (de la feuille 'Info') et j'ai crée une macro qui crée les onglets selon les nom présent dans cette plage.

Le problème : J'aimerais que cette macro 'mette à jour' les onglets, pour supprimer les onglets dont le nom n'apparaît pas dans la liste. Mais ce que j'ai écris supprime tous les onglets avant de les recréer.

Sub MAJ_Ongl()

Application.ScreenUpdating = False


 Dim Sh As Worksheet
 Dim Ws As Worksheet
 Dim LastLig As Long
 Dim i As Integer
 Dim j As Integer
 
j = 1
 
For Each Ws In Application.Worksheets
     If Ws.Name = Sheets("Info").Range("A" & j) Then
     j = j + 1
     Else
     Application.DisplayAlerts = False
         If Ws.Name <> "Info" And Not Ws.Name Like "Data" And Not Ws.Name Like "#*" Then
     Ws.Delete
     j = j + 1
         End If
     Application.DisplayAlerts = True
     End If
 
Next Ws
  
 With Sheets("Info")
     LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
     For i = LastLig To 1 Step -1
         On Error Resume Next
         Set Sh = Sheets(CStr(.Range("A" & i).Value))
         On Error GoTo 0
         If Sh Is Nothing Then
             ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
             ActiveSheet.Name = CStr(.Range("A" & i).Value)
         Else
             Set Sh = Nothing
         End If
     Next i
 End With
 
Sheets("Info").Activate

Application.ScreenUpdating = True
 
End Sub

Il y a plein de codes différents dans le fichier car j'ai exploré pas mal de piste pour tester. Actuellement je suis entrain de voir avec ParamArray ou les noms de plages mais je bloque.

Merci à tous,

AntoineM

 

[DoubleZero]

Re : VBA - Mise à jour des onglets selon une liste

Bonjour, AntoineM, le Forum,

Peut-être ainsi :

Sub MAJ_Ongl()
    Dim Sh As Worksheet
    Dim Ws As Worksheet
    Dim LastLig As Long
    Dim i As Integer
    Dim j As Integer
    Application.ScreenUpdating = False
    j = 1
    Application.DisplayAlerts = False
    For Each Ws In Application.Worksheets
        If Ws.Name = Sheets("Info").Range("A" & j) Then
            j = j + 1
        Else
            If Ws.Name <> "Info" And Not Ws.Name Like "Data" Then
                Ws.Delete
                j = j + 1
            End If
        End If
    Next Ws
    Application.DisplayAlerts = True
    With Sheets("Info")
        LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = LastLig To 1 Step -1
            On Error Resume Next
            Set Sh = Sheets(CStr(.Range("A" & i).Value))
            On Error GoTo 0
            If Sh Is Nothing Then
                ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
                ActiveSheet.Name = CStr(.Range("A" & i).Value)
            Else

            End If
        Next i
    End With
    Sheets("Info").Activate
    Set Sh = Nothing
    Application.ScreenUpdating = True
End Sub

A bientôt

 

[Dranreb]

Re : VBA - Mise à jour des onglets selon une liste

Bonjour.
Ou bien comme ça :

Sub MAJ_Ongl()
Dim Coll As New Collection, N As Long, Nom As String, TE(), Sh As Worksheet
For N = Feuil7.Index + 1 To ThisWorkbook.Worksheets.Count
   Set Sh = ThisWorkbook.Worksheets(N)
   Coll.Add Sh, Key:=Sh.Name
   Next N
TE = Feuil1.[A1:B1].Resize(Feuil1.[A5000].End(xlUp).Row).Value
On Error Resume Next
For N = 1 To UBound(TE): Nom = CStr(TE(N, 1)): Coll.Remove Nom: Next N
Application.DisplayAlerts = False
For Each Sh In Coll: Sh.Delete: Next Sh
Application.DisplayAlerts = True
For N = 1 To UBound(TE)
   Nom = CStr(TE(N, 1))
   Err.Clear: Set Sh = ThisWorkbook.Worksheets(Nom)
   If Err Then Set Sh = ThisWorkbook.Worksheets.Add(After:= _
      ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)): Sh.Name = Nom
   Next N
End Sub

Suite à remarque privée de DoubleZéro, que je salue, je viens de corriger ce code de la façon suivante: Récupération dans TE de 2 colonnes, bien qu'on n'ait besoin que de la 1ère, pour garantir que sa Value soit toujours un tableau 2D, même s'il n'y a qu'une ligne.

 

[AntoineM]

Re : VBA - Mise à jour des onglets selon une liste

Bonjour DoubleZero, Dranreb, le forum,

Alors je suis très nul en vba, j'espère ne pas abuser mais j'aimerais en savoir plus :

- Je ne vois pas trop les changement apporté par DoubleZero à la macro :/.
Après test elle fonctionne bien mais à le même problème que la mienne, elle supprime les onglets et donc, les données inscrites sur les feuilles sont perdues !

- Et j'espère ne pas trop en demander mais Dranreb, pourrais tu me mettre quelque commentaires car je ne comprend pas tout ^^
J'ai testé aussi et le macro fonctionne avec le résultat que j'attendais !
Cela dis, j'aimerais bien la comprendre :/

Sub MAJ_Ongl()
 Dim Coll As New Collection, N As Long, Nom As String, TE(), Sh As Worksheet ' qu'est ce que New collection ? et TE () ?
 For N = Feuil7.Index + 1 To ThisWorkbook.Worksheets.Count 'ici pour toutes les feuille après la feuille 7
    Set Sh = ThisWorkbook.Worksheets(N)
    Coll.Add Sh, Key:=Sh.Name 'la j'ai du mal, tu ajoutes une colonne avec le nom de la feuille ?
    Next N
 TE = Feuil1.[A1:B1].Resize(Feuil1.[A5000].End(xlUp).Row).Value
 On Error Resume Next
 For N = 1 To UBound(TE): Nom = CStr(TE(N, 1)): Coll.Remove Nom: Next N 'je ne maitrise pas du tout UBound et Lbound mais ici ca doit etre un tableau ^^
 Application.DisplayAlerts = False
 For Each Sh In Coll: Sh.Delete: Next Sh 'coll la new collection, mais je vois pas ou tu le définis ou a quoi cela correspond
 Application.DisplayAlerts = True
 For N = 1 To UBound(TE) 'je comprends pas
    Nom = CStr(TE(N, 1)) 'ici tu défini las variable Nom mais je ne comprend pas CStr...
    Err.Clear: Set Sh = ThisWorkbook.Worksheets(Nom) 'je comprend pas
    If Err Then Set Sh = ThisWorkbook.Worksheets.Add(After:= _
       ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)): Sh.Name = Nom
    Next N
 End Sub

[EDIT] TE correspond à un tableau dynamique dont tu définis la limite inférieure avec UBound, mais je ne comprends pas :

For N = 1 To UBound(TE): Nom = CStr(TE(N, 1)): Coll.Remove Nom: Next N

Désolé si j'en demande trop !


Bien à vous,

Antoine M

 

[Dranreb]

Re : VBA - Mise à jour des onglets selon une liste

C'est légitime de vouloir comprendre la macro.
Le mot clé New placé devant un nom de type d'objet forme avec lui un exemplaire nouvellement créé de ce type d'objet. Lorsqu'il est utilisé dans une déclaration Dim, Private ou Public, la création a lieu lors de la réservation de mémoire de l'emplacement pour cette variable.
Derrière un nom de variable dans une déclaration, une parenthèse ouvrante immédiatement suivie d'une parenthèse fermante signale un tableau dynamique, c'est à dire dont les dimensions seront fixées à l'exécution, soit par un Redim soit par l'appel d'une Function ou d'une méthode qui renvoie un tableau.
ici pour toutes les feuille après la feuille 7
Pas tout à fait: pour toutes les feuilles positionnées, dans la collection Sheets, après la feuille représentée par un objet Worksheet nommé Feuil7 dans la rubrique Microsoft Excel Objets.
J'ajoute l'objet Sh lui même à la collection, avec pour clé son nom. Il est donc entendu pour une question ultérieure que cette collection ne contiendra que des objets Worksheet, et que son exploration par un For Each Sh In Coll ne ramènera qu'un Worksheet dans Sh à chaqque passage dans la boucle.
Ubound(TE), équivalent à Ubound(TE, 1) donne en effet la limite supérieure de la première dimension du tableau TE.
CStr était déjà utilisé dans des codes précédents. Il ne serait nécessaire que si certaines feuilles à supprimer ou créer devaient avoir pour nom un numéro. Parce que si on spécifie un nombre derrière Worksheets(, il considère qu'il s'agit de sa position dans la collection et non pas de son nom.

 

[AntoineM]

Re : VBA - Mise à jour des onglets selon une liste

Bonjour Dranreb, le forum,

D'accord, et donc si je veux adapter ce code, pour une plage de A4 à A...
Il me suffit de modifier ce petit bout de code ?

TE = Feuil1.[A4:B4].Resize(Feuil1.[A5000].End(xlUp).Row).Value

Car quand je le fais, la macro crée également 3 feuilles (avec un nom générique du style : Feuil37, cela dépend du nombre de feuilles crées).

Merci je crois que je commence a comprendre ton code, avec quelques recherches pour étoffer je vais y arriver

Bonne journée,

AntoineM

 

[Dranreb]

Re : VBA - Mise à jour des onglets selon une liste

Bonjour
Oui en retranchant le nombre de lignes qui précèdent :

TE = Feuil1.[A4:B4].Resize(Feuil1.[A5000].End(xlUp).Row - 3).Value

Ça explique sinon les 3 feuilles en trop: si on a juste 2 noms, en A4 et A5, 2 = 5 - 3.

 

[AntoineM]

Re : VBA - Mise à jour des onglets selon une liste

Merci Dranreb pour l'aide et les explications.

Cela fonctionne parfaitement (et bonus que je n'avais pas demandé, cela met les onglet dans l'ordre )

Question bonus : Si je veux copier les donnée de la feuille date (qui contient des formules) sur chaque onglet crée sans jamais écraser les données des feuilles qui sont déjà créer et remplies.

Sub MAJ_OnglDranreb()
 Dim Coll As New Collection, N As Long, Nom As String, TE(), Sh As Worksheet
  
  For N = Feuil7.Index + 1 To ThisWorkbook.Worksheets.Count
 
    Set Sh = ThisWorkbook.Worksheets(N)
    Coll.Add Sh, Key:=Sh.Name
    Next N
 TE = Feuil1.[A4:B4].Resize(Feuil1.[A200].End(xlUp).Row - 3).Value
 On Error Resume Next
 For N = 1 To UBound(TE): Nom = CStr(TE(N, 1)): Coll.Remove Nom: Next N
 Application.DisplayAlerts = False
 For Each Sh In Coll: Sh.Delete: Next Sh
 Application.DisplayAlerts = True
 For N = 1 To UBound(TE)
    Nom = CStr(TE(N, 1))
    Err.Clear: Set Sh = ThisWorkbook.Worksheets(Nom)
    If Err Then Set Sh = ThisWorkbook.Worksheets.Add(After:= _
       ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)): Sh.Name = Nom
    Sheets("Data").Select
    Cells.Select
    Range("A1:S80").Copy
    Sh.Select
    Cells.Select
    
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Next N
    
    Feuil1.Select
        
 End Sub

Bien à toi,

AntoineM

 

[Dranreb]

Re : VBA - Mise à jour des onglets selon une liste

Bonjour.

J'avais oublié cette dernière question.
Il convient de ne le faire qu'à la création des feuilles. Donc je pense qu'il vaut mieux l'autre syntaxe du If parce que ça commence à faire trop d'instructions sur la même ligne.
Pas besoin de Select pour la copie:
Sheets["Data").Cells.Copy