Selectionner une feuille en fonction de son code name défini par une variable

**jessylfoh** · 23/03/2018, 10h08

Bonjour à tous,

Voici mon problème: J'ai un classeur comportant 35 onglets qui représentent les résultats commerciaux de chaque service de ma société.
Pour chaque service, il y a deux feuilles correspondantes: une feuille avec le détail et une feuille synthèse qui est lié à la feuille détail qui totalise les résultats.
Je dois créer un fichier pour chaque service avec les deux feuilles concernées.

J'ai crée une Macro Vba qui fait les actions suivantes:
--> Changer les codes name de chaque feuille pour ne pas être embêter si les noms des onglets changent.
--> Boucler sur chaque feuille du classeur et appliquer un code selon le code name

J'ai besoin de votre aide car je n'arrive pas à trouver le code correct pour déterminer en fonction de la feuille avec le détail la feuille de synthèse correspondante et les coller tous les deux dans un nouveau classeur.

Voici mon code. Dans cet exemple, je dois copier la feuille Fe06 et Fe07 dans un nouveau classeur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
'Définir les paramètres
 
 
Public ANNEE As String
Public PERIODE As String
Public FICHIER_ORIGINE As String
Public CHEMIN_ORIGINE As Variant
Public CHEMIN As String
Public DOSSIER As String
Public SS_DOSSIER As String
Public SERVICE As String
Public CHEMIN_FICHIER_DECOUPAGE As Variant
Public She As Worksheet
Public NomFichier As String
Public NomFeuille As String
 
 
Sub Decoupage_Fichier()
 
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Sh3 As Worksheet
Dim Sh4 As Worksheet
Dim Sh5 As Worksheet
Dim Sh6 As Worksheet
Dim Sh7 As Worksheet
Dim ShS As Worksheet
Dim Fe07 As Worksheet
Dim NomShs As String
 
'Définir les valeurs des paramètres
    ANNEE = Range("C4").Value
    PERIODE = Range("C5").Value
    FICHIER_ORIGINE = Range("C6").Value & ".xlsx"
    CHEMIN_ORIGINE = Workbooks(FICHIER_ORIGINE).FullName
 
'Créer les sous dossier le cas échéant :
    CHEMIN = "W:\TDB BUDGET BILAN FRANCE\ETB\"
    DOSSIER = "\Budget\CA"
    SS_DOSSIER = "\Envois\"
 
        'Pour le dossier ANNEE
            If Dir(CHEMIN & ANNEE, 16) = "" Then
            MkDir (CHEMIN & ANNEE)
            End If
        'Pour le dossier Travaux TDB (DOSSIER)
            If Dir(CHEMIN & ANNEE & DOSSIER, 16) = "" Then
            MkDir (CHEMIN & ANNEE & DOSSIER)
            End If
        'Pour le dossier Envois Données (SS_DOSSIER)
            If Dir(CHEMIN & ANNEE & DOSSIER & SS_DOSSIER, 16) = "" Then
            MkDir (CHEMIN & ANNEE & DOSSIER & SS_DOSSIER)
            End If
        'Pour le dossier PERIODE
            If Dir(CHEMIN & ANNEE & DOSSIER & SS_DOSSIER & PERIODE, 16) = "" Then
            MkDir (CHEMIN & ANNEE & DOSSIER & SS_DOSSIER & PERIODE)
            End If
 
'Activer le fichier d'origine à découper
Windows(FICHIER_ORIGINE).Activate
 
'Mettre en calcul Manuel
        'Application.Calculation = xlManual
 
