VBA Coller des Valeurs et Collage Spécial

Ce tutoriel vous montrera comment utiliser le collage spécial (« PasteSpecial ») en VBA pour coller uniquement certaines propriétés d’une pour plusieurs cellules (ex. valeurs, formats).

 

Dans Excel, lorsque vous copiez et collez une cellule, vous copiez et collez toutes les propriétés de la cellule : valeurs, formats, formules, formatage des nombres, bordures, etc :

Alternativement, vous pouvez effectuer un « Collage spécial » pour ne coller que certaines propriétés de la cellule. Dans Excel, le menu « Collage spécial » est accessible par le raccourci CTRL + ALT + V (après avoir copié une cellule) :

Vous pouvez y voir toutes les combinaisons de propriétés de cellules que vous pouvez coller.

Si vous enregistrez une macro tout en utilisant le menu Collage spécial, vous pouvez simplement utiliser le code généré. C’est souvent la façon la plus simple d’utiliser VBA pour le collage spécial.

Coller des valeurs

La fonction Paste Values (Coller des valeurs) ne colle que la « valeur » de la cellule. Si la cellule contient une formule, Paste Values collera le résultat de la formule.

Ce code permet de copier et de coller les valeurs d’une seule cellule dans la même feuille de calcul :

Range("A1").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues

Copier et Coller des Valeurs dans une Feuille Différente

Cet exemple permet de copier et de coller les valeurs de cellules individuelles sur différentes feuilles de calcul.

Sheets("Feuille1").Range("A1").Copy
Sheets("Feuille2").Range("B1").PasteSpecial Paste:=xlPasteValues

Ces exemples permettent de copier et coller les valeurs d’une plage de cellules :

Copier et Coller les Valeurs des Plages

Range("A1:B3").Copy
Range("C1").PasteSpecial Paste:=xlPasteValues

Copier et Coller les Valeurs des Colonnes

Columns("A").Copy
Columns("B").PasteSpecial Paste:=xlPasteValues

Copier et Coller les Valeurs des Lignes

Rows(1).Copy
Rows(2).PasteSpecial Paste:=xlPasteValues

Coller les Valeurs avec Mise en Forme des Nombres

Le collage des valeurs colle uniquement la valeur de la cellule. Aucun formatage n’est collé, y compris le formatage des nombres.

Souvent, lorsque vous collez des valeurs, vous souhaitez probablement inclure le formatage des nombres afin que vos valeurs restent formatées. Regardons un exemple.

Ici, nous allons coller une valeur dans une cellule contenant un pourcentage :

Sheets("Feuille1").Columns("D").Copy
Sheets("Feuille2").Columns("B").PasteSpecial Paste:=xlPasteValues

Remarquez que le formatage du pourcentage est perdu et qu’une valeur décimale peu attrayante est affichée.

Utilisons plutôt la fonction « Coller les valeurs avec mise en forme des nombres » (Paste Values and Number formats) :

