Macro recopie éléments dans différents onglets année

Le 07/11/2017 à 20:34

Bonsoir à tous,

J'ai une macro qui recopie certaines valeurs vers un onglet "Journal Achats 2016"...

Mon problème est que j'ai une page "Journal Achats" pour année civile. J'ai dans mon application Excel une valeur nommée = année_commerciale qui correspond à l'année en cours. Je ne sais pas comment utiliser ce nom "année_commerciale" dans ma macro afin de ne pas changer la macro chaque début d'année...

Je vous montre le début du code qui va vous permettre de visualiser le problème :

    Sheets("Facture Achats").Select
    Range("C70").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Journal Achats 2016").Select
    Range("E20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Je souhaiterai modifier la phrase Sheets("Journal Achats 2016").Select par une automatisation de l'année (sinon, tous les ans je vais être obligé de changer la macro (qui fait de très nombreuses lignes) mais j'avoue que la syntaxe m'échappe après plusieurs tentatives infructueuses....

En attendant d'un avis plus expert que le mieux.

Bonne soirée et d'avance merci.

Joe

Le 07/11/2017 à 20:55

Bonjour Joe,

Supposons par exemple que ta feuille "Journal Achats 2016" soit TOUJOURS la 3ème feuille du classeur ;

et idem pour les années suivantes avec "Journal Achats 2017", "Journal Achats 2018", "Journal Achats 2019", etc...

(sous-entendu : une feuille annuelle remplace la précédente )

Dans ce cas, toutes tes lignes de code peuvent se résumer à ceci :

Worksheets("Facture Achats").[C70].Copy Worksheets(3).[E20]

(cela pour une copie avec le format de la cellule C70)

Si tu n'as pas besoin de copier ce format :

Worksheets(3).[E20] = Worksheets("Facture Achats").[C70]

ATTENTION

Si tu n'es pas sûr que ta feuille annuelle "Journal Achats 20XX" sera toujours la 3ème,

alors n'utilise pas l'index de la feuille mais plutôt le code de la feuille, comme te

l'indique plus bas la solution d'eriiic.

Merci de me dire si ça te convient.

Cordialement

Le 08/11/2017 à 00:00

Bonjour,

Mauvaise idée que d'utiliser l'index. Il suffit qu'une personne ne soit pas au courant, ou ait oublié dans 6 mois, et insère une feuille pour avoir des résultats complètement faux.

Dans ce cas tu devrais utiliser le SheetCode du module de la feuille,

Dans le projet tu lis Feuil1 (nom de la feuille), son sheetcode c'est Feuil1 :

2017 11 07 23 42 38

Et il ne changera pas, quels que soient le nom ou la position de la feuille.

Le code devient :

[barrer]Sheets("Journal Achats 2016").Select[/barrer]
Feuil1.Select

Par ailleurs il faut éviter les .Select inutile (99 des cas). Ca ralenti pour rien.

Et comme en plus tu ne veux que la valeur ton code se résume en fait à 1 ligne, comme dit précédemment :

Feuil1.Range("E20") = Sheets("Facture Achats").Range("C70").Value

J'ai mis Feuil1, met le vrai SheetCode de la feuille bien sûr.

eric

Le 08/11/2017 à 18:46

Bonsoir Dhany et Eriiic,

Merci pour votre attention et vos réponses.

Je me suis peut-être mal exprimé ou peut-être n'ai-je pas compris comment utiliser le code... Alors, je résume :

Je créé mes factures sur la feuille "Facture Achats" puis par Macro j'exporte cette facture (certaines valeurs seulement) vers l'onglet "Journal Achats 2016" (pour les factures de l'exercice 2016 qui est la Feuil.63) mais là je suis en 2017 et je souhaite exporter mes factures vers le "Journal Achats 2017" qui est la Feuil 78...

En fonction de l'exercice (que j'ai appelé "année_commerciale") peut-on choisir le "Journal Achats" concerné... Voilà, j'espère que j'ai été plus clair, désolé...

La solution d'Eriiiiic ne me semble pas du coup réalisable... et celle de dhany non plus parce que pour une même année je suis obligé de créer plusieurs onglets qui viennent s'intercaller dans l'application (et donc mon journal de l'année n'est pas au même endroit)...

Bonne soirée.

Joe

Le 08/11/2017 à 19:11

Bonjour,

je créé (par macro) de nouvelles feuilles, telles que Journal Achats

Si tu en crées une chaque année ce n'est plus la même, ça ne va pas aller.

Fait plutôt comme ça alors :

Const anneeCommerciale = 2016 'mettre à jour chaque année

Sub test()
    Dim shAchat As Worksheet
    Set shAchat = Sheets("Journal Achats ") & anneeCommerciale
    '
    shAchat.Range("E20") = Sheets("Facture Achats").Range("C70").Value
End Sub

Une fois que tu as déclaré une feuille dans une variable avec Set, utilise ce nom partout.

Tu le changes à un seul endroit et tout le code suit. Fait les autres feuilles sur même principe.

Si cette variable doit être visible dans plusieurs modules il faut la déclarer publique, dans un module standard, et au début en dehors de toute procédure (sous Const de mon exemple)

Public shAchat As Worksheet

Sub machin() ' 1ère procédure
' etc
'...

eric

edit : tu as édité ton post. Du coup je ne sais plus trop si tu peux mettre anneeCommerciale en constante, ou s'il te la faut en variable. Tu vois et tu choisis

Le 13/11/2017 à 20:46

Bonsoir à tous... et j'ai l'impression que je tourne en rond...

Je suis toujours en train d'essayer de solutionner mon problème de la macro qui recopie en fonction de l'année commerciale.

J'ai ce début de code mais il ne fonctionne pas, il bloque sur la ligne "Set shAchat ..." .

Voici le début du code

 Const anneeCommerciale = Range(année_commerciale).Value

Sub Facture_Achats_Journal_Achats_Constante()
' Permet d'exporter les éléments de la facture vers le journal d'Achats de l'année en cours
' L'année est définie en case C4 de la page accueil = année_commerciale

    Dim shAchat As Worksheet
    Set shAchat = Sheets("Journal Achats ") & anneeCommerciale

    'Insertion d'une ligne dans le "Journal Achats ") & anneeCommerciale
    Rows("20:20").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("21:21").Select
    Selection.Copy
    Range("A20").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A20").Select

    'Recopie des informations
    shAchat.Range("E20") = Sheets("Facture Achats").Range("C70").Value

