Macro copie et décompte cellules sur plusieurs feuilles

Bonjour,

je voudrais mettre en place une macro compliquée (du moins pour moi) qui copierait tous les feuilles d'un classeur sur un autre et ensuite compterait le nombre de cellules de certaines couleurs.

J'ai un planning (fichier planning 2012).

Je veux copier toutes les pages de ce planning sur un autre classeur. J'y arrive, la macro fonctionne (fichier planning 2012 - CVM). Seulement cela ne fonctionne que pour l'année 2012 et je voudrais la faire fonctionner chaque année sans toucher à la macro sur le planning qui s'appellerait de la même façon mais avec 2013, puis 2014... Le 1er problème est que je ne peux pas toucher pour des questions de droits à ce fichier planning. J'ai donc mis ma macro sur le fichier "planning 2012 - CVM".

Dans la feuille en supplément par rapport au planning, j'ai donc ajouté la date en cellule Q2. Mais je n'arrive pas à l'intégrer dans la suite de la macro.

Une fois ce problème résolu, je continuerai mais macro et reviendrai certainement pour la suite. J'essaierai de la créer seul (au moins une première partie) mais aurai besoin d'aide.

En attendant, je vous remercie par avance pour l'aide sur cette 1° partie.

Bonjour,

Je ne vois guère de différence entre les deux fichiers hormi la feuille ETP qui n'est guère utile puisque tous les liens sont rompus.

De toute façon il faut mettre tout le fichier d'origine ou rien : iil est fort probable que la clef du problème est dans une feuille de paramètre ( peut-être cachée ? )

Il est probable que le fichier ne tient pas en pièce jointe, aussi utilise cjoint pour nous le communiquer.

remarque : les macros CompteCouleurFond et NBC (dans ThisWorkbook) sont identiques, mais NBC ne peux pas fonctionner car elle n'est pas dans le bon module...

Bonjour et tout d'abord merci pour la réponse.

Je vais tenter de m'expliquer plus clairement.

Le fichier planning est créé par une autre personne et je n'ai aucune action dessus.

Je voudrais récupérer toutes les infos par une macro de copie sur le fichier planning CVM. Ensuite, je ferai une macro qui ne gardera que les éléments souhaités (le planning pour HE, le planning pour HD...). Pour le moment pas de problème pour ça.

Les formules ne fonctionnent pas car le planning est vide, les liens ne sont pas actifs car j'utilise un fichier modèle sur le forum. Là encore, ce n'est pas grave car je compte refaire tout ça d'une autre manière.

Mon problème est que j'arrive à faire une macro pour récupérer les feuilles sur le fichier planning 2012. Mais l'année prochaine le plannung s'appelant 2013, il faudrait que je change la macro. Ce que je souhaite c'est que ma macro aille directement sur le planning de l'année souhaitée. Pour cela je voulais indiquer l'information dans la cellule Q2 du planning où se copieront les feuilles mais si cela marche pour l'ouverture, je ne sais pas comment l'appeler pour le fermer.

J'ai mis en lien un planning 2012 complet et un planning 2012 - CVM sans les feuilles copiées. Il faut utiliser la macro copie.

Encore merci

Bonjour,

Ce planning n'a pas l'air très évolué (je parle du fichier 12 mois) et n'est pas perpétuel (évolutif).

Il faut changer toutes les feuilles à la main à chaque changement d'année.

Quand aux WE et fériés comme il n'y a pas de mise en forme conditionnelle ils sont également manuels.

Il est probable que la personne qui l'a confectionné ne s'intéressait qu'à l'aspect comptable des différentes couleurs et pas à faire un calendrier adaptatif.

En conclusion, si l'ergonomie globale te plait, tu peux la garder mais toute la partie calendrier est à refaire chaque année.

A mon avis effectivement, il faut partir sur ton idée d'actualisation chaque année par une cellule qui défini l'année.

Dans ce genre de calendrier toutes les mises à jours se font par formules et mise en formes conditionnelles et non par macros. (Et à mon avis il n'y à guère d'intéret à le faire par macros) Les formules utilises n'étant guère très compliquées.

Tu trouveras sans peine sur la toile des modèles de calendriers perpétuels adaptable à cette présentation mais en l'état ce calendrier ne convient pas car il ne comporte aucune formule permettant l'actualisation du calendrier chaque année.

A+

je te remercie.

Mais comme je le disais, je n'ai aucune action possible sur ce calendrier. J'ai déjà essayé de proposer autre chose, on m'a dit non.

J'ai besoin d'informations dans ce calendrier. Pour cela, je voudrais utiliser un planning modèle (type planning CVM) qui récupère toutes les feuilles du planning. J'y arrive par la macro copie du fichier planning CVM.

