Création de macro VBA - Suivi de prestataires

VBA
1

Bonjour,

Je souhaiterai créer une macro à partir du fichier ci-joint.
PRESTA_2021.xlsm (59,1 Ko)

L’objectif de ce fichier est de suivre l’état des contrats de prestation de la société sur une même année.
Dans la feuille « Etat contrats » se trouvent :

  • Tous les contrats depuis le début de l’année avec des infos service, manager, dates, tarif journalier etc…
  • Une différenciation des contrats de presta : presta / presta intervention
  • Un état du contrat, si F = Fin, si R = Renouvellement, si CS = Contrat entreprise, si « Vide » = contrat en cours.
  • Les mois de présence en fonction des dates renseignées (col P à AA)

L’idée c’est d’avoir deux boutons :

  • 1 qui servirai à créer un fichier de renouvellement uniquement pour les type de contrat : presta intervention (fichier ci-joint que je détaillerai ensuite)
    Renouvellement_Presta_M_M+1_M2.xlsx (13,2 Ko)
  • 1 qui servirai à dupliquer les lignes de contrat dans l’onglet « Facturation » en fonction du mois de présence. Si il y a un 1 dans le mois de janvier et pas ailleurs, alors dupliquer la ligne dans l’onglet « Facturation » et ajouter en colonne A : 01/01/AAAA. Si il y a un 1 de janvier à décembre, alors dupliquer la ligne autant de fois qu’il y a de mois (soit 12) dans l’onglet « Facturation » et ajouter pour chaque ligne le mois concerné.

L’onglet « Facturation » garde la même structure que l’onglet « Etat contrats » mais on ajoute une 1ère colonne qui permet d’indiquer le mois considéré de la facturation + les colonnes en bleu s’ajoutent automatiquement qu’une ligne de facturation est crée.

Pour le fichier de renouvellement, l’idée c’est de cliquer sur le bouton macro pour créer un nouveau fichier qui permettra de communiquer avec les managers concernés sur les prolongations de contrat.
Ce fichier ne filtre que les type de contrat (col D) qui contiennent « Presta Intervention » et que les contrats dont l’Etat (Col O) est vide.
Ainsi, on obtient le fichier joint par rapport à ce qui est renseigné dans le Fichier Presta_2021.
L’idée est d’ajouter les deux mois suivants la date de fin de contrat avec un calendrier plus précis, soit les colonnes O à BL. et ainsi que le manager puisse remplir par un P ou un D la prolongation à venir. La légende étant dans l’onglet « Légende ».

J’espère avoir été claire dans mes explications. Si besoin, je reste disponible.

Merci d’avance pour votre aide.

2

Bonjour

Commençons par la première étape le fichier Renouvellement

Je te propose de créer un modèle nommé « Renouvellement_Presta_Modèle.xlsx » et à mettre dans le même répertoire que le fichier avec les boutons « PRESTA_2021.xlsm »
Dans ce modèle l’ensemble des colonnes à remplir avec les entêtes

Je te joins le fichier « Renouvellement_Presta_Modèle.xlsx » ainsi que le fichier « PRESTA_2021.xlsm » avec un traitement raccordé sur le bouton « Renouvellement Presta Intervention » qui ouvre le fichier « Renouvellement_Presta_Modèle.xlsx » l’enregistre dans le même répertoire sous le nom « Prestat_16072021.xlsx » (Prestat_date du jour) puis inclus de la colonne A à N ligne 5 l’enregistrement du fichier « PRESTA_2021.xlsm » qui répond aux 2 conditions :

colonne C avec la donnée « PRESTA INTERVENTION »
colonne O vide

Puis construit la liste des jours colonne O à BL avec les dates les numéros de semaine et la première lettre de chaque jour en rapport

Attention pas de cellule fusionnée difficile à gérer surtout celles-ci fluctuantes en fonction des semaines considérées le numéro de celles-ci est répété

Le traitement considère 2 mois soit pour l’exemple du fin de contrat at 09/07/21 établit la liste des jours jusqu’au 09/09/21 et non jusqu’au 17/09/21 comme donné dans ton fichier « Renouvellement_Presta_M_M+1_M2.xlsx » dépassant ce délais

Je suppose qu’un seul enregistrement de ton fichier « PRESTA_2021.xlsm » est à traiter car la liste des jours à établir ne peut concerner qu’une seule date de fin de contrat

Sinon il faut m’expliquer comment traiter plusieurs fin de contrat concernant cette liste

Je te joins les 2 fichiers :

Renouvellement_Presta_Modèle.xlsx
PRESTA_2021.xlsm

tu enregistres les 2 dans un seul et même dossier

Puis du deuxième tu exécutes le traitement du bouton « Renouvellements presta Intervention »