Si quelqu'un peu me dire pourquoi ça ne bloque sur la ligne "Set shAchat = Sheets...

En attendant, bonne soirée.

Joe

Le 13/11/2017 à 21:33

Bonsoir Joe,

Tu a écrit :

J'ai ce début de code mais il ne fonctionne pas, il bloque sur la ligne "Set shAchat ...".

Regarde ta 1ère ligne de code : Const anneeCommerciale = Range(année_commerciale).Value

il y a 2 erreurs en même temps : a) année_commerciale est sans doute un nom défini, et dans ce cas, il faut y faire référence

en l'encadrant de guillemets, comme ceci : Range("année_commerciale") ; b) comme la valeur de ta cellule nommée est une

variable et pas une constante, tu ne peux donc pas l'affecter à une constante ; aussi, j'ai réécrit ainsi le début de ta sub :

Sub Facture_Achats_Journal_Achats_Constante()
' Permet d'exporter les éléments de la facture vers le journal d'Achats de l'année en cours
' L'année est définie en case C4 de la page accueil = année_commerciale

  Dim shAchat As Worksheet, anneeCommerciale As String

  anneeCommerciale = Range("année_commerciale")
  Set shAchat = Worksheets("Journal Achats ") & anneeCommerciale

  With shAchat

    'Insertion d'une ligne dans le "Journal Achats " & anneeCommerciale
     .Rows(20).Insert -4121, 0: .Rows(21).Copy: .[A20].PasteSpecial -4122
    Application.CutCopyMode = False

    ' Attention : la sélection d'une cellule ne marche que pour la feuille active,
     ' donc si c'est sur une autre feuille, il faudra d'abord la sélectionner !
     [A20].Select

    'Recopie des informations
     .[E20] = ['Facture Achats'!C70]

    ' suite de ton code VBA qui utilise shAchat (références débutant par un point)

  End With

  ' suite de ton code VBA qui n'a pas besoin de référencer shAchat

Je te laisse essayer ; merci de me donner ton avis.

Cordialement

Le 13/11/2017 à 22:59

Bonjour,

Difficile de croire que

Const anneeCommerciale = Range(année_commerciale).Value

ne t'ai pas retourné d'erreur...

eric

Le 14/11/2017 à 20:21

Bonsoir Eric, Dhany,

Merci pour votre patience

J'ai testé la Macro de Dhany et la macro bloque sur la ligne :

    Set shAchat = Worksheets("Journal Achats ") & anneeCommerciale

Je n'arrive même pas à récupérer la première valeur à récupérer... Pourtant la cellule nommée année_commerciale est correctement orthographié... Bon, je crois que je vais opter pour un "automatisme" à la main en chaque début d'année

Je vais continuer à bûcher les cours de VBA... ça va prendre du temps

Bonne soirée et merci pour votre aide.

Joe

Le 14/11/2017 à 21:14

Bonsoir Joe,

Tu a écrit :

J'ai testé la Macro de Dhany et la macro bloque sur la ligne :

Set shAchat = Worksheets("Journal Achats ") & anneeCommerciale

Je viens de comprendre pourquoi ; désolé, c'est ma faute, j'aurais dû y penser :

c'est tout bêtement la parenthèse droite qui est mal placée ➯ correction :

Set shAchat = Worksheets("Journal Achats " & anneeCommerciale)

Cordialement

Le 15/11/2017 à 00:31

Bonjour,

Tu as copié trop bien pour pour faire TON code, même mon erreur...

eric

Le 15/11/2017 à 01:28

Bonjour eriiic,

eriiic a écrit :