Mon problème est que je dois diriger ma macro vers le planning 2012, activer planning 2012, copier, coller sur planning CVM et fermer planning 2012 sans enregistrer.

Tout est bon. Mais l'année prochaine il faudra que je change ma macro.

Ma question est, sur la macro ci-dessous, comment faire une copie sur un classeur "planning (année)" où année pourrait être évolutif?

Sub Copie()

Dim Chemin As String

Application.ScreenUpdating = False

Nom_de_ce_fichier = ThisWorkbook.Name
Windows(Nom_de_ce_fichier).Activate
Sheets("ETP SEMAINE").Activate
Chemin = ThisWorkbook.Path
Workbooks.Open Filename:=Chemin & "\planning " & Range("Q2") & ".xls"

Windows("planning 2012.xls").Activate
Sheets(Array("JANVIER", "FEVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DECEMBRE")).Select
    Sheets("JANVIER").Activate
    Sheets(Array("JANVIER", "FEVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DECEMBRE")).Copy Before:=Workbooks(Nom_de_ce_fichier).Sheets(1)

Windows("planning 2012.xls").Close SaveChanges:=False

End Sub

Bonjour,

J'ai très bien compris mais tu te répètes inutilement. Pourquoi diable veux tu passer par une macro pour créer un classeur et importer des feuilles alors que tu as un classeur modèle qu'il te suffit de l'Enregistrer Sous ..."le nom que tu veux" (manuellement pour créer ton planning 2013.

Si c'est juste pour utiliser cette macro inutile, le bénéfice est bien mince !

De toute façon ce planning est figé tu peux rebaptiser ton Q2 2013, 2014 ou 2015 tu auras toujours en réalité le planning de 2012 sauf si tu te décides à en faire un calendrier dynamique. Mais tu n'en es pas encore là.

Et quand bien même ton fichier modèle serait dynamique tu n'aurais toujours pas besoin de macro pour le renommer en 2013, 2014...

Pour résumer, laisse tomber les complications :

Crée un fichier modèle complet (type CVM) avec les 12 mois et toutes les autres feuilles dont tu estimes avoir besoin. Crées les formules. Fait en sorte que cela soit dynamique en fonction de ton "Q2" et tu n'auras pas bseoin de macro. Chaque année tu auras juste à enregister une copie de ton fichier modèle en planning 2013, planning 2014...

(EDIT] Pour illustrer mon propos, je joins un fichier un peu du même genre

ou le changement d'année se fait dans la feuille param en G1 tout le reste du calendrier se calcule automatiquement sans appel à des macros en fonction de cette cellule. Donc chaque année l'utilisateur renomme ce modèle comme il le veut et YAPUKA travailler. Ce genre de calendrier est valide jusqu'en 2070 environ (Parait-il...) !

A+

ok merci.

C'est bête mais je n'y avais pas pensé... Pourquoi chercher compliqué quand on peut faire simple.

toutefois, juste pour progresser un peu, est-ce que c'est possible juste à travers ma macro de faire ce que je souhaites?

Mon problème est que le classeur de planning 2012 est protégé et je ne peux donc pas utiliser ces données sans faire une copie de toutes les feuilles en utilisant une macro depuis mon fichier modèle

Bonjour,

Mon problème est que le classeur de planning 2012 est protégé et je ne peux donc pas utiliser ces données sans faire une copie de toutes les feuilles en utilisant une macro depuis mon fichier modèle.

Le fichier que tu as mis en pièce jointe (

)n'est pas protégé. C'est bien pourquoi je me demande pourquoi tu te casses la tête pour rien...

Sinon oui, mais ton code est un peu confus et aproximatif, je ne sais pas par quel bout le prendre...

Tu mélanges joyeusement les

ActiveWorkbook

Workbooks.Open

Windows("planning 2012.xls").Activate (dont on ne sait pas trop si c'est le Workbooks qui a été ouvert à la ligne précédente

)

On sait pas trop ce que contient Q2

