VBA - Ajouter / Copier une feuille dans un classeur

Septembre 2016




Introduction

Il existe deux manières de procéder, selon le résultat attendu :
  • Méthode Copy => Pour obtenir la copie conforme d'une feuille déjà existante,
  • Méthode Add => Pour ajouter une nouvelle feuille vierge à votre classeur.


En programmation, il faudra veiller à ce que le nom de cette nouvelle feuille soit conforme. C'est-à-dire que le nom de la feuille ne doit pas être identique à celui d'une feuille déjà existante et il ne doit pas contenir certains caractères interdits.
Dans les deux cas, les tests de vérifications seront les mêmes.

Méthode Copy

Cette méthode réalise une copie conforme la feuille que vous copiez. Le format, les données seront toutes identiques dans la nouvelle feuille ainsi créée.

Syntaxe

expression .Copy(Before, After)

Expression représente un objet Sheet ou Worksheet

Les paramètres (Before et After) sont facultatifs. Vous ne pouvez spécifier qu'un seul de ces deux paramètres. Ils sont de type Variant et représentent la feuille après ou avant laquelle vous souhaitez copier votre feuille.
Si le paramètre est omis, la feuille est automatiquement copiée dans un nouveau classeur.

Que se passe-t-il alors ?

Lorsque l'on crée une nouvelle feuille par cette méthode, celle-ci :
  • est créée à l'endroit souhaité (après ou avant une autre feuille du classeur),
  • est nommée à partir du nom de la feuille copiée. Exemple : Feuil1 => Feuil1 (2),
  • est identique à la feuille copiée,
  • devient la feuille active.

Si la feuille est copiée dans un nouveau classeur, celui-ci devient le classeur actif.

Exemples d'utilisation

Copie la feuille "Feuil1" après la feuille "Feuil3"
Worksheets("Feuil1").Copy After:=Worksheets("Feuil3")


Copie la feuille "Feuil4" avant la feuille "Feuil2" :
Sheets("Feuil4").Copy Before:=Sheets("Feuil2")


Copie la feuille "recap" dans un nouveau classeur
Worksheets("recap").Copy


Copie la feuille d'index 1 en dernière position dans le classeur, sans se préoccuper du nom de la dernière feuille :
Sheets(1).Copy After:=Sheets(Sheets.Count)


Copie la feuille d'index 10 en première position dans le classeur, sans se préoccuper du nom de la première feuille :
Sheets(10).Copy Before:=Sheets(1)


Vous souhaitez copier plusieurs feuilles de votre classeur actif dans un nouveau classeur :
Sheets(Array("Feuil1", "Feuil3", "Feuil5")).Copy

Méthode Add

Crée une nouvelle feuille de calcul, de graphique ou de macro.
La nouvelle feuille de calcul devient la feuille active.
S'agissant d'une nouvelle feuille, celle-ci sera vierge de toutes données et de tout format.

Syntaxe

expression .Add(Before, After,Count,Type)

Expression représente un objet Sheet ou Worksheet

Les paramètres (Before, After, Count et Type) sont facultatifs.
Vous ne devez spécifier qu'un seul des paramètres Before et After. Ceux-ci sont de type Variant et représentent la feuille après ou avant laquelle vous souhaitez placer votre nouvelle feuille.
Le paramètre Count (facultatif) est de type Variant. Il représente le nombre de feuilles que vous souhaitez ajouter.
Le paramètre Type représente le type de feuille à ajouter. Il s'agit de :
  • xlWorksheet, ajoute une feuille de calcul,
  • xlChart, ajoute une feuille de graphique,
  • xlExcel4MacroSheet ajoute une feuille de macros Excel4,
  • xlExcel4IntlMacroSheet ajoute une feuille de macros Excel,
  • xlDialogSheet ajoute une feuille boîte de dialogue.


Nota : tous ces paramètres sont des paramètres nommés. Il convient donc de... les nommer. Cette syntaxe renverra une erreur :
ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count), , 1, xlChart

Exemples d'utilisation

Ajoute une feuille après la dernière feuille du classeur :
Sheets.Add After:=Worksheets(Worksheets.Count)


Ajoute trois feuilles en "première position" dans le classeur actif :
 ActiveWorkbook.Sheets.Add Before:=Worksheets(1), Count:=3

Nota : les trois feuilles apparaissant successivement, l'ordre pourra être à intervertir. La feuille active est la dernière créée.

Ajoute une feuille de graphique, dans un classeur nommé « Wbk18 » (ouvert !), après la feuille nommée « Feuil4 » :
WorkBooks("Wbk18").Sheets.Add After:=Worksheets("Feuil4"), Type:=xlChart

Nota : L'emplacement de la feuille créée peut vous réserver des petites surprises...

