Dupliquer une macro sur plusieurs onglets

[Mag555]

Bonjour,

J'aimerais à partir d'une même macro la dupliquer sur les différents onglets de mon fichier excel.

En sachant que seules les sources changeront sensiblement:

En effet au lieu de 0610, il faut s'attendre à avoir 0710, 0810, 0910 etc...

Je vous joins une partie de mon code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
'Filtre élaboré pour obtenir matricules selon centre de coût sélectionné
    Workbooks("Ctrl Gestion -SG- analyse par postes -0610.xls").Sheets("RUB2"). _
        Columns("A:S").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "A1:A2"), CopyToRange:=Range(activecell, Cells(a, 2)), Unique:=False
 
 
 
    '1ère ligne formules 2010
    activecell.Offset(1, 1).Select
    activecell.Formula = Application.VLookup(activecell.Offset(0, -2), Workbooks("Ctrl Gestion -SG- analyse par postes -0610.xls").Sheets("RUB2").Columns("A:S"), 2, False)
 
    activecell.Offset(0, 1).Select
    activecell.Formula = Application.VLookup(activecell.Offset(0, -2), Workbooks("Ctrl Gestion -SG- analyse par postes -0610.xls").Sheets("RUB2").Columns("A:S"), 3, False)
 
    activecell.Offset(0, 1).Select
    activecell.Formula = Application.VLookup(activecell.Offset(0, -3), Workbooks("Ctrl Gestion -SG- analyse par postes -0610.xls").Sheets("RUB2").Columns("A:S"), 5, False)
 
    activecell.Offset(0, 1).Select
    Selection = ""
 
    activecell.Offset(0, 1).Select
    activecell.Formula = Application.VLookup(activecell.Offset(0, -5), Workbooks("Ctrl Gestion -SG- analyse par postes -0610.xls").Sheets("RUB2").Columns("A:S"), 11, False)

En partant du principe que je pourrais lier une cellule à la période souhaitée dans chaque onglet, comment puis-je spécifier cela dans mon code?

Vous remerciant par avance pour votre aide,
Très bonne journée

Magali

[BrunoM45]

Bonjour Mag555,

Au lieu de dupliquer une macro ce qui allourdi le fichier,
il suffit de la mettre dans ThisWorkbook ... tout dépend de l'utilisation que l'on veut en faire

A+

[Theze]

Bonsoir,

Généralement on ne duplique jamais une macro, on crée une fonction ou une procédure et on lui passe différentes valeurs en arguments.

Hervé.

[Mag555]

Peux-tu m'expliquer rapidement à quoi sert le Workbook? C'est un fichier qui stocke les fichiers sources?

Exemple: Si j'ai 20 sources utilisées au total dans mes différents onglets mais que dans la feuille actuelle, je n'en utilise que 3, lesquelles vais-je mettre dans le Workbook? Et comment se fera la liaison entre les fichiers dans le Workbook et mes codes?

[MattChess]

Envoyé par Mag555

à quoi sert le Workbook ?

A faire des traitements de niveau Workbook
Exemple : à l'ouverture du classeur .xls dans Workbook_Open().
__________________

Il vaut mieux définir les procédures communes dans un module.

Ouvrir Excel.
Dans le classeur souhaité, ouvrir le Visual Basic Editeur par Alt+F11, c-a-d :
Cliquez sur l'Excel menu "Outils" > "Macro" > "Visual Basic Editeur" (VBE)
Cliquez sur le VBE menu "Insérer" > "Module".

Envoyé par Mag555

Et comment se fera la liaison entre les fichiers et mes codes?

Envoyé par Mag555

En effet au lieu de 0610, il faut s'attendre à avoir 0710, 0810, 0910 etc...

Dans la procédure FilterXls(), on passe en paramètre le suffixe du .xls tel que "0710" ou "0610" défini par la constante correspondante filenameSuffix610.

Dans la fenêtre d'Edition de Module1, copier-coller :

Code :

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
Option Explicit ' Filtre selon classeur
Public Const filenamePrefix = "Ctrl Gestion -SG- analyse par postes -"
Public Const filenameSuffix610 = "0610", filenameSuffix710 = "0710"
Public Const nameSheetRub = "RUB2" ' Commentez ...
 
