Amélioration Code + Récupérer Valeurs dans fichiers fermés

Bonjour à tous,

Mon filleul est en stage et doit créer des macros.

Dans le répertoire joint, il y a :

• 2 classeurs mensuels (CMUMR0110 et CMUMR0210) - Tous ces classeurs mensuels sont nommés sur le même principe : CMUMRmmaa.
• Un classeur SUIVI.

Dans le répertoire réel, il y a une quarantaine de classeurs mensuels.

1 - Il a créé une macro dans chaque classeur mensues qui récupère dans la feuille MUTUELLE, la somme correspondant à chaque numéro de mutuelle. Je trouve cette macro très (trop ?) compliquée. Est-il possible de la simplifier? Actuellement le résultat de la macro apparait en colonne X - Dans la colonne C, c'est moi qui ai mis une formule.

2 - Dans le classeur SUIVI, il faudrait un code afin de récupérer les totaux de chaque mois (sur fichiers fermés)

Merci de votre aide.

Amicalement

Nad

Bonjour Nad, Forum,

Pour le point 1)

il y a des feuilles masquées, il faut les placer en 1er , "Mutuelle" aussi,

on boucle sur les feuilles derrière

Sub SommeBoucleFeuilles()
Dim Lg%, Sh$, J As Byte, i%
    Application.ScreenUpdating = False
        Lg = Range("b65536").End(xlUp).Row
        Range("c3:c" & Lg).ClearContents
        '------ les feuilles à Sommer sont à placer en dernier -------
        '------ ici on commence à la 4ème feuille jusqu'à la dernière -------
    For J = 4 To Worksheets.Count
        Sh = Worksheets(J).Name
        For i = 3 To Lg
            Range("c" & i) = Range("c" & i) + WorksheetFunction.SumIf(Range(Sh & "!A:A"), Range("b" & i), Range(Sh & "!L:L"))
        Next i
    Next J
End Sub

pour le 2ème point, pourquoi ne pas mettre cette feuille dans le même classeur ?

Amicalement

Claude

Bonsoir Claude

Merci de ton aide.

'------ les feuilles à Sommer sont à placer en dernier -------

OK ; par contre, je ne connais pas le nombre total de feuilles dans chaque fichier. Dans ton code, on démarre de la feuille 4 jusqu'à la fin. Il se peut qu'il y ait plus de feuilles dans un des fichiers.

Est-il possible, puisqu'on connait le nom des 3 feuilles à sommer (AUTREOC MG Soldés), que le code débute par placer ces feuilles en position 1 2 et 3 ? Le code serait modifier de la sorte :

For J = 1 To 3

pour le 2ème point, pourquoi ne pas mettre cette feuille dans le même classeur ?

Je ne te suis pas.

Le classeur SUIVI est la compilation de tous les feuilles MUTUELLE des autres classeurs du répertoire. (une colonne par mois - donc une colonne par classeur)

Dans mon 1er post, j'ai parlé de feuilles ; j'aurais dû écrire classeurs. Je modifie pour une meilleure compréhension.

A te relire

Bien amicalement

Nad

Bonsoir Nad, Claude, le forum

Avant de me lancer (peut-être demain)j'aurais besoin d'être rassuré:

Est on sûr que les numéros de mutuelles correspondront toujours ligne par ligne entre les feuilles "mutuelle" et la liste sur le classeur suivi ?

(dans la feuille "soldés du premier fichier, il y a une mutuelle (07...?)qu'on ne retrouve pas dans la liste page "mutuelle")

bonne soirée

re Nad, Bonsoir Michel,

pour le point 1) j'avais compris que c'était là les 40 feuilles,

donc avec ce nouveau code, peu importe la position des feuilles,

Attention à l'orthographe de ces 3 onglets (identiques sur tous les classeurs), j'ai mis un compteur pour vérif.

Sub SommeBoucleFeuilles()
Dim Lg%, Sh$, J As Byte, i%, Ct As Byte
    Application.ScreenUpdating = False
        Lg = Range("b65536").End(xlUp).Row
        Range("c3:c" & Lg).ClearContents
    For J = 1 To Worksheets.Count
        Sh = Worksheets(J).Name
        If Sh = "AUTREOC" Or Sh = "MG" Or Sh = "Soldés" Then
            For i = 3 To Lg
                Range("c" & i) = Range("c" & i) + WorksheetFunction.SumIf(Range(Sh & "!A:A"), Range("b" & i), Range(Sh & "!L:L"))
            Next i
                Ct = Ct + 1
        End If
    Next J
        If Ct < 3 Then MsgBox ("Erreur !  " & Ct & "  feuilles seulement sont sommées")
