Automatiser un ensemble de manip Excel
Bonjour,
Je vais essayer d'être le plus clair possible, car il s'agit probablement de la manip la plus compliquée que j'ai partagé sur le site (pour moi en tout cas!). Je tiens à préciser que je n'ai aucun skills en VBA, que je m'inspire un peu de ce que je trouve sur les forums (en essayant de comprendre toujours!) pour construire mes codes.
Je suis actuellement sur un tableau que j'ai essayé d'automatiser au mieux, il y a des macros de tri par date, de mise à jour des filtres, de blocage de ligne, de verrouillage de ligne dès une certaine case complétée etc.
Aujourd'hui je voudrais passer à la manip ultime qui me faciliterais bien la vie !
Mon tableau permet de récolter des données, à ce jour j'ai quasi paramétrer l'ensemble des colonnes (1 colonne par donnée) nécessaires à mon activité, or si un jour je dois en rajouter une, la manip est fastidieuse et j'aimerais l'automatiser (par un clic sur un bouton "insérer colonne").
Voici la manip détaillée
1- Dans la feuille 1, je clique sur la colonne nommée "A" (qui n'est pas la première colonne du fichier)
2 - J'insère la colonne X dans la feuille 1, juste avant "A" donc (les colonnes devront toujours être insérées devant "A")
3 - Je sélectionne les feuilles 2 (Graph F) et 3 (Calcul F), je les duplique avant la feuille de "A"
4 - Je renomme les feuilles Graph F2 / Calcul F2 en Graph X / Calcul X
5 - Je déverrouille le graph X et la feuille Calcul X. Dans le graph X je change une formule faisant référence à F par X, il y a également un bouton macro qui devra renvoyé sur la feuille 1 / Colonne X
6 - Dans le Calcul X je fais un Ctrl H, je remplace tous les F par des X, je protège la feuille Calcul X, et je la masque
7 - Dans la feuille 1, j'insère un bouton macro "Graph X" dans la colonne X / ligne 17 --> ce bouton devra être relié à une macro qui renvoie sur le Graph X. Je protège le Graph X
Evidemment je ne demande pas à coder tout ça, j'ai commencé à faire un enregistrement de la macro. J'ai compris un peu près la démarche, mais ce qui me bloque c'est la fonction qui permet d'insérer la colonne toujours avant la colonne A, et de changer tous les F (valeur constante = ok) en X, Y, Z etc (valeur changeante = bloquant pour moi).
Voilà voilà, après ce pavé j'espère avoir un peu d'aide pour m'orienter vers un début de soluce qui me permettrai d'avoir une bonne base pour coder, je partagerai mes potentiels futurs blocages ici. Je suis conscient que ça dépasse mon niveau actuel, mais je cherche à m'améliorer :).
Merci et bonne journée !
Bonjour,
Eventuellement, vous pouvez faire le tout à l'enregistreur de macros et poster ensuite le code ici pour qu'on essaie de le réarranger.
Cdlt,
Hello, merci pour votre retour rapide, avant tout j'aimerais bien comprendre les étapes svp !
Voici le code brut (dense...) --> n'hésitez pas pour toute question
Sub Insérer_Colonne()
Columns("CU:CU").Select
Range("CU2").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("CT3:CT13").Select
Selection.AutoFill Destination:=Range("CT3:CU13"), Type:=xlFillDefault
Range("CT3:CU13").Select
Range("CT2").Select
ActiveWindow.SmallScroll Down:=6
Range("CT17").Select
Selection.AutoFill Destination:=Range("CT17:CU17"), Type:=xlFillDefault
Range("CT17:CU17").Select
Range("CT18:CT21").Select
Selection.AutoFill Destination:=Range("CT18:CU21"), Type:=xlFillDefault
Range("CT18:CU21").Select
Sheets("Graph Colonne F").Select
ActiveSheet.Unprotect
Sheets("Graph Colonne F").Select
ActiveWorkbook.Unprotect
Sheets("Graph Colonne F").Select
Sheets("Calcul Colonne F").Visible = True
ActiveSheet.Unprotect
Sheets(Array("Graph Colonne F", "Calcul Colonne F")).Select
Sheets("Graph Colonne F").Activate
Sheets(Array("Graph Colonne F", "Calcul Colonne F")).Copy Before:=Sheets(188)
Sheets("Graph Colonne F (2)").Select
Sheets("Graph Colonne F (2)").Name = "Graph Colonne CU"
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
Selection.Caption = "='Tableau de saisie'!R223C99"
Sheets("Calcul Colonne F (2)").Select
Sheets("Calcul Colonne F (2)").Name = "Calcul Colonne CU"
Range("D6").Select
Cells.Replace What:="'!F", Replacement:="'!CU", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Sheets("Calcul Colonne CU").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Graph Colonne CU").Select
ActiveChart.Shapes.Range(Array("Rectangle 3")).Select
Selection.OnAction = "GraphColonneCU_Rectangle113_Cliquer"
ActiveChart.ChartArea.Select
ActiveChart.Shapes.Range(Array("Rectangle 3")).Select
Selection.OnAction = "GraphColonneCU_Rectangle113_Cliquer"
ActiveWindow.ScrollWorkbookTabs Sheets:=-90
Sheets("Tableau de saisie").Select
ActiveSheet.Shapes.Range(Array("Rectangle 116")).Select
Selection.OnAction = "Rectangle_118Cliquer"
Range("CW18:CW21").Select
End Sub
Merci !
Cordialement !
Bonjour Arkadia,
J'ai pas mal de remarques et questions à poser. Je vais essayer de ne rien oublier :
- Plus de 188 feuilles, ce n'est pas une utilisation normale d'un classeur Excel donc je pense que quelque chose ne va pas avec ce fichier.
- plus de 99 colonnes, à moins de gérer des super bases ou d'avoir une colonne par jour, c'est quelque chose d'assez rare également.
- Pourquoi utilisez-vous la méthode autofill de la colonne CT à la colonne CU ? Pourquoi seulement les lignes 3:13, 17:21 ? Est-il possible d'envisager de copier directement la colonne CT pour l'insérer en CU afin d'avoir les formules propagées ?
- La feuille de départ est-elle bien la feuille "tableau de saisie" ?
- Et quel est le nom de la feuille 188 ?
Les noms en statique posent problème dans le code ! Il faut trouver un moyen de procéder sans ces noms ! Est-ce que les feuilles qui sont copiées ne contiennent qu'un seul graphique ?
Pourquoi affecter une macro à une des formes de la feuille "tableau de saisie" à la fin ? Cette forme ne contient-elle pas déjà la macro en question ?
Cdlt,
Bonsoir, 3GB,
dans vos formule de la feuille source qui va être copiée, vous est-il possible de rendre variable la donnée "F" avec l'instruction INDIRECT ?
En effet l'idée serait de ne changer la valeur que d'une seule cellule suite à cette copie de feuille :
Si vous mettez la valeur "F" en A1 (par exemple si cette cellule est libre) et que vos formule y fasse référence grâce à INDIRECT par exemple =INDIRECT(A1&"6") ici on cible la cellule "F6" car indirect A1 = "F" concaténé avec 6.
C'est une idée, du coup suite à la copiée de la feuille source il suffirait de mettre "X" en A1...
@ bientôt
LouReeD
Hello,
Alors pour commencer merci d'avoir regardé le code ^^" et pris le temps de me répondre.
LouReeD, je vais répondre simplement par je ne sais pas ce qu'est l'instruction INDIRECT (jamais utilisée).
3GB, alors oui ce classeur Excel est balèze, il s'agit clairement d'une base de données ! La feuille "Tableau de saisie") est un tableau qui regroupe l'ensemble des données à saisir à raison d'une variable par colonne (les colonnes sont choisies en fonction de ce que l'on doit récupérer d'un élément A à un élément B etc), chaque colonne de données est reliée à un graphe (chaque graphe = un onglet).Donc si j'ai 50 variables = 50 colonnes de saisie en feuille "Tableau de saisie", j'ai 50 graphes (50 onglets supp) chaque graphe a sa propre feuille de calcul qui est masquée, donc si j'ai 50 variables, j'ai 50 onglet graphes + 50 onglets calcul. Ce fichier tel quel (avec autant d'onglets) est un template qui regroupe toutes les possibilités à ce jour, or le ficher est lourd (bien que j'ai réussi à optimiser les temps d'ouverture/fermeture du fichier).
Chose importante, vu le nombre d'onglets et pour facilement retrouver le graphe associé à la colonne, un bouton macro de redirection est inséré au niveau de chaque colonne ligne 17 --> le bouton sur la table de données redirige vers le graphe correspondant (donc un autre onglet).
Le but de la manip d'automatisation est de rajouter en 1 clic tous ce que j'ai décris et éviter ainsi les erreurs de manip + permet de partir d'un fichier plus léger avec une base de 10 graphes/10 feuilles de calcul par exemple.
Pour ta seconde question, non le template tel quel est statique (je ne pense pas qu'il y aura grosse modif) --> mais cette question rejoint le paragraphe ci-dessus, simplifier le template et n'ajouter que ce qui est nécessaire au lieu de partir de la totalité des données potentielles à récupérer.
La feuille de collecte (donc de départ) est bien "Tableau de saisie".
Désolé je ne sais pas ce qu'est la formule autofill ^^".
Je vais essayer de mieux détailler mes manips:
1) Je déprotège la feuille "Tableau de saisie"
2) Je vais dans la colonne CU, je la select, je fais insérer
3) Je tire les formules stats des lignes 3 à 13 de CT à CU (nouvelle colonne insérée)
4) Je tire la formule des lignes 17 à 22, idem. Je tire par la même occasion le bouton en ligne 17 qui doit rediriger vers le graphe de la nouvelle colonne --> à ce stade ma colonne est prête (le lien sera créé plus tard car le graphe n'existe pas encore).
Je dois maintenant créer le graph.
5) Je déprotège le classeur
6) Je vais sur l'onglet visible Graph Colonne F, j'affiche l'onglet Calcul colonne F
7) Je fais un copier déplacer des deux feuilles en même temps (Ctrl je clique sur les deux feuilles --> copier déplacer --> avant l'onglet historique des modifications)
7) Je change le nom des onglets par Graph Colonne (" ref nouvelle colonne") et Calcul Colonne (" ref nouvelle colonne").
7) Je déprotège la feuille Graph Colonne (" ref nouvelle colonne").
8) Dans Graph Colonne (" ref nouvelle colonne"), j'ai un titre qui dépend d'une formule en F, je change le F avec la référence de la nouvelle colonne, j'ai également un bouton retour sur la table de donnée, je créé la macro de redirection et j'affecte cette macro à mon bouton retour -> la référence de retour est "nouvelle colonne" Ligne 23. Je reprotège la feuille Graph Colonne (" ref nouvelle colonne").
9) Je déprotège la feuille Calcul Colonne (" ref nouvelle colonne").
10) Dans Calcul Colonne (" ref nouvelle colonne"), je fais un Ctrl+H " !'F " par " !' "ref nouvelle colonne ") --> permet de récupérer les données de la nouvelle colonne. Je protège la feuille Calcul Colonne (" ref nouvelle colonne") et je la masque.
11) Je retourne sur la table de données, je créé ma macro de redirection table de données --> Graph Colonne (" ref nouvelle colonne"), j'affecte au bouton ligne 17 la macro.
12) Je masque l'onglet Calcul Colonne F
13) Je reprotège le classeur
Manip terminée !
Le nom de la feuille 188 est "Historique des Modifications" qui est la Feuil51. Chaque nouveau couple Graph/Calcul doit être inséré avant la Feuil51.
Pour les noms statiques, I know, là j'ai juste enregistré en brut (il y a une option dans l'enregistreur de macros Excel pour changer les noms en noms statiques ?). La copie c'est 1 feuille avec un graphe seulement (pas de données écrites etc) + 1 feuille de calcul avec les données permettant de créer le graphe --> l'objectif ici est de séparer les deux pour n'avoir que le visuel (le graphe = visual management).
Vu que les boutons sont copiés/collés soit par un tirer/glisser soit un copier/coller du graphe F, les redirections initiales sont conservées.
Pour rappel : le bouton dans le nouveau graphe doit rediriger vers la colonne nouvellement insérée ligne 23 et le bouton dans le "tableau de saisie", ligne 17 dans la colonne nouvellement insérée, doit rediriger vers le nouveau graphe.
En gros chaque bouton de chaque colonne de données du tableau de saisie redirige vers le graphe de sa colonne, et chaque bouton de chaque graph redirige vers la colonne de sa propre variable en ligne 23.
Pas évident tout ça ^^, pour des raisons de confidentialité vous aurez compris que je ne peux pas partager de fichier :/.
Cordialement !