Donc on sait pas trop ce que sera le fichier cible ( planning " & Range("Q2") +1 & " - CVM.xls" ???)

Mais en fait ça ne sera quand même que la copie de ton CVM renommée car en bout de course tu seras bien obligé de le renommer...

Si la mayonnaise à bien pris ça pourrait peut-être se solder par un

ActiveWorkbook.SaveAs "planning " & Range("Q2") +1 & " - CVM.xls"

... sur la dernière ligne de ta macro.

Mais je ne peux guère être très catégorique. C'est vraiment du code un "peu flou".

As-tu vu l'EDIT et le fichier joint dans ma réponse précédente ?

A+

je te remercie.

Oui j'ai vu l'edit et j'ai un message d'erreur dès l'ouverture du fichier et lorsque je vais sur certaines feuilles.

Je ne vois pas ensuite où mettre l'année pour qu'il s'adapte.

Pour mon problème.

Mon fichier source est "planning (année)". Dans le cas présent l'année est 2012.

Mon fichier terminal doit être planning (année) - CVM".

Dans la macro, j'appelle mon fichier source par

Workbooks.Open Filename:=Chemin & "\planning " & Range("Q2") & ".xls"

Q2 est une cellule du fichier terminal où je renseigne l'année.

Ensuite je veux fermer le fichier source par

Windows("planning 2012.xls").Close SaveChanges:=False

Je ne veux pas fermer mon fichier terminal de suite car je voudrais continuer la macro par d'autres actions.

Mais le problème est que je nomme "planning 2012".Ma macro ne fonctionne donc que pour cette année. Comment intégrer Q2 du fichier terminal pour fermer le fichier source sans enregistrer les modifications?

Mais non, tu patauges dans tes approximations.

Q2 ne peux pas avoir pour valeur 2012 :

2012 est ton fichier source donc tu n'as pas besoin de Q2 pour savoir que ton fichier source est toujours le même.

Workbooks.Open Filename:=Chemin & "\planning_2012.xls" 'conviendra dans tous les cas.

Ensuite quand tu voudras fermer ton fichier source c'est :

Workbooks("planning_2012.xls").Close False 'qui s'applique et pas Windows (à rayer de ton vocabulaire)

Concernant ton fichier CVM_2012 Modèle qui contient la macro et qui est destiné à devenir le fichier terminal (on dit plutot "cible")

il devrait contenir une cellule Q2 qui indique que tu veux construire le fichier 2013

Le fichier 2012 ne subit aucune modification puisque tu te bornes à le copier dans ton fichier CVM_2012 qui sera ensuite renommé en CVM_2013...

Dans ce cas la dernière ligne de ta macro sera : (même si tu veux rajouter des lignes de code avant...)

ActiveWorkbook.SaveAs "planning " & Range("Q2") & " - CVM.xls"

L'année prochaine tu pourras ré_utiliser ton fichier CVM_2012 modèle, mettre 2014 dans ton Q2 et créer ton CVM 2014.

Bon enfin ça c'est si vraiment tu veux te compliquer la vie. Parce que moi les 12 feuilles vierges je les mettrai directement dans le CVM 2012 et TORAIPUKA le renommer comme tu veux tous les ans sans te casser la tête à macroter pour rien.

C'est clair ?

je patauge surtout dans mes explications.

Mon fichier source n'est pas toujours le même. Le nom va changer chaque année.

Planning 2012, aujourd'hui, Planning 2013 dans quelques mois.

Voilà ce que je voudrais et comment j'ai commencé.

J'ouvre mon fichier cible (Planning CVM)

J'ouvre le fichier source nommé toujours de la même manière : planning & Q2 ensuite je copie et je referme sans enregistrer planning et Q2.

En Q2 sur le fichier cible j'indique l'année. Comme c'est le fichier que j'ouvre la macro va bien chercher Q2 et m'ouvre bien aujourd'hui planning 2012.

Par contre une fois planning 2012 ouvert il devient actif et je ne sais pas le fermer sans enregistrer les changements sans le nommer. Si je le nomme, je dois l'appeler 2012 et la macro ne fonctionnera pas l'année prochaine.

Workbooks.Open Filename:=Chemin & "\planning_2012.xls" 'ne fonctionnera pas avec planning 2013

Workbooks("planning_2012.xls").Close False ' ne fonctionnera pas avec planning 2013

je te remercie de ton aide et j'essaie de m'expliquer clairement même si cela semble ne pas marcher.

Je passe la main. Peut-être que quelqu'un sera plus éveillé que moi.

je ne suis peut-être pas très clair dans mes explications....

Bonsoir

matt31 a écrit :

Mon problème est que le classeur de planning 2012 est protégé

Une fois la copie faite les feuilles seront toujours protégées

Un essai sans être sur que cela corresponde à ce que tu veux

Avant le lancement de la macro le fichier ne contient qu'une seule page : ETP SEMAINE

Le début de ton fichier source DOIT ÊTRE (sinon modifier dans la macro) "Planning "

A vérifier

je te remercie, c'est exactement ce que je voulais.

Les feuilles sont encore protégées mais je ne vais pas faire de modif sur ces feuilles, je vais faire un décompte depuis la feuille ETP SEMAINE.

La 1° étape, je sais la faire (normalement) et je risque de revenir ici pour l'étape suivante.

Un très grand merci à vous 2.

Bonjour,

je reviens sur mon sujet car je ne m'en sors pas et je pense m'être trompé de piste pour résoudre mon problème. Il semble qu'il faille repartir de zéro.

Voici donc mon problème.

J'ai un planning protégé sur lequel je ne peux pas agir. Ce planning n'est pas évolutif et doit être refait chaque année. Ma hiérarchie y fait en plus des modifications dans la présentation.

Sur ce planning, les horaires sont représentés par des couleurs (orange, bleu et vert). Le contenu de la cellule n'a pas d'importance. Les noms sont regroupés par pôles (HD, CVM, HE...) et c'est ce critère qui est important pour moi.

Je dois en effet calculer la charge de travail sur chaque pôle en fonction des personnes présentes chaque semaine. Pour cela, je pensais compter le nombre de cellules colorées et diviser pour le nombre de jours ouvrés (pas de travail le week-end ou les jours fériés).

J'ai des fichiers comptabilisant le nombre de dossiers traités mais je n'arrive pas à récupérer et calculer le nombre de personnes présentes chaque semaine pour chaque pôle (et les emplacements des pôles sur le planning sont parfois changés...).

Les semaines sont souvent partagées entre 2 mois et donc sur 2 feuilles du classeur.

Je vous remercie par avance pour vos conseils et votre aide.

Bonjour,

matt31 a écrit :

Les emplacements des pôles sur le planning sont parfois changés...)

