Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cours

[Chri8Ed]

Bonjour à tous

Je souhaiterais pourvoir recopier toutes les données contenues de ma feuille « DATA » dans mon « TABLEAU »
(Il n’y a que 5 lignes de remplies dans mon exemple, Mais ce nombre de ligne peut varier en plus ou moins au cours des mois).

De manière que cette recopie se fasse dans la première cellule vide, soit dans la cellule B17 dans mon exemple
Mais surtout de manière que les dates contenues dans la colonne B de ma feuille « DATA » prennent la valeur du mois en cours si la date du jour est inférieure au 20 du mois, soit la date du mois suivant si elle est postérieure au 20.

Exemple si nous sommes le 15 Décembre
La date du 05/01/14 en B5 dans DATA devienne 05/12/14 en B17
Si nous somme le 21 Décembre
La date du 05/01/14 en B5 dans DATA devienne 05/01/15 en B17
(Les date des jours ne doivent pas changer)

Je n’arrive pas à trouver de solution stable
Si quelqu’un à une idée ou a déjà réalisé de genre de macro ???
Je veux bien son aide

Merci d'avance
Cordialement

 

[Chri8Ed]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonjour et meilleurs vœux à tous

J'ai bien suivit ta procédure, mais je ne vois apparaitre aucun nom de tableau
Cependant j'ai quand même pu trouver cette information dans la gestion des noms
Mon tableau se nome bien Tableau1

Seule ma feuille data ne se situe pas dans la feuil2, mais dans la feuil5
J'ai donc modifié les 3 codes concernant la feuil2

Mais j'obtiens toujours la même erreur

Ne voyant pas où chercher
J'ai copié les 2 feuilles de ta solution dans mon fichier
Le tableau de ton fichier à pris le non de Tableau13
J'ai donc changé les 2 Tableau1 en Tableau13
J'ai aussi rechangé le nom de la feuil2 en feuil6

Je me suis donc retrouvé avec les 2 mêmes feuilles que celles contenues dans ta dernière solution
et je me retrouve avec la même erreur alors que dans ta solution cela fonctionne très bien !!

Je continue à chercher, mais je vois pas bien où peut se trouver le problème ??

A+

 

[Papou-net]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonsoir à tous, et meilleurs voeux,
Bonsoir Chri8Ed,

Peux-tu joindre ton fichier, après suppression des données confidentielles éventuelles?

A +

Cordialement.

 

[Chri8Ed]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonjour Papou-net

Je joins donc mon fichier vidé avec les 2 seules feuilles concernées.

J’ai fini par résoudre partiellement mon problème
Mais cela ne fonctionne toujours pas.

Cependant cette fois je connais exactement le problème
C’est bien du aux formules.

Si je supprime les formules, tout en gardant les lignes vides, cela fonctionne
Mais avec les formules, c’est impossible.
Le message d’erreur qui apparait est trompeur, car totalement inapproprié.