Les fonctions de vérification

Si la feuille existe déjà dans le classeur

'Test si la feuille existe déjà
Function Feuil_Exist(strWbk As String, strWsh As String) As Boolean

'Gestionnaire d'erreur
On Error Resume Next
    '"Test"
    Feuil_Exist = (Workbooks(strWbk).Sheets(strWsh).Name = strWsh)
End Function

Si le nom souhaité contient un caractère prohibé

'Test si la chaine contient un caractère à éviter
Function Valid_Name(strName As String, strChr As String) As Boolean
Dim i As Byte, Tb_Car() As String, strProhib As String

strProhib = "/\:*?""<>|" ' Liste des caractères à éviter
Tb_Car = Split(StrConv(strProhib, vbUnicode), Chr$(0))
'Boucle sur tous les caractères à éviter
 'Nota : le -1 est dû au Split de la chaine par le séparateur Chr(0)
 'En effet, la chaine se terminant par un Chr(0) il convient d'exclure ce dernier caractère
For i = LBound(Tb_Car) To UBound(Tb_Car) - 1
    'Test si la chaîne contient un caractère prohibé
    If InStr(strName, Tb_Car (i)) > 0 Then
 'Si oui : Return False
        Valid_Name = False
 'ET Retourne le caractère prohibé
        strChr = Tb_Car(i)
        Exit Function
    End If
Next i
'Si OK : Return True
Valid_Name = True
End Function

Code d'appel des fonctions de vérification

Dans les deux méthodes, le code est le même.
En effet, la feuille créée porte un nom déterminé par Excel et devient la feuille active.
Puisque l'on ne peut que difficilement connaître à l'avance le nom de cette nouvelle feuille (Feuil1 (3) par exemple), nous allons utiliser le fait qu'elle soit la feuille active, soit : ActiveSheet.

Sub Principale()
Dim strNewName As String, strCara As String

strNewName = "NewSheet"
If Valid_Name(strNewName, strCara) = False Then
    MsgBox "Le nom : " & strNewName & " est invalide." & vbCrLf & _
            "Un nom de feuille ne peut pas contenir le caractère : " & strCara, vbCritical
    Exit Sub
End If
If Feuil_Exist(ThisWorkbook.Name, strNewName) = True Then
    MsgBox "Le nom : " & strNewName & " est invalide." & vbCrLf & _
            "Ce nom de feuille est déjà utilisé dans ce classeur.", vbCritical
    Exit Sub
End If
ThisWorkbook.Sheets.Add 'Ou : ThisWorkbook.Sheets("Feuil1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = strNewName
End Sub

Compléments utiles Office 2013

La suite Office 2013 vous apporte deux méthodes complémentaires à celles vues précédemment. Il s'agit de la copie d'une plage de cellules d'une feuille vers plusieurs feuilles et de la méthode Add2 pour les NewLayout des collections d'objets Charts.

Copie partielle

Cette méthode n'est disponible qu'à partir des versions d'Office 2013.
Vous ne souhaitez copier qu'une plage de cellules de la feuille « Feuil1 » vers les feuilles « Feuil3 », « Feuil5 » et « recap ». Pour cela, vous pouvez utiliser la méthode FillAcrossSheets :

Feuilles = Array("Feuil3", "Feuil5", "Feuil7")
Sheets(Feuilles).FillAcrossSheets Worksheets("Feuil1").Range("A1:C5")


Les paramètres de cette méthode sont :
  • Range : obligatoire
  • Type : facultatif. Il indique comment copier la plage et peut prendre trois valeurs :
    • xlFillWithAll : Copie le contenu et les formats.
    • xlFillWithContents : Copie le contenu.
    • xlFillWithFormats : Copie les formats.

Méthode Add2

Disponible à partir d'Office 2013. Cette méthode est disponible uniquement pour les collections d'objets Charts et renvoie une erreur « Run Time » lorsqu'elle est utilisée avec des objets Sheets et WorkSheets.
Pas plus d'info sur Microsoft.com si ce n'est la syntaxe :
expression .Add(Before, After,Count,NewLayout)

Expression représente un objet Worksheet

Les paramètres sont identiques à la méthode Add, juste le remplacement de Type par le paramètre NewLayout. Si NewLayout possède la valeur True, le graphique est inséré en utilisant les nouvelles règles de mise en forme dynamiques (le titre et la légende sont dessus uniquement s'il y a plusieurs séries).

Conclusion

Les forums bureautique > Excel et programmation > VB/VBA sont à votre disposition pour toute demande complémentaire ou pour adapter ces méthodes à votre code.
N'hésitez pas.

A voir également :

Ce document intitulé «  VBA - Ajouter / Copier une feuille dans un classeur  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.