Comment changer des noms de feuilles dans une macro

[PFRANCO]

Bonsoir à tous,

Je cherche depuis un petit moment comment changer automatiquement une ou plusieurs noms de feuille situés dans une macro.
Le nom de la feuille se situe dans une cellule précise ex: "A2" et la cellule en question se trouve dans la feuille nommée "Base".

Je tourne en rond en essayant de reprendre des bouts de codes mais Pufff...

quelqu'un a-t-il une piste?

Ci joint un fichier exemple.

Merci à tous.

Philippe.

 

[TooFatBoy]

Re : Comment changer des noms de feuilles dans une macro

Salut,

Au départ tu avais une macro nommée Macro1, nous l'avons très légèrement transformée pour remplacer le code fixe et immuable "B2" par une variable nommée Nom.


Actuellement tu affectes une nouvelle valeur à la variable Nom puis que tu recopies exactement le même code derrière, tu comprends bien que ce n'est pas très "judicieux" ou "pratique".
Donc ce que tu peux faire, c'est créer une macro (nommée par exemple AppelMacro1) qui va appeler la macro nommée Macro1.

Mais si AppelMacro1 se contente d'appeler tout bêtement Macro1, Macro1 va toujours faire la même chose...
Il faut donc que AppelMacro1 passe un paramètre à Macro1, à savoir la bonne valeur de Nom.


Donc pour résumer tu crées une macro AppelMacro1 qui fait une boucle pour chaque case de la colonne B à traiter, avec un appel à Macro1 dans cette boucle.

Est-ce compréhensible ce que je raconte-là ???


Bon, en fait je dis ça à vue de nez, maintenant il faut que je vérifie que je ne me suis pa trp trompé dans ce que j'ai raconté...

 

[TooFatBoy]

Re : Comment changer des noms de feuilles dans une macro

