L’objet de worksheets est une collection qui contient toutes les feuilles de calcul du classeur.
Il existe plusieurs options pour modifier une seule feuille:
■ ActiveSheet: la feuille de calcul actuellement active
■ Worksheets(Index): Index est le numéro de séquence de la feuille de calcul dans la collection de feuilles de calcul.
■ Worksheets(“Name”): nom de la feuille de calcul sous forme de chaîne (entre guillemets)
1 Ajouter une nouvelle feuille de calcul
La procédure suivante permet d’insérer une nouvelle feuille de calcul.
Sub insererFeuille () Worksheets.Add End Sub
Commentaires
■ Par défaut, Excel vous propose trois feuilles de calcul lors de la création d’un nouveau classeur. Si vous souhaitez en ajouter, utilisez la méthode Add.
■ La méthode Add crée une nouvelle feuille de calcul. La nouvelle feuille de calcul devient la feuille active. Sa syntaxe est expression. Add (Before, After, Count, type) :
– Before : spécifiant la feuille avant laquelle la nouvelle feuille est ajoutée.
– After : spécifiant la feuille après laquelle la nouvelle feuille est ajoutée.
– Count : Nombre de feuilles à ajouter. La valeur par défaut est le nombre de feuilles sélectionnées.
– Type : Spécifie le type de feuille. Il peut s’agir de l’une des constantes XlSheetType: xlWorksheet, xlChart, xlExcel4MacroSheet ou xlExcel4IntlMacroSheet. Si vous insérez une feuille basée sur un modèle existant, spécifiez le chemin d’accès du modèle. La valeur par défaut est xlWorksheet.
■ La feuille de calcul nouvellement insérée est insérée avant la feuille de calcul active dans le classeur.
Si vous souhaitez insérer une feuille de calcul à une certaine position, vous pouvez également spécifier la position à laquelle vous souhaitez insérer la nouvelle table. Pour ce faire, utiliser la macro suivante :
Sub insererFeuille2() Worksheets.Add Before: = ActiveWorkbook.Worksheets (1) End Sub
Commentaires
■ La nouvelle feuille de calcul a été inséré au début du classeur, c’est-à-dire en tant que première feuille de calcul.
■ La feuille de calcul précédente avec l’index 1 est ensuite déplacée d’une position vers la droite.
Si vous souhaitez insérer la nouvelle feuille de calcul à la fin, c’est-à-dire à l’extrême droite, utilisez la macro suivante suivante:
Sub insererFeuille3 () Worksheets.Add After: = Worksheets (Worksheets.Count) End Sub
Commentaires
■ Afin de déterminer la position où la nouvelle feuille de calcul doit être, vous devez d’abord savoir combien de feuilles de calcul se trouvent déjà dans le classeur. La propriété Count vous y aide. Il fournit le nombre de feuilles de calcul.
■ Ensuite, il vous suffit de saisir l’argument After et la nouvelle feuille de calcul sera insérée en tant que dernière feuille de calcul dans le classeur.
2 Renommer une feuille de calcul
La procédure suivante renomme une nouvelle feuille de calcul dans ce classeur:
Sub CreerRenommerFeuilleCalcul () ThisWorkbook.Activate MsgBox Worksheets.Count Worksheets.Add ActiveSheet.Name = "Elie" MsgBox Worksheets.Count End Sub
Commentaires
■ Tout d’abord, le nombre de feuilles de calcul de ce classeur est déterminé et généré sous forme de contrôle à partir de la propriété Count de l’objet Worksheet.
■ La méthode Add () de l’objet Worksheets est appelée. Cela crée et insère une nouvelle feuille de calcul avant la feuille de calcul active. La nouvelle feuille deviendra automatiquement la feuille de calcul active.
■ ActiveSheet fait référence à la feuille de calcul actuellement active. Le nom d’une feuille de calcul peut être déterminé ou modifié.
■ Le nombre de feuilles de calcul est à nouveau sorti pour vérification. Elle a augmenté de 1 comme prévu.
Regardons cette autre macro :
Sub RenommageFeuille() 'Étape 1: Déterminez ce que Excel doit faire en cas d'erreur (Error) On Error GoTo MonErreur 'Étape 2: Ajoutez une nouvelle feuille et renommez Sheets.Add ActiveSheet.Name = WorksheetFunction.Text(Now(), "d-m-yyyy hh_mm_ss ") Exit Sub 'Étape 3: en cas d'erreur, veuillez informer l'utilisateur MonErreur: MsgBox "Une feuille portant ce nom existe déjà." End Sub
Commentaires
■ Dans ce cas, il faut prévoir que si une nouvelle feuille se voit attribuer un nom déjà utilisé, il y aura une erreur. Par conséquent, lors de la première étape, il a été demandé à Excel d’exécuter la troisième ligne de code immédiatement en cas d’erreur (étape 3).
■ Lors de la deuxième étape, une nouvelle feuille a été créée à l’aide de la méthode Add. Le nom par défaut de la nouvelle feuille de calcul est FeuilN, où N est le numéro suivant. À l’aide du code, nous donnons un nouveau nom en modifiant la propriété Name pour ActiveSheet.
Dans ce cas, la date et l’heure actuelles ont été utilisées pour créer le nom.
Si vous ajoutez une nouvelle feuille au classeur à l’aide de VBA, celle-ci devient automatiquement active. A la fin de cette étape, toute la procédure est terminée. Une telle solution est nécessaire : elle empêche la troisième étape de se produire, ce qui entraînerait l’apparition d’une erreur.
■ À l’étape trois, l’utilisateur est informé que la feuille portant le nom indiqué existe déjà. Nous vous rappelons que cette étape n’est nécessaire que s’il ya erreur.
Dans l’exemple suivant, la feuille de calcul Feuil3 doit porter le nom de la date actuelle.
Sub nomFeuilleDate ()
On Error Resume Next
Worksheets ("Feuil3"). Name = Date
End Sub
Commentaires
■ La date actuelle est donnée à la feuille de calcul sous un nouveau nom via la propriété Name. Vous pouvez le déterminer en appelant la fonction Date.
■ Excel obtient la date actuelle du contrôle système Windows. L’instruction On Error garantit que le cas d’erreur par exemple que cette feuille de calcul n’existe pas, est interceptée.
Tableau des noms en fonction du contenu de la cellule
Dans l’exemple suivant, le nom de la feuille de calcul est formé à partir du contenu de la cellule B1.
Sub nomFeuilleContenu ()
Worksheets (1) .Name = Range ("B1").Value
End Sub
Commentaires
■ La commande Worksheet (1).Name fait référence à la première feuille de calcul du classeur, c’est-à-dire la plus à gauche. Cette commande est similaire à Worksheet (“Feuil1”).Name.
Nommer le tableau en fonction de l’utilisateur et de la date actuelle
Dans l’exemple suivant, attribuez à la première feuille de calcul du classeur le nom de l’utilisateur, combiné à la date actuelle.
Sub nomFeuilleUser () Worksheets (1).Name = Application.UserName & "," & Date End Sub
Commentaires
■ Le nom de la nouvelle feuille de calcul est formé à partir du nom d’utilisateur d’information et de la date actuelle. Vous pouvez déterminer le nom d’utilisateur à l’aide de la propriété UserName.
■ Vous pouvez trouver le nom d’utilisateur ainsi déterminé manuellement en sélectionnant la commande Options dans le menu Fichier, puis en passant à l’onglet Général et en regardant dans le champ Options d’interface utilisateur. La fonction Date vous fournit la date actuelle. Les deux informations sont concaténées avec l’opérateur de concaténation &.
3 Supprimer les feuilles de calcul
Vous savez maintenant comment ajouter des feuilles de calcul. Mais comment les feuilles de calcul sont-elles supprimer? La méthode Delete est utiliser pour supprimer une feuille de calcul.
Sub suppressionFeuilleCalcul ()
On Error GoTo error
Sheets ("Feuil1"). Delete
Exit Sub
error:
MsgBox " il n'y a pas de feuille à supprimer "
End Sub
Commentaires
■ Au début, l’instruction On Error garantit qu’en cas d’erreur, un saut immédiat vers l’erreur se produit. Une erreur peut par exemple se produire si la feuille de calcul n’est pas dans le classeur. Une tentative est alors faite pour supprimer la feuille de calcul Feuil1.
■ L’instruction Exit Sub garantit que la macro se termine immédiatement après que la feuille de calcul a été supprimée avec succès, c’est-à-dire que l’erreur de n’est plus traitée.
■ Un message simple s’affiche à l’écran en tant que réponse d’erreur.
3.1 Supprimer la feuille de calcul sans confirmation
Si vous ne souhaitez pas voir le message de confirmation lors de la suppression de feuilles et que vous souhaitez supprimer la feuille sans demander de confirmation, utilisez la propriété DisplayAlerts.
Sub suppressionFeuilleCalcul () Application.DisplayAlerts = False Sheets (1).Delete End Sub
Commentaires
■ Définissez la propriété DisplayAlert sur la valeur False si vous souhaitez empêcher Excel d’afficher des avertissements et des messages généraux.
■ Cependant, cette valeur est définie sur True par défaut, ce qui pour cet exemple signifie que vous devez supprimer de la feuille de calcul, on vous demandera à nouveau si vous voulez vraiment la supprimer.
3.2 Suppression de toutes les feuilles sauf la feuille active
Il est souvent nécessaire de supprimer toutes les feuilles, sauf celles qui sont actives.
La macro suivante est une boucle qui vérifie séquentiellement chaque feuille et trie leurs noms en fonction du nom de la feuille active. Si une feuille portant un nom différent est trouvée, elle est supprimée. Faites attention à l’utilisation de la méthode DisplayAlerts à la quatrième étape car vous n’avez donc pas besoin de confirmer la suppression de chaque feuille.
Sub SuppressionToutesFeuilles () 'Étape 1: Déclarez la variable Dim mafeuille As Worksheet 'Étape 2: Commencez la boucle pour toutes les feuilles For Each mafeuille In ThisWorkbook.Worksheets 'Étape 3: Vérifiez le nom de chaque feuille If mafeuille.Name <> ThisWorkbook.ActiveSheet.Name Then 'Étape 4: Désactivez les messages et supprimez la feuille Application.DisplayAlerts = False mafeuille.Delete Application.DisplayAlerts = True End If 'Étape 5: Passer à la feuille suivante Suivant Next mafeuille End Sub
Commentaires :
■ Dans un premier temps, une variable nommée mafeuille est déclarée, ce qui créera un conteneur en mémoire pour chaque feuille qui sera traitée dans la boucle.
■ Dans la deuxième étape, l’action démarre une boucle qui demande à Excel d’analyser toutes les feuilles du classeur. Il y a une différence entre l’utilisation de ThisWorkbook et ActiveWorkbook. L’objet ThisWorkbook fait référence à un classeur contenant le code. D’autre part, l’objet ActiveWorkbook s’applique au classeur actif.
Par conséquent, les mêmes objets sont renvoyés, mais si le classeur dans lequel le code a été exécuté n’est pas actif, les résultats seront différents. Dans notre cas, nous ne voulons pas risquer de retirer des feuilles d’autres classeurs, c’est pourquoi nous allons utiliser la méthode ThisWorkbook.
■ Dans l’étape trois, la macro fait simplement correspondre le nom de la feuille de calcul active avec le nom de la feuille de calcul traitée par la boucle.
■ Dans l’étape quatre, si des noms sont requis, l’une des feuilles est supprimée. Comme mentionné précédemment, en utilisant la méthode DisplayAlerts, nous avons compilé des messages dans Excel.
■ À l’étape 5, passez à la feuille suivante. Après avoir analysé toutes les feuilles du classeur de macros, le travail est terminé.
3.3 Supprimer toutes les feuilles de calcul vides du classeur
De nombreux classeurs contiennent des feuilles de calcul vierges et inutiles. La macro suivante recherche dans le classeur actif des feuilles de calcul vides et les supprime.
Sub suppressionFeuillesVides () Dim i As Integer Application.DisplayAlerts = False On Error Resume Next For i = ActiveWorkbook.Sheets.Count to 1 Step -1 Sheets (i) .Activate If ActiveCell.SpecialCells (xlLastCell).Address = _ "$A$ 1" _ Then Sheets (i).Delete Next i Application.DisplayAlerts = True End Sub
Commentaires
■ Vous pouvez utiliser la propriété Count, que vous appliquez à l’objet de collection Sheets, pour déterminer le nombre de feuilles de calcul qui se trouvent dans le classeur.
■ Créez votre boucle For Next en fonction du nombre que vous avez déterminé. Activez la feuille de calcul dans la boucle et vérifiez si la méthode SpecialCells avec la constante xlLastCell renvoie l’adresse de cellule A1.
■ Dans ce cas, vous pouvez supposer qu’aucune donnée n’a été saisie dans la feuille de calcul. Supprimez ces tables à l’aide de la méthode Delete.
4 Activer la feuille de calcul
La procédure suivante permet d’activer les feuilles de calcul Feuil3 et Feuil1.
Sub ActiverFeuille()
ThisWorkbook.Activate
Worksheets("Feuil3").Activate
MsgBox ActiveSheet.Name
Worksheets("Feuil1").Activate
MsgBox ActiveSheet.Name
End Sub
Commentaires
■ La méthode Activate () de l’objet Worksheets est appelée pour activer une feuille de calcul. Cela a du sens si vous voulez être sûr de continuer à travailler sur une feuille de calcul spécifique. Les actions dans les cellules ou les plages font référence à cette feuille.
■ A titre de vérification, le nom de la feuille de calcul active est affiché dans la procédure.
Vous pouvez accéder à une feuille de calcul spécifique ou faire sauter un feuille en avant ou en arrière.
Sub activerFeuillePrecedente () On Error Resume Next ActiveSheet.Previous.Activate End Sub
■ L’instruction On Error entre en jeu si vous avez déjà activé la première feuille de calcul dans le classeur et que vous ne pouvez plus faire défiler vers la gauche. Ce cas doit être intercepté, sinon vous recevrez un message d’erreur.
Sub activerFeuilleSuivante () On Error Resume Next ActiveSheet.Next.Activate End Sub
Comme dans l’exemple précédent, vous pouvez également faire défiler vers la droite dans votre classeur. Pour ce faire, utilisez la propriété Next.
5 Copier et déplacer une feuille de calcul
Vous devez souvent accéder à des feuilles de tableau existantes pour les copier ou les déplacer vers un autre emplacement. Comment ça marche avec VBA?
5.1 Copier une feuille de calcul
Utilisez la procédure suivante pour copier une feuille de calcul dans le classeur actif classeur:
Sub CopierFeuille()
ThisWorkbook.Activate
Worksheets("Feuil1").Copy After:=Worksheets("Feuil3")
ActiveSheet.Name = "Inventaire"
End Sub
Commentaires :
■ La méthode Copy () de l’objet Worksheets est appelée. La feuille de calcul Feuil1 et copié et insérée après la feuille de calcul Feuil3, sous le nom Inventaire, dans le même classeur et devient la feuille de calcul active
■ After est un paramètre de la méthode Copy qui permet de préciser la destination de la copie.
■ Si aucun paramètre n’est spécifié, un nouveau classeur contenant la copie sera généré.
Dans l’exemple suivant, la plage utilisée dans Feuil1 est copiée et collée dans Feuil2.
Sub CopiePlageFeuil ()
Worksheets("Feuil1").UsedRange.Copy
Worksheets("Feuil2").Paste _
Worksheets("Feuil2").Range("A1")
Application.CutCopyMode = False
End Sub
Commentaires
■ Utilisez la propriété UsedRange pour déterminer la plage utilisée de la feuille de calcul.
■ Utilisez la méthode Copy pour copier cette zone.
■ Utilisez la méthode Paste pour coller la zone copiée dans Feuil2.
■ N’oubliez pas de définir la propriété CutCopyMode sur False à la fin. Cela supprime les marges de copie qui sont automatiquement générées pendant un processus de copie.
Si vous ne souhaitez pas utiliser la méthode Copy et que vous souhaitez plutôt transférer des données à l’aide de variables, vous pouvez également choisir une approche différente. La macro suivante transfère toutes les cellules de la colonne A de Feuil1 à Feuil2.
Sub transfertFeuil()
Dim maFeuil1 As Worksheet
Dim maFeuil2 As Worksheet
Dim i As Integer
Dim y As Integer
Set maFeuil1 = ThisWorkbook.Worksheets("Feuil1")
Set maFeuil2 = ThisWorkbook.Worksheets("Feuil2")
For i = 1 To maFeuil1.UsedRange.Rows.Count
y = y + 1
maFeuil2.Cells(i, 1) = maFeuil1.Cells(y, 1)
Next i
End Sub
Commentaires
■ Dans la première étape, définissez la feuille de calcul source maFeuil1 et la feuille de calcul cible maFeuil2. Pour ce faire, utilisez l’instruction Set.
■ Ensuite, vous formez une boucle qui traverse toutes les lignes utilisées dans Feuil1. Dans la boucle, vous transférez les cellules une par une d’une table à une autre.
5.2 Déplacer une feuille de calcul
Utilisez la procédure suivante pour déplacer une feuille de calcul d’un classeur. La méthode Move () correspondante fonctionne beaucoup comme la méthode Copy ():
Sub DeplacerFeuille()
ThisWorkbook.Activate
Worksheets("Inventaire").Move Before:=Worksheets("Feuil1")
End Sub
Commentaires :
■ On appelle la méthode Move () de l’objet Worksheets pour déplacer une feuille. Il est inséré derrière une feuille de calcul dans le même classeur et devient la feuille de calcul active.
■ Au lieu de Before, nous aurions pu travailler avec After ou sans paramètres After, Before, comme avec Copy ().
5.3 Déplacement de la feuille de calcul active
La macro suivante nous permet de déplacer la feuille active.
Sub DeplacerFeuille () 'Déplacer la feuille active à la fin ActiveSheet.Move After:=Worksheets(Worksheets.Count) 'Déplace la feuille active au début ActiveSheet.Move Before:=Worksheets(1) End Sub
Commentaires :
■ Cette macro fait deux choses. Premièrement, cela déplace la feuille active à la fin. En VBA, il n’est pas possible d’indiquer la “dernière feuille” comme emplacement spécifique. Mais vous pouvez calculer les feuilles, puis utiliser ces valeurs comme index pour l’objet Worksheets. Cela signifie que vous pouvez entrer dans l’exemple WorkSheet (3) et ainsi pointer vers la troisième feuille du classeur. Ainsi, vous pouvez écrire une feuille de calcul (Worksheets .Count) et indiquer la dernière feuille de cette manière.
■ La macro déplace ensuite la feuille active au début du classeur. C’est simple: utilisez l’objet Worksheets(1) pour indiquer la première feuille du classeur, puis déplacez la feuille active devant.
5.4 Transférer la feuille de calcul sans formules ni liens
Vous avez probablement déjà reçu un classeur dans lequel d’autres classeurs étaient liés. Cependant, comme vous ne disposiez pas des classeurs liés, il était inutile de mettre à jour les liens. Si vous envoyez également des classeurs et que vous souhaitez les libérer des liens au préalable, insérez un nouveau classeur et transférez un tableau sans les liens qu’il contient. Vous pouvez également automatiser cette tâche avec une macro VBA.
Sub transfererFeuilleCalcul ()
Cells.Copy
Application.Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Commentaires
■ A l’aide de la propriété Cells et de la méthode Copy, vous pouvez copier toutes les cellules de calcul actif.
■ Insérez ensuite un nouveau classeur à l’aide de l’instruction Workbooks.Add et placez le pointeur de la souris sur la cellule A1 de la première feuille.
■ Maintenant, utilisez la méthode PasteSpecial, dans laquelle vous utilisez la constante XlValues dans l’argument Paste. Cette méthode insère les données du presse-papiers dans la plage spécifiée (Range (“A1”)).
■ Définissez ensuite l’état de la propriété CutCopyMode sur False. Cela entraîne la suppression du rectangle de sélection de la zone copiée.