Microsoft 365 VBA Gérer le format Tableau Excel

[GMeunier]

Bonjour,

Je mets à jour périodiquement une base de données d’indicateurs (Base de Données) en copiant collant une feuille de mise à jour (Mise à jour) ayant ce jeu d’indicateurs et en modifiant la date et la valeur, pour la nouvelle période. Les 2 bases sont au format Tableau et j’ai automatisé avec une macro le copier-coller et la remise à blanc de la feuille Mise à Jour. Cela marche très bien. Voir ci-dessous.
Sub MàJ()
Dim ligne As Long
'Sélection de la feuille Mise à Jour (Feuil3) dans le cahier
Feuil3.Select
'Sélection du tableau Mise à Jour
Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count).Select
Selection.Copy
'Sélection de la feuille Base de données
Sheets("Données").Select
'Numéro de la première ligne vide
ligne = Sheets("Données").Range("A1048576").End(xlUp).Row + 1
'Sélection de la première ligne vide
Range("A" & ligne).Select
'Copie de la mise à jour
ActiveSheet.Paste
'Remettre à blanc les colonnes variables (B et H) de MàJ
Feuil3.Select
Range("B2", Range("B2").End(xlDown)).Clear
Range("H2", Range("H2").End(xlDown)).Clear
End Sub
J’ai aussi créé une macro qui génère un numéro d’identification (Colonne «A» ID) pour les données ajoutées à la suite de la base principale Voir code ci-dessous. Or ce code ne fonctionne que si la base est au format Plage de données. Après des recherche, j’ai cru comprendre que c’était lié à l’objet Tableau et qu’il fallait utiliser ListObject et les instruction adaptées. Mais là, j’ai du mal !
Sub NumAuto()
Dim i As Long, DernLigne As Long, PremLigne As Long
Sheets("MàJ").Select
' N° de la dernière ligne de la colonne A non vide
DernLigne = Range("A" & Rows.Count).End(xlUp).Row
Debug.Print DernLigne
PremLigne = DernLigne + 1
Debug.Print PremLigne
'Remplissage du numéro ID en colonne A jusqu'à la dernière ligne vide en colonne B
For i = PremLigne To Range("B" & Rows.Count).End(xlUp).Row
Debug.Print i
Range("A" & i).Select
ActiveCell.FormulaR1C1 = i
Next i
End Sub
Je suis preneur d’une aide pour adapter mes 2 macros au fait que je travaille avec des Objets Tableau.
Merci

 

[danielco]

Bonjour,

Pour la première macro, essaie :

Sub MàJ()
  Dim ligne As Long
  'Sélection de la feuille Mise à Jour (Feuil3) dans le cahier
  Feuil3.Select
  'Sélection du tableau Mise à Jour
  ActiveSheet.ListObjects(1).Range.Copy
  'Sélection de la feuille Base de données
  Sheets("Données").Select
  'Numéro de la première ligne vide
  ligne = Sheets("Données").Range("A1048576").End(xlUp).Row + 1
  'Sélection de la première ligne vide
  Range("A" & ligne).Select
  'Copie de la mise à jour
  ActiveSheet.Paste
  'Remettre à blanc les colonnes variables (B et H) de MàJ
  Feuil3.Select
  Range("B2", Range("B2").End(xlDown)).Clear
  Range("H2", Range("H2").End(xlDown)).Clear
End Sub

Le tableau doit être le premier de la liste (index 1). Sinon, change le numéro d'index ou remplace-le par le nom du tableau. Teste. Je regarde pour la seconde partie du message.

Cordialement.

Daniel

 

[danielco]

A première vue, la seconde macro devrait fonctionner aussi avec un tableau. Quelle est l'erreur, et sur quelle ligne ?

Daniel

 

[GMeunier]

Danielco bonjour!
Merci pour vos réponses, voici mes compléments :

1) 1° Macro - Ajout d'un Tableau à un autre :
Cela fonctionne mais cette nouvelle macro recopie aussi la ligne de titre (1° ligne) du Tableau "Mise à jour". Il faudrait éliminer la ligne de titre du Tableau "MàJ" dans la recopie dans "Base de Donnée"