Tu as copié trop bien pour faire TON code, même mon erreur...

ceci en parlant de la parenthèse droite qui est mal placée dans cette instruction :

Set shAchat = Worksheets("Journal Achats ") & anneeCommerciale

Tu veux donc dire par là que j'ai plagié ton propre code, et c'est à la fois FAUX et VRAI ; je m'explique :

J'ai copié cette ligne à partir du code VBA du demandeur joept69 (son message du 13 novembre à 21:46)

sans m'apercevoir qu'il a lui-même recopié ton propre code VBA (ton message du 8 novembre à 20:11).

Même si joept a recopié ton code, ou même s'il avait fait lui-même l'erreur en question, je prends très

volontiers à ma charge le fait de n'avoir pas détecté et corrigé cette erreur simple, mais je n'admets

certainement pas d'être accusé de plagiat, ni par toi, ni par quiconque !!!

Quand je reprends le code de quelqu'un d'autre, je le cite toujours ; exemple : sujet de danyboy117

« ecrire avec mise en forme », mon message du 13 novembre à 10:22 : j'ai écrit texto, ce que je te

laisse vérifier : « Je te propose le fichier Excel ci-dessous, qui est une reprise modifiée de la solution

de gmb » ; et par simple sens de la déontologie, je le fais même si le code VBA que je reprends est

d'une personne que j'apprécie moins que les autres (en disant ça, je ne parle pas du tout de gmb).

Si tu tombes sur un exemple où j'aurais mis le code de quelqu'un d'autre sans le citer, c'est soit un oubli,

soit que je l'ai fait volontairement car je sais qu'il est évident pour le demandeur qui a déjà vu ce code

qu'il l'a obtenu grâce à un autre contributeur que moi.

Cordialement,

dhany

Le 15/11/2017 à 10:42

Bonjour joept69,

Tu a écrit :

Bon, je crois que je vais opter pour un "automatisme" à la main en chaque début d'année

Ne laisse pas tomber si vite ! j'ai trouvé la cause du problème : tu as recopié le code d'eriiic où une ligne d'instruction comportait une parenthèse droite placée au mauvais endroit ; personne n'est infaillible : tout le monde peut faire une erreur (moi y compris) ; d'ailleurs, à partir de ta recopie de code (ton message du 13 novembre à 21:46), j'ai recopié ensuite cette instruction sans me rendre compte de cette erreur ; voici maintenant la correction :

Set shAchat = Worksheets("Journal Achats " & anneeCommerciale)

Mes excuses pour cette étourderie et le contre-temps occasionné ; cette fois, ça va aller du premier coup ! plus besoin de te résoudre à « un "automatisme" à la main en chaque début d'année » !

En principe, le reste de mon code VBA doit être ok, mais si besoin, n'hésite pas à réclamer ! si ton problème est réglé, merci de passer le sujet en résolu : cliquer sur la coche verte V (à droite du bouton EDIT).

Cordialement,

dhany

Le 15/11/2017 à 19:12

Ahhhh je me disais aussi ! D'habitude ce genre de piège je me les créé moi-même

Ok, j'arrive à lancer la macro et elle m'insère bien la ligne... Le problème cette fois-ci c'est qu'elle ne me récupère aucune donnée.

Je suis censé récupérer la cellule E10 de la feuille "Facture Achats" et qui doit se coller en A20 de mon journal achats de l'année en cours (soit shAchat)... Mais je ne récupère aucune donnée... Je vais creuser !

A bientôt j'espère avec de bonnes nouvelles

Encore merci pour votre aide.

Bonne soirée.

Joe

Le 15/11/2017 à 20:53

Bonsoir Joe,

Dans le code d'eriiic, il y avait cette instruction : shAchat.Range("E20") = Sheets("Facture Achats").Range("C70").Value

Tu l'as reprise telle quelle, puis à partir de ta recopie de code (je précise cela pour eriiic avant qu'il m'accuse de nouveau de plagiat pour une simple malheureuse parenthèse droite mal placée ! ), j'ai mis cette instruction : .[E20] = ['Facture Achats'!C70]

Dans les cas précédents :

a) la feuille shAchat est celle-ci : "Journal Achats " & anneeCommerciale

b) sur cette feuille, en cellule E20, on met le contenu de la cellule C70 de la feuille "Facture Achats"

Or tu as écrit :

Je suis censé récupérer la cellule E10 de la feuille "Facture Achats" et qui doit se coller en A20 de mon journal achats de l'année en cours (soit shAchat)... Mais je ne récupère aucune donnée...

Dans ce cas, l'instruction doit être : .[A20] = ['Facture Achats'!E10]

Cordialement

Le 16/11/2017 à 22:10

Bonsoir à tous et un grand merci, ça semble fonctionner, j'ai testé sur les 3 premières valeurs à copier et ça se recopie correctement... !

MERCI !

Je vais affiner la macro pour faire ça tout proprement

Bonne soirée (et sûrement à bientôt)

Joe

Rechercher des sujets similaires à "macro recopie elements differents onglets annee"