'Qualifier les feuilles et changer le Code Name
        'ETB
            Set Sh1 = Sheets("ETB")
            Sh1.[_CodeName] = "Fe01"
        'ETB Exploitation
            Set Sh2 = Sheets("ETB Exploitation")
            Sh2.[_CodeName] = "Fe02"
        'ETB Zones-Ciaux
            Set Sh3 = Sheets("ETB Zones-Ciaux")
            Sh3.[_CodeName] = "Fe03"
        'ETB Ciaux-Zones
            Set Sh4 = Sheets("ETB Ciaux-Zones")
            Sh4.[_CodeName] = "Fe04"
        'ETB Budget Mensuel
            Set Sh5 = Sheets("ETB Budget Mensuel")
            Sh5.[_CodeName] = "Fe05"
        'Synthèse DOM TOM
            Set Sh7 = Sheets("Synth DOM TOM")
            Sh7.[_CodeName] = "Fe07"
        'DOM TOM
            Set Sh6 = Sheets("DOM TOM")
            Sh6.[_CodeName] = "Fe06"        
 
 
 For Each She In ActiveWorkbook.Worksheets
 
 Select Case She.[_CodeName]
 
'Fichier "DOM TOM"
 
        Case Is = "Fe06"
                'Définir le nom du Service en fonction de la ou les feuilles copiées
                    SERVICE = "DOM TOM"
                    T = 6
                'Définir la feuille synthèse
                If T + 1 < 10 Then
                    NomShs = "Fe0" & T + 1
                              Else
                    NomShs = "Fe" & T + 1
                End If
 
'Voici l'endroit où je ne trouve pas le bon code             
                    'Sheets(NomShs).Select
                    'Set Fe = NomShs
                    Sheets(Fe07).Select
 
 
                'Copier les feuilles dans un nouveau classeur --> besoin d'aide pour ce code aussi
 
 
                'Définir le chemin du fichier découpage
                    CHEMIN_FICHIER_DECOUPAGE = CHEMIN & ANNEE & DOSSIER & SS_DOSSIER & PERIODE & "\Matrice Budget ETB_" & SERVICE & "_" & PERIODE & ".xlsx"
 
                    Call ENREGISTRER_CLASSEUR
                    Sheets.Add Count:=1
 
                'Fermer le fichier et activer le fichier d'origine
                    Call FERMER_CLASSEUR
                    Workbooks(FICHIER_ORIGINE).Activate
 
 
        Case Is = "Fe08"
...
 End Select
 Next She
 
 MsgBox ("les Fichiers sont prêts pour l'envoi")
 
 End Sub

Je vous remercie par avance de votre aide.

**joe.levrai** · 23/03/2018, 10h22

Bonjour,

c'est ceci que tu veux ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub toto()
Dim Sh7 As Worksheet
Set Sh7 = Sheets("Feuil2")
    Sh7.[_CodeName] = "Fe07"
    Fe07.Select  ' sélection de la feuille via son CodeName
End Sub

**Pierre Fauconnier** · 23/03/2018, 10h26

Salut.

Nativement, VBA ne propose pas de trouver une feuille via son codename (comme il le propose par rapport à son nom Excel). Tu dois passer par une fonction perso. En voici une qui renvoie la feuille concernée ou Nothing si non trouvée. Insensible à la casse.

A te garder dans un module Tools, par exemple, pour te constituer une boite à outils que tu balades de projet en projet, histoire de ne pas réinventer la roue à chaque fois.

[EDIT] Code corrigé suite à la remarque de Joe...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function getWorksheetByCodename(Codename As String, Optional WB As Workbook) As Worksheet
  Dim Counter As Long
  Dim Found As Boolean
 
  If WB Is Nothing Then Set WB = ActiveWorkbook
  Counter = 1
  Do While Counter <= WB.Worksheets.Count And Not Found
    If StrComp(Codename, WB.Worksheets(Counter).Codename, vbTextCompare) = 0 Then
      Set getWorksheetByCodename = WB.Worksheets(Counter)
      Found = True
    End If
    Counter = Counter + 1
  Loop
End Function

**joe.levrai** · 23/03/2018, 10h30

Bonjour Pierre,

quelle est l'utilité de la variable Found ? Elle n'interrompt pas ta boucle Do While.

Je m'attendais plutôt à un Exit Function à la place (et une boucle For i = 1 to WB.Worksheets.Count pour éviter la variable Counter), mais quelque chose doit m'échapper ?