2) 2° Macro - Numérotation automatique :
a) Ci dessus, mon fichier d'essai au format Plage de données avec la colonne ID remplie avec la macro. Cela fonctionne, en fait le ID = 2 devrait être 1, 1° enregistrement mais c'est facile à corriger.
b) Si je mets cette plage au format Tableau, la colonne ne se remplit pas. Dans la fenêtre d'exécution j'obtiens 9 et 10. Le numéro de la dernière ligne de la colonne A (9) est en fait le numéro de la dernière ligne remplie (quelque soit la colonne) du Tableau. La macro n'identifie pas la dernière ligne remplie de la colonne A.
Cordialement

 

[danielco]

S'il te plait, met ton classeur en pièce joointe.

Daniel

 

[GMeunier]

Danielco,

J'ai joint le fichier d'essai. Voici ma perception du problème :

A) Ajout du Tableau T_MAJ au Tableau T_DONNEES
a) La macro MàJ01 (Syntaxe Plage) fonctionne correctement,
b) La macro MàJ02 (Syntaxe Tableau) fonctionne mais insère aussi la ligne des titres. Il ne faut pas copier la ligne de titre. Je suppose que c'est de la syntaxe de références structurées. Par ailleurs, je n'ai pas compris comment identifier le numéro d'index (1) de ListObjects.

B) Numérotation automatique de l'ID des lignes de mise à Jour rajoutées à la Table T_DONNEES
a) La macro NumAuto01 complète la série des numéros d'identification de la plage de données de la feuille DONNEES01, quelque que soit les numéros déjà enregistrés. Sur un format Plage de données, ça fonctionne.
b) La macro NumAuto02 est censée faire la même chose mais sur un format Tableau T_DONNEES (Feuille DONNEES). Dans ce cas, ça ne fonctionne pas. C'est la dernière ligne remplie du Tableau, la 9, qui est identifiée et non la dernière ligne remplie de la colonne A (dans le cas présent la 4 - car les autres cellules de la colonne A sont déjà remplies). Donc ma question : Quel code pour que cette numérotation fonctionne aussi sur un format Tableau?

Je reste à l'écoute,

GMeunier

 

[danielco]

Compris.
A.

Sub MàJ02()
    'Syntaxe tableau
    Dim ligne As Long
    'Sélection de la feuille Mise à Jour (Feuil1) dans le cahier
    'Sélection du tableau Mise à Jour - Syntaxe Tableau
    'Sélection de la feuille Base de données
    'Numéro de la première ligne vide
    ligne = Sheets("DONNEES").Range("A1048576").End(xlUp).Row + 1
    'Confirmation et copie de la mise à jour
    MsgBox "Confirmer la mise à jour de la base", vbOKCancel
    Feuil1.ListObjects(1).DataBodyRange.Copy Sheets("DONNEES").Cells(ligne, 1)
    'Remettre à blanc les colonnes variables (B, D et E) de MàJ
    With Feuil1.ListObjects(1)
      .ListColumns("ANNEE").DataBodyRange.Clear
      .ListColumns("VALEUR").DataBodyRange.Clear
      .ListColumns("Ha").DataBodyRange.Clear
    End With
End Sub

B.

Sub NumAuto02()
  Dim i As Long, DernLigne As Long, PremLigne As Long
  Sheets("DONNEES").Select
  With Sheets("DONNEES").ListObjects(1)
    For i = 1 To .ListRows.Count
      .ListRows(i).Range(1) = i
    Next i
  End With
End Sub

En règle générale, il faut éviter les "Select", qui ralentissent l'exécution.

Daniel

 

[GMeunier]

Merci vraiment, c'est simple, compréhensible et ça marche.
La seule instruction que j'ai modifié et le Clear que j'ai remplacé par .Value ="" car sinon Clear efface la validation que j'utilise pour remplir la colonne Année.

 

[GMeunier]

Compris.
A.

Sub MàJ02()
    'Syntaxe tableau
    Dim ligne As Long
    'Sélection de la feuille Mise à Jour (Feuil1) dans le cahier
    'Sélection du tableau Mise à Jour - Syntaxe Tableau
    'Sélection de la feuille Base de données
    'Numéro de la première ligne vide
    ligne = Sheets("DONNEES").Range("A1048576").End(xlUp).Row + 1
    'Confirmation et copie de la mise à jour
    MsgBox "Confirmer la mise à jour de la base", vbOKCancel
    Feuil1.ListObjects(1).DataBodyRange.Copy Sheets("DONNEES").Cells(ligne, 1)
    'Remettre à blanc les colonnes variables (B, D et E) de MàJ
    With Feuil1.ListObjects(1)
      .ListColumns("ANNEE").DataBodyRange.Clear
      .ListColumns("VALEUR").DataBodyRange.Clear
      .ListColumns("Ha").DataBodyRange.Clear
    End With