Pour moi c'est rédhibitoire : Les protections de feuilles et de classeurs sont pas faites pour les chiens !

Si tu n'as pas la maîtrise du classeur, inutile de t'échiner : YFO le donner à celui qui est capable de le modifier !

A+

le problème c'est que l'on ne me donnera pas le mot de passe et que la personne qui s'occupe du planning ne veut pas que j'y touche. J'ai toutefois un travail à faire et l'on ne dépend pas de la même hiérarchie. Du coup, je suis bloqué.

C'est pour cette raison, que dans un premier temps je souhaitais copier les données sur un fichier auxquel je pouvais ajouter des feuilles. Mais je ne m'en sors pas.

N'y a-t-il pas de moyen pour copier les plages m'intéressant sur un fichier modèle et ensuite faire le décompte des cellules m'intéressant? J'ai l'idée mais pas les connaissances pour le faire à condition évidemment que ce soit réalisable

Bonsoir,

Je comprend mieux ton problème, mais je ne vois guère ce qu'on peut faire pour toi ?

Tu parles de feuilles protégées mais tu as maintenant des feuilles déprotégées : Dans le fichier joint on n'a aucune protection sur les feuilles, mais par contre on a des liens rompus.

Je suppose que toi tu dois être en mesure de récupérer tous les chiffres. Au besoin avec un Copier/Collage spécial valeur.

Après, si la création du pole gériatrie te gène rien ne t'empêche de rajouter les lignes manquantes pour les 8 premiers mois de manière à avoir des dispositions identiques qui sont le préalable à faire une consolidation 3D sur ta feuille récap.

Si tu as la possibilité de figer les données, j'ai le sentiment qu'on peut déplacer les plages AH:AW de quelques colonnes sur la droite.

Ainsi ça va te libérer quelques colonnes pour ramener à la "mano" les quelques jours qui manque pour les semaines "à cheval" sur 2 mois. Tout ça ce n'est que du petit bricolage... Mais je ne sais même pas si j'ai bien compris le fond de ton problème.

Après, je ne sais pas. A toi de préciser qu'est-ce que tu attends.

A+

bonsoir et merci.

J'ai pu mettre un fichier non protégé en recréant toute la mise en forme manuellement et en recopiant uniquement les couleurs du planning.

Ci-joint, j'ai refait un planning de 2012 vierge et au format identique sur toutes les feuilles. Peut-être qu'en présentant un planning évolutif ou en y mettant des macros de moi même sans trop toucher à la structure cela peut être accepté.

Je vais tenter d'expliquer mes besoins.

Pour chaque pôle (HD, CVM, ILM, Neuro...) je dois calculer la charge de travail. J'ai déjà un fichier calculant le nombre de dossiers par semaine. Je voudrais y rattacher les données du planning afin de faire une moyenne par semaine de dossiers traités par rapport au nombre de personnes présentes. Le but étant d'ajuster au mieux les effectifs.

Pour compter les personnes présentes, je pense compter le nombre de cases colorées par semaine et le diviser par le nombre de personnes théorique (en comptant le nombre de case non vides dans les 6 lignes sous le nom du pôle).

Afin d'éviter de faire tout ça manuellement, je voudrais récupérer pour chaque pôle le planning et ensuite faire le décompte ci-dessus par semaine.

Chaque planning s'appelerait planning "nom du pôle" - 2012 et mon calcul de charge de travail renverrait directement sur ce fichier. C'est que j'avais mis en place sur mon premier fichier dans la feuille ETP SEMAINE avec la fonction CompteCouleurFond.