**Pierre Fauconnier** · 23/03/2018, 10h32

Salut Joe

Envoyé par joe.levrai

Bonjour Pierre,

quelle est l'utilité de la variable Found ? Elle n'interrompt pas ta boucle Do While.

Je m'attendais plutôt à un Exit Function à la place (et une boucle For i = 1 to WB.Worksheets.Count pour éviter la variable Counter), mais quelque chose doit m'échapper ?

Je n'ai pas ma boite à outils avec moi, et j'ai réécris la fonction en vitesse. Je viens de corriger dans le code donné dans ma première réponse.

Found sert justement à ne pas avoir à utiliser Exit For. Je n'aime pas les Exit Machin, question personnelle. Mais effectivement, si on n'est pas allergique aux EXIT, ta solution d'un for est parfaitement valable.

Merci de m'avoir fait remarquer ma méprise...

**jessylfoh** · 23/03/2018, 10h33

Envoyé par joe.levrai

Bonjour,

c'est ceci que tu veux ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub toto()
Dim Sh7 As Worksheet
Set Sh7 = Sheets("Feuil2")
    Sh7.[_CodeName] = "Fe07"
    Fe07.Select  ' sélection de la feuille via son CodeName
End Sub

Bonjour,

En fait, je n'ai pas précisé que le code Name de la seconde feuille est terminée par une variable dans mon code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
T = 6
                'Définir la feuille synthèse
                If T + 1 < 10 Then
                    NomShs = "Fe0" & T + 1
                              'Else
                    NomShs = "Fe" & T + 1
                End If

Mon problème est que je n'arrive pas à utiliser NomShs pour sélectionner ma feuillle.
Mon second problème est que je dois sélectionner les deux feuilles en même temps (ici Fe06 et la Fe07 qui est déterminé par NomShs).
Merci encore de votre aide

**Pierre Fauconnier** · 23/03/2018, 10h35

Jessye,

La fonction que je te propose permet d'utiliser une variable pour choisir ta feuille. Il te suffit de composer le nom puis de le passer à ma fonction pour récupérer la feuille correspondante.

Exemple d'utilisation qui recompose le codename sur base d'une racine commune

Nom : 2018-03-23 10_48_56-Microsoft Edge.png
Affichages : 2436
Taille : 25,8 Ko

Nom : 2018-03-23 10_48_56-Microsoft Edge.png
Affichages : 2436
Taille : 25,8 Ko

**jessylfoh** · 23/03/2018, 10h51

Bonjour Pierre,

Désolée je n'avais pas vu tes messages précédents.
Je commence à me débrouiller avec vba à force de parcourir les forums mais je ne sais pas commencer faire pour utiliser ta fonction dans mon code.
Je vais l'intégrer dans mon module mais comment je fais pour l'utiliser dans la procédure de ma macro d'origine??
Merci de ton aide.

**Pierre Fauconnier** · 23/03/2018, 11h04

Envoyé par jessylfoh

[...]comment je fais pour l'utiliser dans la procédure de ma macro d'origine??
Merci de ton aide.

Tu l'utilises comme je l'ai illustré dans la procédure TEST, c'est-à-dire comme une fonction native de VBA qui renvoie un objet de type Worksheet.

En fait, tu l'utilises comme tu utiliserais Worksheets("Pierre") si tu voulais manipuler une feuille sur base de son nom Excel. Mais comme Excel ne propose pas nativement la fonction, tu remplaces Worsheet par GetworksheetByCodename et Pierre par le codename souhaité, qu'il soit en dur ou recomposé au sein d'une variable (comme je l'illustre dans la procédure TEST)...

**Marc-L** · 23/03/2018, 14h37

Bonjour,

une autre fonction selon le CodeName dans cette discussion …

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

Selectionner une feuille en fonction de son code name défini par une variable

Macros et VBA Excel

Discussions similaires

Partager

Partager