End Sub

B.

Sub NumAuto02()
  Dim i As Long, DernLigne As Long, PremLigne As Long
  Sheets("DONNEES").Select
  With Sheets("DONNEES").ListObjects(1)
    For i = 1 To .ListRows.Count
      .ListRows(i).Range(1) = i
    Next i
  End With
End Sub

En règle générale, il faut éviter les "Select", qui ralentissent l'exécution.

Daniel

 

[GMeunier]

Danielco, bonjour,

En fait, il me reste un problème dans la macro de numérotation après avoir testé. Après chaque ajout, elle renumérote l'ensemble des lignes. Or il faut qu'elle renumérote, à la suite de la dernière valeur renseignée en A, que les nouvelles lignes. En effet le but de cette colonne ID (A) est de donner un numéro d'identification fixe dès le début. Supposons que le fichier Données soit trié sur une autre colonne alors lors de l'ajout suivant tout est renuméroté et je perds l'ID initial.
Il me semble que l'instruction devrait définir le i comme la première cellule vide de la colonne A et alors ça devrait fonctionner.
Donc comment identifier la valeur de la ligne dans la valeur dans la colonne A est vide ?

Très cordialement et sans vouloir abuser car je suis très satisfait du reste que j'ai bien compris

GMeunier

 

[danielco]

Bonjour,

Sub NumAuto02()
  Dim i As Long, DernLigne As Long, PremLigne As Long
  With Sheets("DONNEES").ListObjects(1)
    If .ListRows(1).Range(1) = "" Then .ListRows(1).Range(1) = 1
    For i = 2 To .ListRows.Count
      If .ListRows(i).Range(1) = "" Then
        .ListRows(i).Range(1) = .ListRows(i - 1).Range(1) + 1
      End If
    Next i
  End With
End Sub

Daniel

 

[GMeunier]

Danielco,
C'est parfait, les ID déjà créés restent fixes. Il me reste à m'approprier le code en comprenant bien la terminologie Tableau.
Encore merci!
GMeunier

 

[GMeunier]

Danielco, bonjour,

En fait, il reste encore un petit problème. La macro ne renumérote pas depuis le début de la série et c'est OK. Mais, après différents tests, je me suis aperçu que la numérotation des lignes ajoutées partait du dernier numéro rempli de la colonne N°.
Si la base a été triée sur un autre champ, entre 2 mises à jour pour des raisons d'analyse, dans ce cas le dernier numéro de la colonne N° n'est plus le dernier numéro de la série des numéros d'identification. Il faudrait que le numéro pris comme point de départ de la numérotation des lignes ajoutées soit celui de la ligne correspondante (c.à.d celle ayant la 1° cellule vide de la colonne A) -1 et non le numéro inscrit dans cette dernière ligne remplie colonne N°.
J'ai remis mes 2 tableaux en A1, ce qui est plus simple.

Je vous ai joint le nouveau fichier.

Très cordialement
GMeunier

 

[danielco]

Bonjour,

Essaie :

Sub NumAuto01()
'Ne fonctionne pas car concernant la dernière remplie de la colonne A, il reprend le dernier N° de la colonne A et non le n° de la ligne, ceci entraine le risque de n° en double si la fichier a été trié
  Dim i As Long, DernLigne As Long, PremLigne As Long, Maxi As Long
  With Sheets("Données").ListObjects(1)
    Maxi = Application.Max(.ListColumns(1).DataBodyRange)
    For i = 1 To .ListRows.Count
      If .ListRows(i).Range(1) = "" Then
        Maxi = Maxi + 1
        .ListRows(i).Range(1) = Maxi
      End If
    Next i
  End With
End Sub

Daniel

 

[GMeunier]

Daniel,

Merci, la macro reprend bien la suite du dernier numéro quelque soit l'ordre.
Je n'ai plus qu'à bien comprendre le code pour les tableaux! Ça progresse!

Très cordialement

GMeunier