Un fichier sur la base de celui « Renouvellement_Presta_Modèle.xlsx » sera créé enregistré sous « Prestat_JJMMAAAA.xlsx » dans le même répertoire et enrichi de toutes les données
Il suffira à la fin du traitement d’aller l’ouvrir pour en vérifier le contenu

Fais des tests et dis moi

Renouvellement_Presta_Modèle.xlsx (11,9 Ko)
PRESTA_2021.xlsm (68,9 Ko)

3

Bonjour,

Ce process fonctionne bien sur la partie renouvellement et me convient bien.
Par contre, j’ai fait le test en ajoutant une nouvelle ligne de « Presta Intervention » dans le fichier « PRESTA_2021.xlsm » en ligne 12 et quand j’exécute la macro « Renouvellement Presta Intervention », il n’y a qu’une ligne reportée. Normal ?
Fichier joint avec mon ajout :
PRESTA_2021.xlsm (64,4 Ko)

Est-ce que ce que je viens de dire ci-dessus fait référence à ce commentaire ? Dans ce cas, ce qui peut être fait, c’est de proposer 2 mois (sur le fichier renouvellement) à partir de la date de fin la plus proche. En l’occurrence, dans l’exemple, c’est 09/07.

Ok pour ça, ça me va.

Pour la forme, le fichier renouvellement peut-il s’appeler : « Renouvellement_Presta_DDMMAAAA » ?

Merci d’avance.

4

Bonjour
Ravi que cela te convienne

« Est-ce que ce que je viens de dire ci-dessus fait référence à ce commentaire ? Dans ce cas, ce qui peut être fait, c’est de proposer 2 mois (sur le fichier renouvellement) à partir de la date de fin la plus proche. En l’occurrence, dans l’exemple, c’est 09/07. »

C est bien ce que j ai voulu dire dans mes propos
Je ne vois pas comment gérer la liste des jours (2 mois) avec plusieurs date de fin contrat differentes servant de base à sa mise en place
La il faut que tu m expliques comment tu le conçois
Sinon pourquoi ne pas réaliser pour chacun d eux leur propre restitution avec leur liste de jours dans un onglet spécifique ce serait moins hazardeux que d essayer de les combiner dans le même

Qu en penses tu ?

Merci de me dire

« Pour la forme, le fichier renouvellement peut-il s’appeler : « Renouvellement_Presta_DDMMAAAA » ? »

Aucun souci sera mis en place dans la prochaine version

Dans l attente de te lire

5

Ci-joint une nouvelle version sur le principe d’un onglet par enregistrement à intégrer
Sur la base de ton dernier fichier avec 2 enregistrements à considérer fichier Renouvellement 2 onglets créé :
Renouvellement-1
Renouvellement-2

Avec pour chacun sa propre liste de jours établie sur 2 mois

Testes et dis moi

PRESTA_2021-2.xlsm (70,0 Ko)
Renouvellement_Presta_19072021.xlsx (17,2 Ko)

6

Bonjour,

Merci pour l’essai.

Comme je l’indiquais dans mon commentaire précédent l’idée c’est de traiter tous les renouvellements du fichier à partir de la date de fin la plus proche + 2 mois.
Dans l’exemple du fichier la date de fin la plus proche est le 09/07 donc deux mois à partir de cette date et on laisse le 2ème renouvellement à compter de cette date pour avoir la liste de tous les renouvellements sur le même onglet.

7

Bonjour
Donc j ai tout faux avec autant d onglet que d enregistrements à traiter
Dommage cela me semblait répondre au mieux à la situation
Dans ta manière de gérer qu entends tu par date de fin la plus proche ?
Plus proche de quoi ?
Quid des données de chaque enregistrement concernés ?

Merci pour ces précisions

8

Je n’ai pas saisi votre dernier commentaire :
« Donc j ai tout faux avec autant d onglet que d enregistrements à traiter
Dommage cela me semblait répondre au mieux à la situation »

Pour moi, il n’y a pas autant d’onglet que d’enregistrement à traiter cad :

  • le fichier renouvellement devrait comporter toutes les lignes « Presta Intervention » dont l’état est vide
  • le fichier renouvellement devrait comporter un seul onglet avec toutes ces lignes identifiées ci-dessus
  • le fichier renouvellement devrait comporter un ajout de colonnes O à BL ressemblant à un calendrier sur les 2 mois suivant (voir fichier)
    En ce qui concerne les deux mois suivants, je propose que soit affiché les 2 mois suivants selon la date de fin de contrat de l’une des lignes la plus proche de la date à laquelle je clique sur le bouton macro « renouvellement Presta intervention » (donc exemple : si c’est le 07/07, la date la plus proche est le 09/07 donc les 2 mois à prendre en compte sont ceux à partir du 09/07 pour les 2 lignes de mon précédent envoi de fichier).

« Quid des données de chaque enregistrement concernés ? »
Je ne comprends pas la question, à quoi fait-elle référence ?