' Ajoute un filtre sur le classeur filenamePrefix + filenameSuffix610
Sub FilterXls(ByVal strSuffix As String)
Dim wkBook As Workbook, strPath As String, rngTarget As Range
Dim a As Integer
 
    Stop ' Déboguer en pas à pas puis enlever l'instruction Stop quand la procédère est au point.
    strPath = filenamePrefix + strSuffix + ".xls"
    On Error Resume Next
    Set wkBook = Workbooks(strPath)
    If Err.Number <> 0 Then Warning "1000: Impossible d'accéder au classeur " + vbCrLf + _
                                    strPath: Exit Sub
    On Error GoTo 0
 
    a = 123 ' Définir la valeur de a et renommez le nom de la variable sur au moins cinq lettres.
    Set rngTarget = Range(ActiveCell, Cells(a, 2)) ' Cible pour CopyToRange
    'Filtre élaboré pour obtenir matricules selon centre de coût sélectionné
    wkBook.Sheets(nameSheetRub).Columns("A:S").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("A1:A2"), CopyToRange:=rngTarget, Unique:=False
    '...
End Sub
 
' Common error management
Sub Warning(ByVal strMsg As String)
Const lenErr = 4 ' Number of digits of the error code beginning the message
    If Err.Number <> 0 Then
        strMsg = strMsg + vbCrLf + "Error " + Str(Err.Number) + ": " + Err.Description
    End If
    MsgBox Mid(strMsg, lenErr + 3), vbExclamation, "Filter warning " + Left(strMsg, lenErr)
End Sub

Dans la fenêtre d'Exécution immédiate (Ctrl+G) du VBE (Visual Basic Editeur) d'Excel, copier-coller et valider par ENTER :

Code :

FilterXls filenameSuffix610

___________

Si la discussion est résolue, vous pouvez cliquer sur le bouton

En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert ci-dessous.

[Mag555]

Merci mille fois pour cette réponse très etoffée MattChess.

Si j'ai bien compris, cela n'est pas gênant en soit que tout mon code soit contenu dans le userform de ma feuille ?

Pour permettre à mes fichiers sources notamment d'être "variabilisés" sur les différentes feuilles, il me suffit d'entrer les codes ci-dessous dans un module de chaque feuille.

La seule chose qui changera devrait être

Code :

Public Const filenameSuffix610 = "0610"

Mais je me pose encore une question, dans mon Userform où je spécifie encore mes chemins d'accès aux fichiers en toutes lettres, comment vba va-t-il faire le lien entre mon module et mon userform?

Je m'explique : Que deviendraient les lignes de sources de fichiers comme

Code :

1
2
3
4
'Filtre élaboré pour obtenir matricules selon centre de coût sélectionné
    Workbooks("Ctrl Gestion -SG- analyse par postes -0610.xls").Sheets("RUB2"). _
        Columns("A:S").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "A1:A2"), CopyToRange:=Range(activecell, Cells(a, 2)), Unique:=False

Et je suppose que pour des feuilles nommées dans l'onglet, il faut simplement les remplacer par Activesheet ?

Désolée pour les questions qui fusent, mais débuter dans VBA, c'est pas une mince affaire !

[MattChess]

Envoyé par Mag555

... dans un module de chaque feuille.

Un module est global. Il n'est pas lié à une feuille particulière. C'est pourquoi on peut mettre dans un module des procédures communes pour toutes les feuilles qu'elles les utilisent ou non.

Envoyé par Mag555

La seule chose qui changera devrait être

Code :

Public Const filenameSuffix610 = "0610"

Oui, en fait ce qui changera c'est l'appel de la fonction commune telle que FilterXls() grâce à son paramètre qui indique une partie du nom du .xls à traiter.

Envoyé par Mag555

dans mon Userform où je spécifie mes chemins d'accès aux fichiers en toutes lettres, comment vba va-t-il faire le lien entre mon module et mon userform?

Au moment de la validation de l'Userform en cliquant sur le bouton "OK", on récupère par exemple dans un TextBox un chemin de classeur .xls.
Si on veut ensuite appeler la procédure commune FilterXls() du module, il faut extraire le suffixe du chemin saisi dans l'Userform.

Dans la fenêtre d'Exécution immédiate (Ctrl+G) du VBE (Visual Basic Editeur) d'Excel, copier-coller et valider par ENTER :

Code :

? Left(Right("Ctrl Gestion -SG- analyse par postes -0910.xls", 8), 4)

0910

On a extrait le suffixe "0910" du nom du classeur en composant les fonctions d'extraction de chaînes Left() et Right().

Envoyé par Mag555

Que deviendraient les lignes de sources de fichiers comme

Code :

La mise en place du filtre a été découpée sur plusieurs lignes pour rendre variable le suffixe "0610" du classeur en paramètre de la procédure FilterXls() :

Code :

1
2
3
Set wkBook = Workbooks(strPath) ' Voir ligne 14 
'...
wkBook.Sheets(nameSheetRub).Columns("A:S").AdvancedFilter '... ligne 22

Suivre avec le débogueur le contexte d'appel dans les lignes 12 à 23 de FilterXls().
___________

En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert ci-dessous.