Om a des centaines de feuilles de calcul nommées différemment, toutes dans le même classeur. La structure de chaque feuille de calcul est essentiellement la même. Il souhaite copier une plage fixe (A146: O146) de chaque feuille de calcul vers une nouvelle feuille de calcul, une ligne après l’autre.

Il y a plusieurs façons de procéder. Pour mon argent, une simple macro est le meilleur pari. Avant d’entrer dans l’approche macro-basée, cependant, il existe un moyen de le faire à l’aide de formules. Une manière assez unique consiste à définir une formule dans le gestionnaire de noms. Suivez ces étapes pour commencer:

  1. Affichez l’onglet Formules du ruban.

  2. Dans le groupe Noms définis, cliquez sur l’outil Gestionnaire de noms. Excel affiche la boîte de dialogue Gestionnaire de noms.

  3. Cliquez sur le bouton Nouveau. Excel affiche la boîte de dialogue Nouveau nom. (Voir la figure 1.)

  4. Dans le champ Nom, entrez le nom ListSheets (notez qu’il s’agit d’un seul mot, sans espaces).

  5. Dans le champ Se réfère à, entrez la formule suivante:

  6. Cliquez sur le bouton OK pour terminer la création de la plage nommée. La nouvelle plage doit apparaître dans la boîte de dialogue Gestionnaire de noms.

  7. Cliquez sur le bouton Fermer pour fermer la boîte de dialogue Gestionnaire de noms.

Avec la formule définie de cette manière, vous pouvez ensuite accéder à la feuille de calcul sur laquelle vous souhaitez fusionner toutes ces plages. (Disons que le nom de cette feuille de calcul est «Résumé».) Je suggère fortement de vous assurer que cette feuille de calcul est la toute dernière de votre classeur. Dans la feuille de calcul Résumé, mettez la formule suivante dans la colonne A de n’importe quelle ligne:

=INDIRECT(INDEX(ListSheets,ROWS($A$1:$A1))&"!"&CELL("address",A$146))

Copiez la formule vers le bas mais de nombreuses lignes sont nécessaires pour représenter toutes les feuilles de calcul. En d’autres termes, si vous avez 25 feuilles de calcul (sans compter la feuille de calcul Résumé), copiez la formule sur 24 lignes. En comptant l’original, la formule devrait maintenant apparaître sur un total de 25 lignes.

Remarque: la formule ListSheets – celle que vous avez définie dans le Gestionnaire de noms – renvoie un tableau de noms de feuilles de calcul. La fonction ROWS est utilisée pour déterminer quel élément de ce tableau est renvoyé via la fonction INDEX. Si votre feuille de calcul Résumé n’est pas la dernière de votre classeur, elle peut facilement être renvoyée par ListSheets et vous finirez par en extraire des valeurs. C’est sans aucun doute quelque chose que vous ne voulez pas faire, c’est pourquoi j’ai suggéré de vous assurer que Résumé était la dernière feuille de calcul du classeur.

Maintenant, copiez simplement les formules de la colonne A vers la droite afin de les avoir tout au long de la colonne O. Le résultat est que vous aurez les valeurs de A146: O146 dans les cellules contenant les formules.

J’ai dit plus tôt que je pense qu’une approche macroéconomique est le meilleur pari.

Voici une petite macro qui montre pourquoi c’est le cas.

Sub CopyRange()

Dim w As Worksheet     Dim sRange As String     Dim lRow As Long

sNewName = "Summary" 'Name for summary worksheet     sRange = "A146:O146" 'Range to copy from each worksheet

Worksheets(1).Select     Worksheets.Add     ActiveSheet.Name = sNewName

lRow = 2     For Each w In Worksheets         If w.Name <> sNewName Then             'Comment out the following line if you don't want to             'include worksheet names in the summary sheet             Cells(lRow, 1) = w.Name             'If you commented out the previous line, make a change             'in the following line: change (lRow, 2) to (lRow, 1)

w.Range(sRange).Copy Cells(lRow, 2)

lRow = lRow + 1         End If     Next w End Sub

Notez qu’il existe deux variables (sNewName et sRange) qui sont définies au début de la macro. Ceux-ci représentent le nom que vous souhaitez utiliser pour la nouvelle feuille de calcul récapitulative créée par la macro et la plage de cellules que vous souhaitez copier à partir de chaque feuille de calcul.

La macro rend ensuite active la première feuille de calcul du classeur et ajoute une nouvelle feuille de calcul à utiliser pour la synthèse. Cette feuille de calcul reçoit le nom que vous avez spécifié dans la variable sNewName. La macro parcourt ensuite une boucle vérifiant chacune des autres feuilles de calcul. Tant que la feuille de calcul n’est pas la feuille de calcul, le nom de la feuille de calcul est placé dans la colonne A de la feuille de synthèse et la plage spécifiée dans la variable sRange (A146: O146) est copiée dans la feuille de calcul de synthèse à partir de la colonne B .

L’approche macro est simple et rapide. De plus, si jamais vous avez besoin de refaire votre feuille de résumé, supprimez simplement l’ancienne et réexécutez la macro.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (9812) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.