[VIDEO]Comment créer un modèle de feuille et le dupliquer à l'infini
Aujourd’hui, je vous propose de voir comment créer un schéma de feuille de calcul, que nous pourrons ensuite réutiliser comme modèle de base pour travailler sur de nouvelles feuilles. En d’autres termes, nous allons mettre en place une feuille de calcul avec une mise en forme complète (dans laquelle nous retrouverons des formules, des en-têtes et tout un tas d'autres informations) et que nous pourrons ensuite réutiliser à volonté dans de nouveaux documents ; en cliquant uniquement sur un bouton. Pour l’exemple nous allons utiliser un modèle pour créer une feuille de facturation. Ce modèle nous permettra ensuite de générer rapidement une nouvelle feuille de calcul dans laquelle nous n'aurons plus qu'à saisir les informations propres à cette facturation.
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier dans le lien disponible en fin d’article (cliquez ici pour y accéder).
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Présentation
Le document que nous allons utiliser est celui que vous retrouverez juste en dessous, et que je vous invite à télécharger en remplissant le formulaire disponible dans l’introduction de cet article afin de suivre le tutoriel.
Il s'agit d'une feuille de calcul présentant une facturation, et dans laquelle nous allons retrouver les différentes informations habituelles.
Nous y retrouvons ainsi :
- L’entête avec les coordonnées de la société,
-
Les coordonnées du client,
-
La référence de la facture ainsi que la date d’établissement,
-
Chacune des lignes de la facture, avec l’identification de l’article (code et description), la quantité, le prix unitaire, le taux de TVA applicable et le montant HT de la ligne (qui correspond au nombre d’unités multiplié par le prix unitaire),
-
Plus bas, nous retrouvons bien entendu l’encart avec le total hors taxes de la facture, le montant de la tva (que nous calculons avec la formule SOMMEPROD() qui permet de réaliser la somme des montants hors taxes que nous multiplions tout d’abord par le taux de TVA) et enfin le montant TTC de la facture,
- Pour finir, nous retrouvons un certain nombre de formalités propres à ce type de document commercial
Nous allons écrire un programme (une macro-commande en VBA) qui va nous permettre de dupliquer cette feuille de calcul en la « nettoyant » de tous les éléments à mettre-à-jour (afin de générer une nouvelle facture), en appuyant sur un seul bouton.
2. La macro-commande
Pour créer une macro commandes en VBA, il faut se rendre dans l’outil de développement d’Excel (VBA). Le moyen le plus simple pour s’y rendre, est de faire un clic-droit sur l'onglet de la feuille de calcul et choisir l’option « Visualiser le code ».
De cette manière nous arrivons directement sur une feuille blanche, qui correspond à la feuille de saisie du code à insérer directement dans la feuille de calculs sélectionnée. Ici nous allons plutôt travailler dans un nouveau module. Nous allons donc cliquer sur le menu « Insertion », puis « Module ».
De cette manière, le code que nous allons saisir sera disponible dans l'ensemble des feuilles du classeur, et nous pourrons éventuellement l’exporter afin de le réutiliser dans d’autres documents de travail.
2.1. Dupliquer le modèle
La macro-commande qui va nous permettre de dupliquer le modèle est la suivante :
Sub dupliquer()
Sheets("Facture").Copy after:=Sheets(Sheets.Count)
End Sub
Nous commençons par créer une nouvelle macro qui porte le nom de « Sub dupliquer() », puis nous y insérons une ligne de code qui va se charger de dupliquer la feuille « Facture » et de placer cette nouvelle feuille tout à la fin du classeur (« Sheets.Count » permet de positionner cette feuille après la dernière feuille du classeur).
Lorsque l’on teste la macro (en cliquant sur le petit triangle vert depuis l’outil de développement VBA, ou en appuyant sur les touches [Ctrl]+[F8] depuis la feuille de calcul), une nouvelle feuille qui est la copie conforme de la feuille « Facture » est maintenant insérée.
Celle-ci porte le nom de « Facture (2) » et l’onglet qui permet de l’afficher se trouve bien tout à droite de la liste des feuilles de calculs.
2.2. Supprimer les données dupliquées
Pour supprimer les données présentent au sein de la facture d’origine, nous allons procéder de la manière suivante :
- Nous allons commencer par sélectionner les cellules susceptibles de devoir être réinitialisées (pour sélectionner plusieurs cellules qui ne sont pas contiguës, nous maintenons la touche [Ctrl] du clavier,
- Puis nous allons donner un nom à cet ensemble de cellules afin de pouvoir les appeler facilement, toutes ensembles. Pour cela nous allons saisir ce nom directement dans la « Zone de nom », en haut à gauche de la fenêtre Excel (pour valider le nom, nous appuyons ensuite sur la touche [Entrée] clavier). Ici, nous choisissons d’appeler ces cellules « _zoneSaisie » :
À présent que ces cellules sont assemblées dans une seule et même plage de cellules, nous allons pouvoir modifier notre macro-commande en ajoutant la ligne suivante :
Sub dupliquer()
Sheets("Facture").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("_zoneSaisie").ClearContents
End Sub
Cette nouvelle ligne permet de supprimer tout contenu présent dans la plage des cellules qui porte le nom de « _zoneSaisie » dans notre nouvelle feuille de calcul.
Un nouveau test permet de contrôler que tout se passe bien :
La nouvelle feuille de calcul porte maintenant le nom de « Facture (3) ». Voyons maintenant comment donner le numéro de facture comme nom de feuille afin de faciliter la recherche des factures.
2.3. Renommer la feuille de calcul de manière automatique
Pour commencer, nous allons instancier une nouvelle variable tout au début de notre macro « dupliquer() », qui va permettre de stocker le numéro de la facture. Ensuite, nous allons demander à l’utilisateur de renseigner cette information. Puis à la fin de la procédure, lorsque le modèle aura été dupliquer, nous allons modifier le nom de la feuille :
Sub dupliquer()
Dim numFacture As String ' Création d'une nouvelle variable de type string (= chaîne de caractères) afin d'y stocker des caractères alphanumériques
numFacture = InputBox("Numéro facture") ' Présentation d'une zone de saisie pour demander le numéro de la facture à l'utilisateur
Sheets("Facture").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("_zoneSaisie").ClearContents
ActiveSheet.Name = numFacture ' Assignation du numéro de la facture au nom de la feuille
End Sub
Nous pouvons tester la procédure :
La facture « FA1234 » est bien insérée en fin de classeur.
Nous allons profiter du fait que l’utilisateur ait renseigné le numéro de la facture pour l’enregistrer directement dans la zone prévue à cet effet dans la facture. Pour cela, nous allons donner un nom à cette cellule (« _reference »), ainsi qu’à la cellule située juste en dessous (« _date »), dans laquelle nous viendrons saisir automatiquement la date du jour :
Puis nous allons ajouter les deux lignes suivantes dans notre code :
Sub dupliquer()
Dim numFacture As String
numFacture = InputBox("Numéro facture")
Sheets("Facture").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("_zoneSaisie").ClearContents
ActiveSheet.Name = numFacture
ActiveSheet.Range("_reference").Value = numFacture ' Ajout du numéro de la facture
ActiveSheet.Range("_date").Value = Now() ' Ajout de la date du jour
End Sub
