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"
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 :
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 :
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.