End Sub

Pour le reste, je ne suis pas sûr d'être à la hauteur, je regarde quand même

Claude

Re

Michel, excellente remarque. J'essaye de joindre mon filleul afin qu'il nous dise ce qu'il en est.

Claude : ton code est parfait.

Pour le classeur SUIVI, je pense que ça va être coton.

Amicalement

Nad

Nad,

Pour le suivi, tu veux faire çà sur fichiers fermés,

J'imagine que c'est pour ne pas avoir à ouvrir 40 fichiers, ok

mais si tu procède autrement, à savoir:

à partir du fichier mensuel, il suffirait d'ajouter dans mon code 3 ou 4 lignes pour mettre

directement les valeurs dans la bonne colonne du fichier suivi.

il n'y aurait que les 2 fichiers concernés d'ouverts

çà serait jouable pour toi ?

Claude

Claude,

à partir du fichier mensuel, il suffirait d'ajouter dans mon code 3 ou 4 lignes pour mettre

directement les valeurs dans la bonne colonne du fichier suivi.

Ce qui m'a fait réfléchir. Il est vrai que maintenant, pour mettre à jour les 40 fichiers il faut les ouvrir un a un pour la 1ère macro.

Nouvelle question : est-il possible de boucler le code sur les 40 classeurs ?

Le code serait placé dans SUIVI. Il ouvre tous les classeurs CMUMRxxxx, le code roule (avec les 3/4 lignes en plus dont tu parles), et on les referme tous sauf SUIVI.

Tu en penses quoi ? C'est faisable ?

Nad

re,

çà va surement compliquer, mais pas impossible,

Peut-on dire que les N° de mutuelle (colonne B) sont identiques et triés pareil sur tous les classeurs ?

En suivant ton idée, en lançant la macro est-on sûrs que le fichier mensuel existera (crée) ?

La macro ouvre un par un les fichiers CMUMRxxxx, met les sommes dans Suivi, ferme et ouvre le

suivant etc.. (en boucle)

la nuit portant conseil, je regarde ça demain matin,

Amicalement

Claude

Bonjour tout le monde

Nad:

dans le classeur "suivi" il n'y a que 4 mois de représentés: reste on toujours sur les 4 derniers mois?

si oui, l'ouverture-fermeture des classeurs source proposée par Claude est envisageable. Pour ma part mon idée est d' utiliser ADO justifié par la rapidité d'exécution et/ou par la recherche dans 40 classeurs.

tu dis...

Bonjour Michel

Les colonnes sont figées à partir de la B ; elles sont donc toutes présentes et donc toutes à remplir.

