XL 2016 VBA: Sélectionner puis désélectionner toutes les feuilles

[Aloha]

Bonjour,

Je voulais enregistrer l'action de sélectionner toutes les feuilles, mais l'enregistreur n'a rien enregistré

Quel petit code me permettrait de sélectionner toutes les feuilles d'un fichier?
Et lequel pour les désélectionner à nouveau?
Cela ne peut pas être une seule Sub, puisqu'entre l'action de sélectionner et celle de désélectionner il y a d'autres Sub.

Bonne soirée
Aloha

 

[Staple1600]

Bonjour le fil , le forum

@Aloha
Quelques indices de plus
La suite des opérations:
1. vérifier si le fichier Base est ouvert, sinon l'ouvrir
en stock dans le magasin XLD (autrement dit les archives du forum)
2. ouvrir le fichier Fichier destination
en stock dans le magasin XLD (autrement dit les archives du forum)
3. inscrire SERVICE 3 en A1 de la feuille Personnel du fichier Base
réalisable avec l’enregistreur de macros
4. sélectionner toutes les feuilles du fichier de destination
en stock dans cette discussion
5. mettre les formules dans B6:E8 de toutes les feuilles
en stock dans le magasin XLD (autrement dit les archives du forum)
6. remplacer les formules (qui viennent de récolter les informations dans l'autre fichier depuis que SERVICE 3 a été mis en A1 de Personnel; dans mon exemple il n'y a que des N/A; il faut les considérer comme des valeurs) par leurs valeurs
7. désélectionner les feuilles
en stock dans cette discussion
8. fermer le fichier en le sauvegardant
en stock dans le magasin XLD (autrement dit les archives du forum)

NB : Pas sur qu'il soit nécessaire de sélectionner les feuilles pour faire ce que tu veux faire
Si tu as utilisé l’enregistreur de macros, dans ce cas, il eut été plus pratique de laisser le code VBA obtenu dans tes classeurs, non?
Car nous n'aurions pas à "reconstruire" ce code sur nos PC si il était déjà dans tes fichiers exemple.

 

[Staple1600]

Re

@Aloha
Tu joins un fichier Base.xlsx
et ta formule est

=RECHERCHEH($E$2;INDIRECT("[Base.xls]"&"Personnel"&"!$D$3:$AI$489");13;0)

Et je ne comprends les références de cellules D$3:$AI$489
car dans ton exemple, cela correspond à des cellules vides

 

[Aloha]

Bonjour,

Oui, tu as raison, mais il faut ignorer cela: dans le fichier réel elles fonctionnent. Cela aurait été trop onéreux d'imiter exactement la situation réelle très compliquée.
Ton code fonctionne à la merveille dans un exemple simple, mais si je le transpose dans mon fichier réel il bloque à

s.Select Tout

Qui plus est, j'avais oublié qu'il y a un fichier caché qu'il ne faut pas sélectionner.
Et je ne sais pas comment transformer ton code pour en tenir compte.

En utilisant ton code et l'enregistrement, j'ai fabriqué le chef-d'oeuvre que voici:

Sub RemplacerFormules()

    ScreenUpdating = False

    Workbooks.Open Filename:= _
        "C:\Users\...\Desktop\....\....\XY\XY 2017.xlsx"
    Windows("Base.xls").Activate
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "XY"
    Windows("XY 2017.xlsx").Activate

    WS_Selection

    Range("B6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),13,0)"
    Range("E6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),14,0)"
    Range("H6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),15,0)"
    Range("B7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),22,0)"
    Range("E7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),23,0)"
    Range("H7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),24,0)"
    Range("B8").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),31,0)"
    Range("E8").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),32,0)"
    Range("H8").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=HLOOKUP(R2C5,INDIRECT(""[Base.xls]""&""Personnel""&""!$D$3:$AI$489""),33,0)"
   
    Range("B6:H8").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
    WS_Selection True

    ActiveWindow.Close True

A part qu'il bloque et qu'il ne tient pas compte du fichier à ne pas sélectionner (fichier modèle qui sert à fabriquer de nouvelles fiches et qui doit absolument garder les formules.

Je suppose que c'est l'inscription des formules qui le rend si lent.
En outre, je pensais que si je sélectionne toutes les fiches identiques (et dans ce seul but) il n'avait pas à inscrire les formules dans les cellules fiche par fiche mais toutes à la fois, ce qui ne semble pas être le cas. Idem pour le remplacement par les valeurs.
N'y a-t-il pas une possibilité de traiter toutes les fiches à la fois?

D'ailleurs, pour bien faire, il faudrait intégrer toutes les opérations dans mon code qui copie mensuellement des données dans le fichier; seulement, je n'ai pas réussi à cela et voilà pourquoi je réouvre chaque fichier pour remplacer les formules dans le but de garder les valeurs (fonction INDIREC).

A+
Aloha

P.S.: je n'avais pas vu le me message #16 lorsque j'ai rédigé celui-ci.

 

[Staple1600]

Re

@Aloha

. Cela aurait été trop onéreux d'imiter exactement la situation réelle très compliquée.

Onéreux dans quel sens? Ici on est tous bénévole
Plus on disposera d'un fichier exemple proche du fichier réel (avec des exemples de données et pas des colonnes vides), plus on sera en mesure de t'aider.
Tu as juste à veiller à fournir un fichier sans données confidentielles.

PS: Tu as bien lu (surtout) entre tes lignes le message#16 ?

 

[Aloha]

Re,

Ce que j'ai dit à propos du blocage de ton code n'est plus exact: il ne bloque plus.
Cependant, il me semble que je ne l'ai pas bien intégré: les formules ne sont remplacées que dans une seule feuille.

La situation réelle est bien compliquée pour la reproduire, mais si vous êtes toujours prêts à m'aider je peux essayer de la préciser.

Si tu as utilisé l’enregistreur de macros, dans ce cas, il eut été plus pratique de laisser le code VBA obtenu dans tes classeurs, non?
Car nous n'aurions pas à "reconstruire" ce code sur nos PC si il était déjà dans tes fichiers exemple.

J'ai enregistré dans le fichier réel. Je vais refaire l'opération dans l'exemple.

J'ai lu tes remarques du message #16. C'est bien beau de trouver les procédures ici, mais il faut encore savoir comment les trouver, et, surtout, comment les intégrer!

A+
Aloha

 

[Staple1600]

Re

@Aloha
Pour les trouver, rien de plus simple, le forum dispose d'un moteur de recherche interne.
Pour ce qui est de les intégrer, rien de plus simple, il suffit d'essayer
Et comme dirait Yoda: le meilleur des maîtres, c'est l'échec
Donc ce qui vaut pour les padawans Jedi vaut pour les pavbawans

Encore une fois, plus les membres du forum disposent d'un fichier exemple fourni et détaillé mais anonymisé fourni par le demandeur, mais il est facile de faire des tests avant de proposer des solutions.
Ca ne vaut pas que pour ton fil, mais pour tous fils et ce depuis bien avant qu'Excel sache enregistrer un classeur en PDF

 

[Aloha]

Re,

Pour les trouver, rien de plus simple, le forum dispose d'un moteur de recherche interne.

Je voulais dire qu'il faut encore savoir quoi introduire dans la recherche.

J'ai modifié les deux fichiers pour qu'ils correspondent, pour la tâche actuelle, aux fichiers réels.

By the way, dans le fichier "Base", feuille "Personnel" il y a, en B1, und formule INDIRECT qui ne veut pas fonctionner -je ne comprends pas pourquoi-, voilà pourquoi je l'ai remplacée, pour cet exemple, par des formules de référence directe. C'est le cas pour les cellules D3:G3, D13:G15, D22:G24, D31:G33.

J'avais détaillé la tâche dans un post précédent. Voici un résumé:
Ouvrir "Base" si pas ouvert, ouvrir "SERVICE 3 2017", sélectionner toutes les feuilles sauf "Modèle" (si indiqué et utile), inscrire, dans toutes les feuilles sauf "Modèle", les formules en B6 à H8, mettre "SERVICE 3" dans "Base : Personnel A1", revenir au fichier "SERVICE 3 2017", copier les formules de B6 à H8 dans chaque feuille sauf "Modèle", les remplacer par leurs valeurs, désélectionner les feuilles (si elles ont été sélectionnées), et enregistrer le fichier.

Comme je l'ai déjà dit avant, je dois, faute de connaissances suffisantes en VBA, copier le code 9 fois pour l'adapter aux 9 autres services.
A+
Aloha

 

[Staple1600]

Re

Ta formule pointait vers C3 (qui est vide)
En modifiant comme ceci, pas d'erreur
=INDIRECT("'"&$A$1&"'!"&"D3")

 

[Aloha]

Je viens de me rendre compte que j'ai oublié d'enregistrer les actions.
Je le fais de suite...

Qui plus est. le formatage de certaines cellules, qui doivent être liées, n'est pas correct. Je le corrige.

 

[Aloha]

Re,

Voici les fichiers corrigés.
J'ai d'ailleurs découvert une méthode alternative, bien plus rapide, pour mettre les formules, avec la conditio sine qua non que "Modéle" ne soit pas sélectionné avec les autres feuilles. Dans ce cas je peux copier les formules de "Modèle" et les coller dans les autres fiches.

Pour l'instant, avec l'enregistreur, la sélection des feuilles est rigide.

A+
Aloha

 

[Aloha]

Je viens de remarquer que l'extension de "Base" n'est pas correcte dans le code: elle est "xlsm" et non pas "xlsx". Sorry

 

[Aloha]

Re,

Je pense avoir trouvé une solution:

Sub Remplacer_formules_par_valeurs()

    Dim wSht As Worksheet
    Sheets("Personnel").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "SERVICE 3"
    Workbooks.Open Filename:= _
        "C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 3 2017.xlsx"
    Sheets("Modèle").Visible = True
    Sheets("Modèle").Select
    Range("B6:H8").Select
    Selection.Copy
For Each wSht In Worksheets
    If wSht.Name <> "Modèle" Then
    wSht.Select
    Range("B6:H8").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End If
Next wSht
For Each wSht In Worksheets
    If wSht.Name <> "Modèle" Then
    wSht.Select
    Range("B6:H8").Select
    Selection.Copy
    Range("B6:H8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
Next wSht
    Sheets("Modèle").Visible = False
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

Qu'en pensez-vous? Elle a d'ailleurs l'avantage d'être ultra-rapide.

Mon prochain pas serait de faire tourner le code pour tous les services.
Je suppose qu'on ne peut pas téléverser des dossiers, ce qui serait important pour comprendre la structure l'hiérarchie des dossiers.

Voici le "path" (en français?) des fichiers:

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 1\SERVICE 1 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 2\SERVICE 2 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 3\SERVICE 3 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 4\SERVICE 4 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 5\SERVICE 5 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 6\SERVICE 6 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 7\SERVICE 7 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 8\SERVICE 8 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 9\SERVICE 9 2017.xlsx"

"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 10\SERVICE 10 2017.xlsx"

J'aurais donc besoin d'un bout de code qui ouvrirait un fichier après l'autre, y mettrait les formules, y ferait la copie, l'enregistrerait, pour ensuite ouvrir le prochain.
Mais ça, ça me dépasse totalement!
Bonne nuit
Aloha