Après une petite simplification (il y a moyen de simplifier un peu plus puisqu'elle ne s'exécute que lors de la création d'une nouvelle feuille), la macro Macro1 doit donner ça :

Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 22/06/2008 par PH CONSULTANTS
'
Dim Nom As String

    Application.ScreenUpdating = False
    ActiveSheet.Unprotect

    [COLOR="Blue"]Nom = Sheets("Base").Range("B2")[/COLOR]

    Sheets.Add
    [COLOR="red"]ActiveSheet.Name = Nom[/COLOR]
    Sheets("listing").Shapes("Button 3").Copy
    ActiveSheet.Paste
    Sheets("listing").Select
    Range("A5").Select
    [COLOR="red"]Sheets(Nom).Range("E4").Value = Nom[/COLOR]
    Sheets("REPARTITION CHAMBRES").Select
    Rows("7:7").Select
    Selection.AutoFilter
    [COLOR="red"]Selection.AutoFilter Field:=5, Criteria1:=(Nom)[/COLOR]
    Range("E7:AP7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    [COLOR="red"]Sheets(Nom).Select[/COLOR]
    Range("A7").Select
    ActiveSheet.Paste

    Range("F2").Value = "FICHE JOURNALIERE INDIVIDUELLE DE TRAVAIL"
    With Range("F2:K3").Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("G5").Value = Range("E4")
    Range("F2:K3,G5:J5").Select
    With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .Font.Bold = True
        .MergeCells = True
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With

    Range("M2").Value = "Nb total de chambres"
    Range("M4").Value = "Durée totale de travail"
    With Range("M2:N2,M4:N4")
        .MergeCells = True
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
    End With
    
    Range("O2").FormulaR1C1 = "=COUNTA(C[-13])-1"
    Range("O4").FormulaR1C1 = "=SUM(R[4]C:R[8]C)"
    Range("O2,O4").Select
    With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .MergeCells = False
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Font.Bold = True
    End With
    
    Range("E4").Font.ColorIndex = 2
    Range("A7").Select
    
    Sheets("REPARTITION CHAMBRES").Select
    Selection.AutoFilter
    Range("E7").Select

End Sub

On voit bien que dans toute la macro (dans les lignes en rouges) on n'utilise que la variable nom, après l'avoir initialisée dans la ligne en bleu, pour faire référence au nom de l'employé.
On ne fait donc plus directement référence à la cellule "B2" de la feuille "Base".

Il ne reste plus qu'à supprimer la ligne en bleu et à appeler cette macro en lui passant le paramètre Nom initialisé avec la valeur désirée (par exemple le contenu de la cellule "B2" de la feuille "Base", biensûr. ).

 

[PFRANCO]

Re : Comment changer des noms de feuilles dans une macro

Bonjor Marcel 32,

Merci pour ta réponse. Je me doutais bien que metrre à la suite 30 fois la même opération n'était pas conventionnel. Mais je ne comprends pas vraiment ta proposition, je ne suis pas encore trés "pro" tu sais.
Quand tu dis:
on n'utilise que la variable nom, après l'avoir initialisée dans la ligne en bleu, pour faire référence à au nom de l'employé.
On ne fait donc plus directement référence à la cellule "B2" de la feuille "Base".
et:
Il ne reste plus qu'à supprimer la ligne en bleu et à appeler cette macro en lui passant le paramètre Nom initialisé avec la valeur désirée (par exemple le contenu de la cellule "B2" de la feuille "Base", biensûr. ).

Je ne sais pas vraiment de quoi il s'agit. J'ai bien compris que tu remplaces le nom de feuille par "Nom" ok.

J'ai donc installé le code que tu m'a concocté et cela fonctionne. Maintenant, que faut il rajouté dans le code pour que, automatiquement, soit balayé la colonne "B" de "B2:B31" dans la feuille "Base" et ainsi enchainé la création de toutes les feuilles an rapport avec les noms?

A+. Merci.

Philippe

 

[TooFatBoy]

Re : Comment changer des noms de feuilles dans une macro

Bonjour, je vais tenter de t'expliquer simplement le principe.


Tout d'abord il faut savoir qu'une macro peut en appeler une autre, par exemple pour effectuer un travail répétitif (ça tombe bien, non ? )

Prenons par exemple une macro nommée Mac1 et une macro nommée Mac2 et imaginons que la macro Mac1 appelle la macro Mac2. Que se passe-t-il en gros ?
Eh bien c'est très simple :
- la macro Mac1 se déroule jusqu'à ce qu'elle arrive à sa ligne de code qui fait appel à la macro Mac2.
- la macro Mac2 va alors se dérouler, puis quand elle sera terminée il y aura retour à la macro Mac1 qui continura de se dérouler.
Le retour se fait bien évidemment à la ligne de code de Mac1 située juste après la ligne de code faisant appel à Mac2.

Une fois que Mac2 est au point (tu sais exactement ce qu'elle fait et tu sais qu'elle le fait bien), tu n'as plus à t'en soucier et dans le reste de ton code il te suffit d'appeler cette macro Mac2 pour qu'elle s'exécute.
Ainsi tout le travail effectué par Mac2 est remplacé dans le reste du code par une seule instruction : l'instruction faisant appel à cette macro Mac2.


Si tu n'as pas compris le principe, ce n'est pas grave car je pense qu'on peut en fait s'en passer ici...



[edit]
Mais en fait tu devais l'avoir bien compris depuis longtemps puisque c'est ce que tu fais quand tu fais appel à la macro Macro1.
Mais bon, un peu d'instruction ne fait jamais de mal.
[/edit]

 

[TooFatBoy]

Re : Comment changer des noms de feuilles dans une macro

On doit en fait dans ton cas pouvoir tout mettre dans la même macro si tu préfères.

En effet, si cette macro Macro1 (de création et de remplissage d'une feuille en fonction du nom d'un employé) n'est appelé qu'une seule fois dans ton programme, alors le balayage de la colonne B (de B2 à B31) peut être effectué dans Macro1.

Pour ce balayage, il faudrait faire un truc du genre :
for each B in range("B2:B31")
...
next

 

[PFRANCO]

Re : Comment changer des noms de feuilles dans une macro

Bonsoir Marcel 32

Sur le principe, j'ai compris. En fait , imaginons que j'ai 3 macros identiques pour créer trois feuilles differentes. Je peux dans la macro 1 appeler la macro 2 ect.. Ok Ca je comprends.

Question 1: Comment faire, a la fin de la macro 1 pour appeler la macro2 (ecriture de code).
Question 2: comment faire pour utiliser "For Each" etc.. car j'ai déjà vu cela pour interdir la double saisie dans une cellule à partir d'une liste nommée.

If [Nb_enregistrements_Bd] > 0 Then
For Each cell In Range("employés")
If cell.Value = [consult_nom_employé] Then Exit Sub
Next
End If

A plus tard si tu veux et merci pour ta simplicité.

Philippe.

 

[TooFatBoy]

Re : Comment changer des noms de feuilles dans une macro

Salut,

Je n'ai pas le temps de me pencher sérieusement sur ton fichier et j'en suis fort désolé.

Bon, on va essayer de tout mettre dans la même macro en fait, ça évitera de faire un appel à une autre macro avec passage de paramètre...

Essaye un truc du genre (à dégugguer surement ) :

Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 22/06/2008 par PH CONSULTANTS
'
Dim Nom As String

    Application.ScreenUpdating = False
    ActiveSheet.Unprotect

    For Each Cell in Range("employés")
        Nom = Cell.Value
        If Nom <> "" Then
            Sheets.Add
            ActiveSheet.Name = Nom
            Sheets("listing").Shapes("Button 3").Copy
            ActiveSheet.Paste
            Sheets("listing").Select
            Range("A5").Select
            Sheets(Nom).Range("E4").Value = Nom
            Sheets("REPARTITION CHAMBRES").Select
            Rows("7:7").Select
            Selection.AutoFilter
            Selection.AutoFilter Field:=5, Criteria1:=(Nom)
            Range("E7:AP7").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Sheets(Nom).Select
            Range("A7").Select
            ActiveSheet.Paste

            Range("F2").Value = "FICHE JOURNALIERE INDIVIDUELLE DE TRAVAIL"
            With Range("F2:K3").Font
                .Name = "Arial"
                .Size = 12
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
            Range("G5").Value = Range("E4")
            Range("F2:K3,G5:J5").Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection
                .Font.Bold = True
                .MergeCells = True
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
            End With

            Range("M2").Value = "Nb total de chambres"
            Range("M4").Value = "Durée totale de travail"
            With Range("M2:N2,M4:N4")
                .MergeCells = True
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .HorizontalAlignment = xlRight
                .VerticalAlignment = xlBottom
            End With

            Range("O2").FormulaR1C1 = "=COUNTA(C[-13])-1"
            Range("O4").FormulaR1C1 = "=SUM(R[4]C:R[8]C)"
            Range("O2,O4").Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection
                .MergeCells = False
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
                .Font.Bold = True
            End With

            Range("E4").Font.ColorIndex = 2
            Range("A7").Select

            Sheets("REPARTITION CHAMBRES").Select
            Selection.AutoFilter
            Range("E7").Select
        End If
    Next Cell

End Sub

Il faudrait peut-être ajouter un test au cas où la feuille existe déjà, mais comme je ne sais pas exactement comment fonctionne le reste du programme, ce n'est pas évident de savoir si c'est nécessaire ou pas.

 

[PFRANCO]

Re : Comment changer des noms de feuilles dans une macro

Boujour à tous les forumeurs,

Tout d'abord merci MARCEL32. Tes explications m'ont permis de comprendre comment introduire une variable dans une macro pour ne pas être obligé d'écrire des dizaines de macros.

Tout fonctionne bien sauf un truc.

Rappel contexte:
Je saisis des noms dans la colonne d'une feuille "REPARTITION CHAMBRES".
J'ai une liste de noms dans une feuille nommée "Base".
La macro va chercher chaque nom de la liste "Base" pour y appliquer un code qui permet de créer une nouvelle feuille nommée avec chaque nom.

Lorsque je ne saisis que deux ou trois noms dans ma feuille "REPARTITION CHAMBRES", ca plante et le message Excel dit: la fonction ActiveSheet.Paste, n'a pas pu s'effectuer.

As tu une explication à ce phénomène?

Merci.

Philippe.


Voir macro suivante:

Dim Nom As String

Application.ScreenUpdating = False
ActiveSheet.Unprotect
If MsgBox("Avant d'enregistrer les fiches individuelles, VERIFIEZ bien que vous avez enregistré au moins 4 noms différents dans le planning", vbYesNo, "RH. CONSEILS & INGENIERIE") = vbYes Then
For Each Cell In Range("nom")
Nom = Cell.Value
If Nom <> "" Then
Sheets.Add
ActiveSheet.Name = Nom
Sheets("listing").Shapes("Button 3").Copy
ActiveSheet.Paste
Sheets("listing").Select
Range("A5").Select
Sheets(Nom).Range("E4").Value = Nom
Sheets("REPARTITION CHAMBRES").Select
Rows("7:7").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=(Nom)
Range("E7:AP7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(Nom).Select
Range("A7").Select
ActiveSheet.Paste Range("F2").Value = "FICHE JOURNALIERE INDIVIDUELLE DE TRAVAIL"
With Range("F2:K3").Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("G5").Value = Range("E4")
Range("F2:K3,G5:J5").Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection
.Font.Bold = True
.MergeCells = True
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("M2").Value = "Nb total de chambres"
Range("M4").Value = "Durée totale de travail"
With Range("M2:N2,M4:N4")
.MergeCells = True
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
End With
Range("O2").FormulaR1C1 = "=COUNTA(C[-13])-1"
Range("O4").FormulaR1C1 = "=SUM(R[4]C:R[8]C)"
Range("O2,O4").Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection
.MergeCells = False
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Bold = True
End With
Range("E4").Font.ColorIndex = 2
Sheets("REPARTITION CHAMBRES").Select
Selection.AutoFilter
ActiveSheet.Shapes("Oval 314").Select
Selection.ShapeRange.Fill.Visible = msoFalse
ActiveSheet.Shapes("Oval 319").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Range("E7").Select
End If
Next Cell
End If
End Sub