Merci d’avance,

9

Je n’ai pas saisi votre dernier commentaire :
« Donc j ai tout faux avec autant d onglet que d enregistrements à traiter
Dommage cela me semblait répondre au mieux à la situation »

Je t’ai transmis une proposition qui mettait chaque enregistrement dans son propre onglet et ainsi de ne pas avoir à choisir en fonction de la date de fin de contrat la plus proche
L’as tu regardé et testé ?

Mettre sur un même onglet oblige à faire un choix sur ce critère :

"suivants selon la date de fin de contrat de l’une des lignes la plus proche de la date à laquelle je clique sur le bouton macro « renouvellement Presta intervention "

Je ne sais pas si le nombre de lignes à analyser pour faire ce choix sera important mais cela va alourdi le traitement
De plus en fonction du jour de la manipulation ce choix peut être différent

Mais indépendamment de ce choix pour établir la liste des jours sur 2 mois je répète ma question quid des données de chaque enregistrement colonne A à N du fichier Renouvellement ?
Sont ils tous à recopier ?

Merci pour tes réponses

10

Oui, je l’ai testé et je trouve que d’avoir un onglet par ligne est encore plus alourdissant pour un manager plutôt que d’avoir un seul onglet avec tous ses collaborateurs. Il n’y en aura jamais plus de 10 lignes.
C’est pour cette raison que je propose d’avoir seulement les 2 mois suivants la date de fin de contrat la plus proche. La fois suivante cette date sera passée et on aura à nouveau une nouvelle date de fin auquel il faudra ajouter 2 mois.

Oui, tous ceux qui sont en type de contrat : « Presta Intervention » et état : « vide » sont à recopier dans ce fichier renouvellement.

11

J’ai donc selon tes désirs mis tous les enregistrements dans le même onglet et à partir de leur date de fin de contrat calculé le nombre de jours qui les séparent de la date du jour pour cibler celle la plus proche
Nous somme le 20/07
J’ai un enregistrement avec une date de fin de contrat au 09/07 et un deuxième enregistrement avec une date de fin de contrat au 30/07

C’est le 30/07 le plus proche 10 jours
Celle du 09/07 est distante de 11 jours

Donc la liste des jours sur 2 mois est établit sur la base de l’enregistrement avec sa date de fin au 30/07/21

Question :

Qu’en est il pour des enregistrements ayant des dates de fin différentes mais avec le même écart qui les séparent de la date du jour ?

Exemple sur ces 2 enregistrements comme date de fin de contrat :

10/07/21 et 30/07/21

Lequel il faut cibler pour établir la liste des jours sur 2 mois

Merci de me dire

Ci-joint une nouvelle version prenant en compte l’ensemble des enregistrements répondant aux conditions et dont la liste des jours sur 2 mois est établit sur la base de celui dont la date de fin est au plus proche de la date du jour (dans l’exemple que tu as fourni l’enregistrement ayant la date de fin de contrat au 30/07/21)

Testes et dis moi

PRESTA_2021-3.xlsm (84,7 Ko)

12

Bonjour,

J’ai testé le nouveau fichier avec le lancement de la macro et voici le résultat. les dates ne correspondent pas aux dates de fin de contrat du fichier.
Renouvellement_Presta_21072021 après macro lancée.xlsx (13,8 Ko)
L’idée c’est d’avoir ce résultat (fichier ci-après) avec les deux contrats et les dates renseignées à la date la plus proche de la fin de contrat la plus proche :
Renouvellement_Presta_21072021 résultat souhaité.xlsx (13,2 Ko)

Comme indiqué, il faut prendre les 2 mois à partir de la date de fin de contrat la plus proche soit le 10/07/21 dans votre exemple.

Restant disponible
Et merci,

13

Bonjour
« Comme indiqué, il faut prendre les 2 mois à partir de la date de fin de contrat la plus proche soit le 10/07/21 dans votre exemple. »

Dans mon exemple 10/07/21 et 30/07/21 en date du 20/07/21 les 2 dates on le même écart soit 10 jours pour les 2 donc il n y en a pas une plus proche les 2 peuvent être considérées comme étant la plus proche
Je ne peux donc me satisfaire de ta réponse
Pour tes 2 exemples fournis un avec comme date de fin le 09/07 l autre comme date fin le 30/07 c est ce deuxième qui est la plus proche à ce jour soit 9 jours d écart pour 12 jours pour le premier nous sommes le 21/07 et chaque jour qui passe diminue l écart du second tandis qu accroît celui du premier
Selon tes indications choix de la date la plus proche c est donc l enregistrement du 30/07
Dans le cas contraire il faut m apporter des explications car je ne comprends pas la logique

C est indispensable pour que je te propose le traitement en rapport

Merci de m éclairer

14