Je ne sais pas si il est possible de contourner ce problème ??
Mais personnellement, je ne peux pas me passer des lignes vides
Ni des formules dans ces lignes vides.
(Feuille récapitulative, Nombreuses macros d'automatisations de saisies....)

Je continue par moi même de trouvez une solution
Mais je doute de la trouver

A+

 

[Papou-net]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonjour Chri8Ed,

En modifiant la macro comme suit, l'insertion des lignes se fait bien avec la recopie des formules.

Par contre, je ne peux pas vérifier si ça répond bien à ton besoin, car lorsque j'active la modification à l'ouverture de ton fichier j'obtiens le message #VALEUR! dans la colonne K. Je te laisse donc le soin d'essayer chez toi.

Sub Recopie()
Dim Cel As Range, Col As Integer, Dte As Date, Lg As Integer
Dim Plage As Range

Application.ScreenUpdating = False
For Each Cel In Feuil3.Range("B:B").SpecialCells(xlCellTypeConstants)
  Dte = Day(Cel) & "/" & Month(Date) & "/" & Year(Date)
  If Day(Date) > 20 Then Dte = DateAdd("m", 1, Dte)
  With Feuil1
    Lg = .Range("Tableau1").Columns(1).SpecialCells(xlCellTypeConstants).Count + 1
    Set Plage = .Range("Tableau1").Range("A" & Lg & ":O" & Lg)
    .Rows(Lg + 3).EntireRow.Insert Shift:=xlDown
    .Rows(Lg + 2).EntireRow.Copy .Rows(Lg + 3)
    Feuil3.Range("B" & Cel.Row & ":I" & Cel.Row).Copy Plage.Offset(-1, 0)
    Plage.Offset(-1, 0).Range("A1") = Dte
  End With
Next
Application.ScreenUpdating = True
End Sub

A +

Cordialement.

 

[Chri8Ed]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonjour Papou-net

Et bien pour moi cela fonctionne parfaitement cette fois
Il y a une petit temps d'attente
Mais la copie s"effectue sans problème avec les formats

Je te remercie beaucoup pour ton travail et ta persévérance
C'est parfait

J'ai du mal à comprendre le code
Mais je vais essayé de le faire

A+

 

[Papou-net]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

RE:

Merci du retour d'info.

Je t'ai ajouté des commentaires sur le code proposé pour t'aider à comprendre la structure de la procédure. J'espère avoir été suffisamment clair et concis.

NB: j'en ai profité pour rectifier une petite erreur sans conséquence, mais une erreur tout-de-même.

C'est la ligne suivante

Set Plage = .Range("Tableau1").Range("A" & Lg & ":N" & Lg)

Sub Recopie()
Dim Cel As Range, Col As Integer, Dte As Date, Lg As Integer
Dim Plage As Range

'Désactivation du rafraîchissement écran pour accélérer le déroulement de la procédure
Application.ScreenUpdating = False
'La boucle analyse chaque cellule non vide de la colonne 3 de la feuille "Data"
For Each Cel In Feuil3.Range("B:B").SpecialCells(xlCellTypeConstants)
  'Dte stocke la date du jour de la cellule en cours de lecture et du mois et de l'année en cours
  Dte = Day(Cel) & "/" & Month(Date) & "/" & Year(Date)
  'Si le jour de Dte  est supérieur à 20, ajoute 1 au mois
  If Day(Date) > 20 Then Dte = DateAdd("m", 1, Dte)
  With Feuil1 'Toutes les références commençant par un "." se réfèrent à la feuille "Tableau"
    'Lg récupère le numéro de la première cellule vide de Tableau1
    'Attention: la numérotation se fait à partir du haut de Tableau1, c'est-à-dire la ligne 4 de la feuille "Tableau"
    Lg = .Range("Tableau1").Columns(1).SpecialCells(xlCellTypeConstants).Count + 1
    'Plage définit les cellules de Tableau1 correspondant à Lg, soit des colonnes B:O
    Set Plage = .Range("Tableau1").Range("A" & Lg & ":N" & Lg)
    'Insertion d'une ligne entière sur la feuille "Tableau" sur la ligne Lg +3
    'et non dans Tableau1, ce qui provoquait une erreur
    .Rows(Lg + 3).EntireRow.Insert Shift:=xlDown
    'Copie de la ligne au-dessus de Lg sur la ligne Lg
    .Rows(Lg + 2).EntireRow.Copy .Rows(Lg + 3)
    'Copie et colle la zone B:I de la ligne de Cel sur la ligne active de la feuille "Tableau"
    Feuil3.Range("B" & Cel.Row & ":I" & Cel.Row).Copy Plage.Offset(-1, 0)
    'Met la date à jour dans la cellule B
    Plage.Offset(-1, 0).Range("A1") = Dte
  End With
Next
'Réactivation du rafraîchissement écran (à ne pas oublier)
Application.ScreenUpdating = True
End Sub

A +

Cordialement.

 

[Chri8Ed]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Re,

Merci encore pour ce travail supplémentaire
Car je suis très comptant d'avoir cette macro
Mais il est tout aussi important de progresser en VBA

Quelques précisions si je n’abuse pas ?
Mais il n'y aucune obligation de réponse

'La boucle analyse chaque cellule non vide de la colonne 3 de la feuille "Data"

Je suppose que c'est la colonne 2 et pas 3.

Dte = DateAdd("m", 1, Dte)

Je ne connaissais pas cette fonction, mais j'ai pu trouver dans l'aide un exemple qui correspondait justement à cela

'Lg récupère le numéro de la première cellule vide de Tableau1
'Attention: la numérotation se fait à partir du haut de Tableau1, c'est-à-dire la ligne 4 de la feuille "Tableau"
Lg = .Range("Tableau1").Columns(1).SpecialCells(xlCellTypeConstants).Count + 1

Là je ne comprends malheureusement pas la syntaxe ??
On traite de 1ère cellule vide dans toutes les colonnes ou dans la colonne B ?

'Plage définit les cellules de Tableau1 correspondant à Lg, soit des colonnes B:O
Set Plage = .Range("Tableau1").Range("A" & Lg & ":N" & Lg)

Je ne vois le référencement aux colonnes B:0, mais plutôt les colonnes A:N ?

'Insertion d'une ligne entière sur la feuille "Tableau" sur la ligne Lg +3

Pourquoi ce plus 3 ?

A+

 

[Papou-net]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

RE:

Bonsoir,

La boucle analyse chaque cellule non vide de la colonne 3 de la feuille "Data"

Effectivement, c'est bien vu: il s'agit effectivement de la colonne B

Lg récupère le numéro de la première cellule vide de Tableau1
Attention: la numérotation se fait à partir du haut de Tableau1, c'est-à-dire la ligne 4 de la feuille "Tableau"
Lg = .Range("Tableau1").Columns(1).SpecialCells(xlCellT ypeConstants).Count + 1

On cherche la première cellule vide dans la première colonne de Tableau1 (.Columns(1)) laquelle est située en colonne B dans le référentiel de la feuille. En fait, la première cellule de Tableau1 est située sur la cellule B4 de l'onglet

'Plage définit les cellules de Tableau1 correspondant à Lg, soit des colonnes B:O
Set Plage = .Range("Tableau1").Range("A" & Lg & ":N" & Lg)

Même raisonnement que ci-dessus: le référencement A:N de Tableau1 correspond à B:O de la feuille, puisque la colonne de gauche de Tableau1 est sur la colonne B de l'onglet. Il faut simplement imaginer que la première cellule d'un tableau (ou d'une plage de cellule référencée) est toujours A1, quelle que soit la position de ce tableau sur la feuille. Ce qui répond par là même à ta dernière question:

Pourquoi +3? Et bien tout simplement parce que la première ligne de Tableau1 est sur la ligne 4 de l'onglet et que la commande d'insertion de ligne s'applique au référentiel de la feuille et non à celui du tableau.

Espérant avoir répondu

 

[Chri8Ed]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonjour Papou-Net, Bonjour à tous

Merci à nouveau pour ces précisions.
Avec ces explications c’est beaucoup plus claire pour moi.
Sauf pour le raisonnement au sujet du référencement de la cellule vide qui reste encore un peu obscure.
Mais je vais essayer d’approfondir cette logique ....

Cette macro fonctionne maintenant à merveille et va me simplifier nettement cette tache.
J’ai pensé que puisqu’elle fonctionne sans intervention du placement,
Que l’on pouvait faire encore bien mieux.

Puisque cette macro ne doit se lancer qu’une fois par mois,
Elle pourrait se lancer automatiquement avec une macro événementielle.

Ce qui ne devrait sans doute pas être très difficile
Ce qui en revanche risque d’être beaucoup plus ardu
C’est qu’elle ne se lance qu’une seule fois dans le mois !!

Mais comme c’est quelque chose de différent
Je vais lancer une nouvelle discussion.

A bientôt peut-être

 

[Papou-net]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonsoir Chri8Ed,

Voici la version 2 de ton fichier avec activation du bouton une seule fois dans le mois.

La macro événementielle qui se lance à l'ouverture du fichier:

Private Sub Workbook_Open()
With Feuil1
  .CommandButton21.Enabled = IIf(CInt(.Label21) = Month(Date), False, True)
End With
End Sub

Tu remarqueras que j'ai ajouté un contrôle Label21 sur Feuil1, dont le Caption enregistre le mois de la mise-à-jour. Pour aider à le visualiser, j'ai laissé sa propriété Visible à True, mais dans la version finale, il vaut mieux le masquer.

Ensuite, j'ai ajouté deux lignes à la procédure du bouton Recopie que tu n'auras sûrement aucune peine à décoder:

Private Sub CommandButton21_Click()
Recopie
Me.Label21.Caption = Month(Date)
Me.CommandButton21.Enabled = False
End Sub

En te souhaitant une bonne soirée.

Cordialement.

 

[Chri8Ed]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonsoir Papou-Net

Merci encore pour ce développement
C'est du soigné !

Ce n'est pas tout à fait à ce que je pensais
Je pensais à une automatisation totale de la recopie
C'est à dite à l'ouverture du fichier et sans aucune intervention (Pas de bouton à cliquer)
Mais en fait c'est peut-être pas plus mal ainsi
Et sans doute plus sûr !

Je vais encore la tester demain car il est à présent un peu tard
Mais d’ores et déjà c'est vraiment un grand merci que je t'adresse.

A+

 

[Papou-net]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonjour Chri8Ed,

Pour le cas où, voici une version simplifiée sans bouton, et entièrement autmatique.

Bon dimanche.

Cordialement.

 

[Chri8Ed]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonjour Papou-Net

Cette dernière version répond exactement à ce que je pensais
Mais comme je l'ai dit hier, je vais peut-être conservé ta solution précédente.

J'avais cependant pensé pour ne pas oublier de faire cette opération au moment opportun, à une MsgBox qui se déclencherai à partir du 21 dans le cas où cette opération n'aurait pas été effectuée.

C'est un peu le contraire de ce que tu as réalisé dans cette dernière version .

A+

 

[Papou-net]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

RE:

C'est un peu le contraire de ce que tu as réalisé dans cette dernière version.

Alors peut-être que cette version 4 y répond?

A+

Cordialement.

 

[Chri8Ed]

Re : Recopie de données entre feuilles avec MAJ des dates en fonction du mois en cour

Bonsoir

Oui c'est exactement cela
Mais il y a un problème
Même quand la mise à jour à déjà été faite
La MsgBox réapparait à nouveau

A+