dimanche 18 mars 2012

Copier-coller en valeur... un classeur entier (et vite).

Poussons plus loin le thème du billet précédent.

On a vu dimanche dernier comment copier-coller en valeur une plage de cellules : le processus basique à la souris, puis la méthode plus rapide au clavier, et enfin une courte macro VBA qui permet de faire la même chose en une fraction de seconde.

Cette manip s’appliquait à une plage de cellules : on peut aussi l’effectuer sur une cellule toute seule, ou sur une colonne entière (ou plusieurs), ou sur une ligne entière (ou plusieurs), ou sur la feuille entière.

À partir de là on entrevoit un autre besoin : copier-coller en valeur l’intégralité de chacune des feuilles d’un classeur.

C’est ce qu’on appelle parfois “écraser les formules” - on le dit pour une plage seule mais surtout pour un classeur entier. C’est ce qu’on fait notamment lorsqu’on doit envoyer le classeur à un destinataire (client ou fournisseur par exemple) qui a seulement besoin de voir les valeurs et pas la mécanique des calculs. Ou à un destinataire dont on estime qu’il ne doit pas voir cette mécanique-là.

Passons rapidement sur le “100% souris” : si on a vraiment envie d’y perdre beaucoup de temps, on peut sélectionner chaque feuille l’une après l’autre en cliquant sur son onglet, puis, dans chacune, sélectionner l’ensemble des cellules, puis effectuer la série de clics évoqués dans le dernier billet. Les feuilles masquées vont encore alourdir l’histoire : on va avoir besoin de les afficher l’une après l’autre, puis de faire la fameuse série de clics, et éventuellement de les re-masquer ensuite.

Au clavier ? Faire la même chose est plus rapide dans la mesure ou la répétition de la même séquence de touches permet d’accélérer son exécution. Pour mémoire, c’est Ctrl PgSuiv pour aller à la feuille suivante (Ctrl PgPréc dans l’autre sens), et Ctrl A pour sélectionner la feuille entière. Et dans la foulée, effectuer la séquence de touches décrite l’autre jour.



Maintenant, parlons VBA.

L’idée de départ est simple : on va parcourir l’ensemble des feuilles du classeur et, pour chacune, sélectionner toutes les cellules puis lancer les trois lignes de code présentées dans le billet précédent. Celles-ci seront en quelque sorte le coeur de la macro.

Quelque chose comme ça :

       Dim Feuille As Worksheet        ' déclaration de variable
       For Each Feuille In ActiveWorkbook.Worksheets

''' coeur de la macro :
''' sélectionner toute la feuille
''' puis copier-coller en valeur

       Next Feuille


Cette façon de parcourir passe par toutes les feuilles de travail, affichées ou non. On va se heurter au problème des feuilles masquées puisqu’il n’est pas possible de faire de copier-coller sur une feuille qui n’est pas visible et activée. Pour tenir compte de cela, on va faire ceci pour chaque feuille :
- regarder si la feuille est affichée ou non et mémoriser cette information-là,
- si elle est masquée, l’afficher,
- sélectionner toutes les cellules de la feuille,
- exécuter le copier-coller valeur sur place,
- si la feuille était masquée au début, la re-masquer.

Le noeud de l’histoire est de mémoriser l’information “affichée ou non”. Pour ça, on va utiliser une variable booléenne (Vrai/Faux). Articulé avec ce qu’on vient de voir, ça donne ceci :

    Dim Feuille As Worksheet, Mémo As Boolean
    For Each Feuille In ActiveWorkbook.Worksheets
       
           ' cas d'une feuille masquée
           If Feuille.Visible = False Then
             Mémo = True
Feuille.Visible = True
           End If

''' coeur de la macro :
''' sélectionner toute la feuille
''' puis copier-coller en valeur

' cas d'une feuille qui était masquée au début           
           If Mémo = True Then
Feuille.Visible = False    ' re-masquage
Mémo = False               ' neutralisation
End If
       
    Next Feuille


L’essentiel est là. Reste à régler quelques détails.

Le premier : après l’exécution du coeur de la macro, je vais redéfinir la cellule sélectionnée sur A1, comme un retour à la maison. C’est une coquetterie qui a du sens par rapport à la perspective d’envoyer le classeur à quelqu’un qui ne le connaît pas forcément mais qui l’attend et pourrait apprécier d’ouvrir un classeur “où on sait où on est”. Pour ça, il suffit de l’instruction suivante à la fin du coeur de la macro :
Feuille.Range("A1").Select

Le deuxième : le mode copie, on n’a pas besoin de le désactiver à chaque tour. On peut faire ça tout à la fin, une seule fois :
    Application.CutCopyMode = False

Le troisième : on peut trouver perturbant le fait que parcourir les feuilles de travail laisse comme feuille active, à la fin, la dernière du classeur. On va donc faire en sorte que, après cela, la feuille active soit à nouveau la même qu’avant. Ça se fait autour de la boucle de parcours, d’une part au début pour noter quelle feuille est active et servira donc de référence :
Dim FeuilleRef As Worksheet
Set FeuilleRef = ActiveSheet
d’autre part à la fin pour la réactiver :
FeuilleRef.Activate

Le quatrième : dans la boucle qui parcourt les feuilles du classeur, on travaille beaucoup sur la variable Feuille. Plutôt que d’y faire appel explicitement à chaque fois, on utilise un bloc With, pour simplifier ces appels par la suite. Comme ceci, à l’intérieur de la boucle :
       With Feuille
    ''' (...)
End With

Le dernier : désactiver (au début) puis réactiver (à la fin) le rafraichissement écran. On y gagne en vitesse d’exécution et ça fait plus propre. On manipule ça en modifiant la propriété Application.ScreenUpdating.


Eh bien voilà, maintenant on met tout ça ensemble :


Sub CopColVal_Classeur()
Dim Feuille As Worksheet, Mémo As Boolean, FeuilleRef As Worksheet

    Application.ScreenUpdating = False
   
    ' mémorisation de la feuille active
    Set FeuilleRef = ActiveSheet

    ' parcours des feuilles de travail
    For Each Feuille In ActiveWorkbook.Worksheets
        With Feuille
       
           ' cas d'une feuille masquée
           If .Visible = False Then
Mémo = True         ' mémorisation
.Visible = True     ' affichage
           End If
           
           ' coeur de la macro
           .Select
           .Cells.Copy
           .Cells.PasteSpecial Paste:=xlPasteValues
           .Range("A1").Select
           
           ' cas d'une feuille qui était masquée au début
           If Mémo = True Then
.Visible = False    ' re-masquage
Mémo = False        ' neutralisation
           End If
       
        End With
    Next Feuille
   
    ' réactivation de la feuille active initiale
    FeuilleRef.Activate
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub



Mettons ce code dans un classeur de macros personnelles, à portée de doigts dans une barre d’outils par exemple. Copier-coller en valeur l’intégralité d’un classeur se réalise alors en un instant !

2 commentaires:

  1. Bonjour et merci, simple et efficace.

    RépondreSupprimer
  2. bonjour, très belle macro et parfaitement expliquée mais ne semble pas fonctionner dans le cas où des cellules sont fusionnées...

    RépondreSupprimer