Prévoir que chaque mois il y aura un fichier mensuel supplémentaire ; donc aller chercher dans tous les fichier "CMUMR*". (il faut peut-être changer les titres des colonnes dans SUIVI dans un format qui permettrait d'aller dans le bon fichier)

Amicalement

Nad

Bonjour Nad, Michel, Forum,

Là je suis un peu largué !

les 40 fichiers représentent + de 3 ans,

------ 2 questions -------

Si on te suis, çà veut dire que les valeurs évoluent dans ces fichiers, même les anciens ?

et qu'il faudra les actualiser tous les mois ? + insérer une nouvelle colonne (nouveau mois)

Bonne journée

Claude

Re

Oui Claude : les valeurs peuvent évoluer dans chaque classeur et chaque mois il y aura un nouveau classeur (tant qu'il sera possible d'ajouter des colonnes dans SUIVI)

J'ai eu l'info de mon filleul : les numéros de Mutuelles ne bougent pas : toujours les mêmes et dans le même ordre.

Nad

re,

J'ai bidouillé les en-têtes, vois si tu peux compléter le tableau, améliorer ou modifier

je ne sais pas encore où je vais !

Claude

Re,

Tu m'a demandé de faire le report de tous les mois existants ("les mois sont figés")... mais comme Claude je me pose des questions

dans "suivi", il y a des trous dans les mois par ex fev 08, avril08, juin08,juillet08 il y a donc "que" 21 classeurs;

j'ai modifié le mode d'inscription des mois pour les rendre utilisables pour la relation entre classeurs mensuels et le récap

mais ces trous ont compliqué un peu la procédure

ci joint proposition pour reporter les mois existants mais j'ai arrêté là sans m'occuper pour l'instant d'un nouveau mois car je crains que ton filleul veuille insérer une nouvelle colonne à chaque fois et ce à partir de février 2008 ?. Dans ce cas ne serait il pas judicieux de placer la colonnes enc et total à la gauche des mois?

donc j'attend ces précisions (serai absent une partie de l'ap-midi (comme ce matin, j'ai des soins...))

Bonjour Michel

Bon, je te joins le nouveau fichier. On démarre à Janvier 2009.

Ta macro tourne nickel. J'ai modifié les plages dans :

 If Application.CountIf(Range("C1:IV1"), mois_an) > 0 Then

        'mémorise la position de la colonne

        col = Application.Match(mois_an, Range("C1:IV1")) + 2

pour être sûre de prendre toutes les colonnes, mais ce n'est peut-être pas conseillé ; il faut peut-être chercher la dernière colonne remplie. En tout cas, ça fonctionne.

C'est énorme ce que tu as fait.

J'ai ajouté la macro de claude pour insérer une colonne.

Serait-il possible d'y intégrer les formules pour récupérer le prochain mois ? J'ai essayé avec l'enregistreur de macro de glisser les formules vers la droite, mais je ne sais pas modifier le code afin que cela se fasse, après l'insertion de la colonne, depuis "TOTAL" - 4 jusqu'à "TOTAL" - 3

Donc, pour résumer :

1 - Je copie la macro de claude dans les 40 fichiers et je la fais tourner sur chacun (Calcule les montant de la colonne C) -

2 - Je n'ai plus qu'à ouvrir SUIVI et lancer "Actualiser" (macro reporter)

A te relire

Amicalement

Nad

Bon j'ai ajouté dans la sub "reporter" les lignes pour insérer une colonne avé les dates du mois+1

mais je pense qu'il faudrait que la procédure reporte un mois, ajoute une colonne, passe au mois suivant etc. avec 2 bornes:

1/ si C1 est vide on parcourt l'ensemble

2/ si le dernier reporté est par ex 0210, on ne lance la procédure que pour le ou les mois supérieurs à 0210

Tu dis...si OK, j'attaquerais tout ça demain matin à la fraiche

en attendant, version 3 avec insertion d'une nouvelle colonne au mois+1

-- Jeu Avr 08, 2010 5:48 pm --

le jour où j'oublierai rien !!!

On peut aussi fixer une borne 3 avec un départ mini. par ex: 0109, on ne prendrait que mois>=0109

michel_m a écrit :

mais je pense qu'il faudrait que la procédure reporte un mois, ajoute une colonne, passe au mois suivant etc. avec 2 bornes:

1/ si C1 est vide on parcourt l'ensemble

Pourquoi C1 serait vide puisque on ajoute les mois vers la droite ? Je ne comprends pas.

michel_m a écrit :

2/ si le dernier reporté est par ex 0210, on ne lance la procédure que pour le ou les mois supérieurs à 0210

Il peut y avoir eu des changement dans les autres mois. Donc je ne pense pas que ce soit bon, mais voir le point 3.

michel_m a écrit :

On peut aussi fixer une borne 3 avec un départ mini. par ex: 0109, on ne prendrait que mois>=0109

Ça oui, car il est sûr qu'en mars 2010 on ne reviendra pas par exemple sur janvier 2009. Donc un choix de début de mise à jour me semble très approprié.

Dans ton fichier, j'ai cliqué une 1ère fois sur ACTUALISER : j'ai bien les montants de janvier et février et cela m'a bien créer Mars 2010. Mais j'ai cliqué à nouveau et cela m'a créé un second Mars 2010. Tu peux ajouter dans le code que si le mois existe déjà on ne l'ajoute pas de nouveau ?

Merci encore du temps que tu passes sur ce fichier.

Nad

Bonsoir Nad, Michel,

Tout çà me semble allez bon train !

Je me retire, mais toujours là si besoin

Amicalement

Claude

Merci Claude

Effectivement, ça sent la fin.

Tu m'as été d'une aide très précieuse, comme toujours.

Un ÉNORME merci.

Nad