Sheets("Feuille1").Columns("D").Copy
Sheets("Feuille2").Columns("B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Vous pouvez maintenant remarquer que le formatage des nombres est également collé, en conservant le format de pourcentage.

.Value au lieu de .Paste

Au lieu de coller des valeurs, vous pouvez utiliser la propriété Value de l’objet Range :

Ainsi, la valeur de la cellule A2 sera égale à la valeur de la cellule B2.

Range("A2").Value = Range("B2").Value

Vous pouvez également définir une plage de cellules comme étant égale à la valeur d’une seule cellule :

Range("A2:C5").Value = Range("A1").Value

ou une plage de cellules égale à une autre plage de cellules de taille identique :

Range("B2:D4").Value = Range("A1:C3").Value

L’utilisation de la propriété Value est moins contraignante. En outre, si vous voulez devenir compétent avec Excel VBA, vous devez vous familiariser avec la propriété Value des cellules.

Propriétés Value et Value2 d’une Cellule

Techniquement, il est préférable d’utiliser la propriété Value2 d’une cellule. Value2 est légèrement plus rapide (cela n’a d’importance que pour les calculs extrêmement exigents) et la propriété Value peut vous donner un résultat tronqué si la cellule est formatée en devise ou en date.  Cependant, plus de 99% du code que j’ai vu utilise .Value et non .Value2.  Personnellement, je n’utilise pas .Value2, mais il est important de savoir qu’elle existe.

Range(« A2 »).Value2 = Range(« B2 »).Value2

Créateur de Copier-Coller

Nous avons créé un générateur de code copier-coller (Copy & PasteBuilder) qui permet de générer facilement du code VBA pour copier (ou couper) et coller des cellules. Le générateur fait partie de notre module complémentaire VBA : AutoMacro.

AutoMacro contient également plusieurs autres Générateurs de Code, une vaste Bibliothèque de Codes et de puissants Outils de Programmation.

Collage Spécial – Formats et Formules

Outre coller des valeurs (Paste Values), les options de collage spécial les plus courantes sont Coller les Formats et Coller les Formules (Paste Formats et Paste Formulas).

Coller les Formats

L’option Coller les formats (Paste Formats) vous permet de coller tout le formatage des cellules.

Range("A1:A10").Copy
Range("B1:B10").PasteSpecial Paste:=xlPasteFormats

Coller les Formules

La fonction Coller les formules (Paste Formulas) permet de coller uniquement les formules des cellules. Cette fonction est extrêmement utile si vous souhaitez copier les formules des cellules, mais ne souhaitez pas copier les couleurs d’arrière-plan des cellules (ou tout autre formatage de la cellule).

Range("A1:A10").Copy
Range("B1:B10").PasteSpecial Paste:=xlPasteFormulas

Coller les Formules et Formats Numériques

Comme pour le collage de valeurs et de formats numériques ci-dessus, vous pouvez également copier et coller des formats numériques avec des formules.

Ici, nous allons copier une formule de cellule avec le formatage numérique de type comptabilité et coller uniquement les formules.

Sheets("Feuille1").Range("D3").Copy
Sheets("Feuille2").Range("D3").PasteSpecial xlPasteFormulas

Remarquez que la mise en forme des nombres est perdue et qu’une valeur non arrondie est affichée à la place.

Utilisons plutôt la fonction Coller les Formules et Format Numériques (Paste Formulas and Number Formats) :

Sheets("Feuille1").Range("D3").Copy
Sheets("Feuille2").Range("D3").PasteSpecial xlPasteFormulasAndNumberFormats

Vous pouvez maintenant voir que le formatage des nombres est également collé, en conservant le format de type comptabilité.

Collage spécial – Transposition et Blancs Non Compris

Collage spécial – Transposition

Le collage spécial transposition (Paste Special Transpose) vous permet de copier et de coller des cellules en changeant leur orientation de haut en bas vers une orientation de gauche à droite (ou vice-versa) :

 

Sheets("Feuille1").Range("A1:A5").Copy
Sheets("Feuille1").Range("B1").PasteSpecial Transpose:=True

Collage Spécial – Blancs Non Compris

Blancs non compris (Skip Blanks) est une option de collage spécial qui ne semble pas être utilisée aussi souvent qu’elle le devrait.  Elle vous permet de copier uniquement les cellules non vides lors du copier-coller. Les cellules vides ne sont donc pas copiées.

Dans l’exemple ci-dessous. Nous allons copier la colonne A, faire un collage normal dans la colonne B et ignorer les cellules vides pour les coller dans la colonne C. Vous pouvez voir que les cellules vides n’ont pas été collées dans la colonne C dans l’image ci-dessous.

Sheets("Feuille1").Range("A1:A5").Copy
Sheets("Feuille1").Range("B1").PasteSpecial SkipBlanks:=False
Sheets("Feuille1").Range("C1").PasteSpecial SkipBlanks:=True

Autres Options de Collage Spécial

Collage Spécial – Commentaires

Sheets("Feuille1").Range("A1").Copy
Sheets("Feuille1").Range("E1").PasteSpecial xlPasteComments

Collage Spécial – Validation

Sheets("Feuille1").Range("A1:A4").Copy
Sheets("Feuille1").Range("B1:B4").PasteSpecial xlPasteValidation

Collage Spécial – Tout, avec le Thème Source

Workbooks(1).Sheets("Feuille1").Range("A1:A2").Copy 
Workbooks(2).Sheets("Feuille1").Range("A1").PasteSpecial 
Workbooks(2).Sheets("Feuille1").Range("B1").PasteSpecial xlPasteAllUsingSourceTheme

Collage Spécial – Tout Sauf les Bordures

Range("B2:C3").Copy
Range("E2").PasteSpecial
Range("H2").PasteSpecial xlPasteAllExceptBorders

Collage Spécial – Largeur des colonnes

Un de mes favoris. Le collage spécial largeur des colonnes (PasteSpecial Column Widths) copiera et collera la largeur des colonnes.

Range("A1:A2").Copy
Range("C1").PasteSpecial
Range("E1").PasteSpecial xlPasteColumnWidths

Collage Spécial – Formattages Conditionnels Combinés

Range("A1:A4").Copy
Range("C1").PasteSpecial
Range("E1").PasteSpecial xlPasteAllMergingConditionalFormats