Il s’agit de la date la plus proche à laquelle j’exécute le fichier donc dans l’exemple vu que le contrat est au 09/07, le fichier sera forcément exécuté avant cette date.
Ce sera plus simple, je viens de changer les dates dans le fichier de base « PRESTA_2021-3 » en considérant que je veux exécuter la macro de renouvellement aujourd’hui (21/07) pour récupérer les informations du manager avant la date de fin de contrat du 23/07. Donc dans ce nouvel exemple, la date de fin de contrat la plus proche est bien le 23/07 et il faut donc appliquer les 2 mois supplémentaires à partir de cette date.

PRESTA_2021-3.xlsm (60,1 Ko)

15

Ok cela me convient mieux

Ci-joint ton fichier répondant à tes derniers critères exprimés

La liste des jours sur 2 mois est bien réalisée sur la base de l’enregistrement avec la date de fin de contrat au 23/07

Dans ton exemple cet enregistrement est le premier dans le fichier « Renouvellement_Presta_21072021.xlsx »
Mais sa position est en fonction de l’ordre de ceux-ci dans le fichier « Presta_2021 »
Si celui-ci n’est pas le premier il en sera de même dans le fichier Renouvellement
Malgré tout la liste des dates sera construit sur la base de sa date de fin de contrat qui ne se retrouvera pas en tête
Ce n’est donc pas dans cette hypothèse la date en regard de la liste des jours qui sera la référence mais celle de la ligne de l’enregistrement qui correspond à la date la plus proche pouvant même être la dernière

Si cela pose une difficulté pour la situer je peux lui adjoindre une couleur de fond

A toi de me dire

Ci-joint ton fichier adapté

Testes et dis moi

PRESTA_2021-4.xlsm (66,3 Ko)

16

Pour l’ordre, pas de souci pour moi si dans le fichier de renouvellement la date de fin la plus proche se retrouve être en dernière ligne. Par contre, peut-on recopier la même mise en forme que la première ligne autant de fois qu’il y a de ligne (notamment avec la liste déroulante) ?

Merci bcp,

17

Ton fichier ci-joint avec les mises en forme et les listes déroulantes colonne E et J

Pour la colonne E celle-ci fait appel à une liste de données onglet « Paramètres » colonne M
Il faut rajouter cet onglet avec cette liste de données dans cette même colonne dans le fichier modèle

Je joint donc ces 2 fichiers (le fichier modèle sera à remplacer)

Testes et dis moi

PRESTA_2021-5.xlsm (67,3 Ko)
Renouvellement_Presta_Modèle.xlsx (14,4 Ko)

18

Ok, merci mais ça ne prend pas en compte la mise en forme des colonnes de O à BL (avec liste déroulante) ?
Pour chaque ligne supplémentaire de « Presta Intervention » aurais-je cette même mise en forme dès ajout d’une ligne ?
Merci,

19

Re-bonjour,

Et, pour la seconde partie de ma demande, qu’est-il possible de faire ?
En vous remerciant d’avance,

20

Bonjour

« Ok, merci mais ça ne prend pas en compte la mise en forme des colonnes de O à BL (avec liste déroulante) ? »

Les données récupérées de chaque enregistrement sont celles de la colonne A à N

Les colonnes suivantes étant celles de la liste des jours sur 2 mois

Si tu souhaites avoir pour chaque enregistrement toutes les données de la colonne A à BL tout d’abord les données sources s’arrêtent à la colonne AA et non jusqu’à BL :

image
image822×316 71.8 KB

De plus dans le fichier cible je devrais recopier les données d’enregistrement après les lignes de la liste des jours car celles-ci occupent les colonnes à partir de O sur 4 lignes

A moins que je les décales

A toi de me dire éventuellement me donner un exemple de la présentation que tu attends

Pour ton 2° point il faut m’apporter des précisions et éclairage
Lorsque tu parles de dupliquer c’est de réaliser un clone d’une ligne existante dans l’onglet « Facturation »

Exemple la première ligne onglet « Etat contrats » ligne 7 a colonne P mois de janvier le chiffre 1
je dois onglet « Facturation » pour un enregistrement de ce mois en colonne A même Nom Prénom colonne B réalisé un clone de la ligne (ligne 2) entière ou d’un nombre limité de colonnes lesquelles à réaliser à la suite immédiate ou à la fin des enregistrements

Sinon merci de spécifier la duplication à réaliser avec un exemple à l’appui

« Pour chaque ligne supplémentaire de « Presta Intervention » aurais-je cette même mise en forme dès ajout d’une ligne ? »

Le traitement « Renouvellements Presta Intervention » pour les enregistrements de l’onglet « Etat contrats » qui répondent aux conditions récupère l’intégralité de leurs données colonnes A à N avec toute leurs mise en forme et listes déroulantes défini à l’identique dans le fichier Modèle