Discussion :

[Esculape]

Bonjour le Forum!


Je développe actuellement un bout de code pour réaliser une action sur plusieurs feuilles: modifier certaines plages en indiquant la valeur de la cellule à la place de la formule.
D'autres feuilles ne doivent, elles, pas être modifiées (celles-ci sont connues et leur nom restera figé).

Puisque je veux parer l'ajout de nouveaux onglets dont je ne connaitrai pas le nom, j'ai tenté de mettre en place une solution dynamique qui compte les feuilles, puis applique l'action que je souhaite sur ces dernières tout en évitant cette action sur celles qui ne doivent pas être modifiées.

Concrètement:
- Un ensemble de feuilles connues ne doit pas être modifié,
- Un ensemble variable de feuilles de nom inconnu doit être modifié.


Voici mon code:

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
Sub Essai()
 
Dim myRange As Range, myRange1 As Range, myRange2 As Range, myRange3 As Range
Dim derniereLigne As Long
Dim i As Integer
 
 
derniereLigne = Range("E7").End(xlDown).Row
 
 
 
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "Data" Or Worksheets(i).Name = "Budget_2015" _
        Or Worksheets(i).Name = "Budget_2016" Or Worksheets(i).Name = "Budget_2017" _
        Or Worksheets(i).Name = "Liste des centres" Or Worksheets(i).Name = "CA_Moyens" Then
        Else
 
 
'Ici je peux voir que le nom de chaque apparait bien par des MsgBox successifs, mais l'action suivante ne fonctionne pas
        MsgBox Worksheets(i).Name
 
            With Worksheets(i).Name
 
                Set myRange = Range(Cells(10, 3), Cells(derniereLigne, 10))
                Set myRange1 = Range(Cells(10, 14), Cells(derniereLigne, 15))
                Set myRange2 = Range(Cells(10, 19), Cells(derniereLigne, 20))
                Set myRange3 = Range(Cells(10, 22), Cells(derniereLigne, 25))
 
                myRange.Value = myRange.Value
                myRange1.Value = myRange1.Value
                myRange2.Value = myRange2.Value
                myRange3.Value = myRange3.Value
 
            End With
        End If
    Next i
 
End Sub

L'action s'effectue bien si je me place sur la feuille en question, mais pas d'action sur les autres en revanche.
J'imagine qu'il doit y avoir un lien avec le With, mais je ne vois pas trop pourquoi ni comment...


Merci d'avance pour votre aide ou vos axes de réflexions !


Bonne journée,


Esculape.

[Kiouane]

Je ne sais pas si c'est ça mais je crois que tu dois enlever le .Name après With Worksheet(i).

[Esculape]

Hello Kiouane!

Non ça ne marche pas mieux, désolé.

[Kiouane]

Si j'ai bien compris, tu veux remplacer les formules dans les cellules avec les valeurs retournées par ces même formules? Si c'est ça il faut passer par un collage spéciale.

[Invité]

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
Sub Essai()
Dim myRange As Range, myRange1 As Range, myRange2 As Range, myRange3 As Range, S As Worksheet
Dim derniereLigne As Long
Dim i As Integer
For Each S In Worksheets
    If S.Name = "Data" Or S.Name = "Budget_2015" _
        Or S.Name = "Budget_2016" Or S.Name = "Budget_2017" _
        Or S.Name = "Liste des centres" Or S.Name = "CA_Moyens" Then
    Else
        'Ici je peux voir que le nom de chaque apparait bien par des MsgBox successifs, mais l'action suivante ne fonctionne pas
        MsgBox S.Name
        With S
             derniereLigne = .Range("E7").End(xlDown).Row
            Set myRange = .Range(.Cells(10, 3), .Cells(derniereLigne, 10))
            Set myRange1 = .Range(.Cells(10, 14), .Cells(derniereLigne, 15))
            Set myRange2 = .Range(.Cells(10, 19), .Cells(derniereLigne, 20))
            Set myRange3 = .Range(.Cells(10, 22), .Cells(derniereLigne, 25))
            myRange.Value = myRange.Value
            myRange1.Value = myRange1.Value
            myRange2.Value = myRange2.Value
            myRange3.Value = myRange3.Value
        End With
    End If
Next
End Sub

[Esculape]

Kiouane: oui c'est ça!
J'ai déjà essayé le collage spécial mais je ne trouve pas cela optimal.

Une simple transposition Value = Value marche très bien, mais c'était mon With qui posait réellement problème.


rdurupt: Merci, ça marche!
Du coup il faut attribuer directement une variable à l'objet Worksheet si j'ai bien tout saisi?

Ca rejoint ce que tu disait finalement Kiouane.


Merci pour votre aide précieuse !

A bientôt.

[MacKay77]

Bonjour,

Sauf erreur de ma part, ton erreur venait bien du with qui n'était pas utilisé.
Il fallait rajouter un point devant les range et cells pour les relier au worksheet définit par le with.

Bonne journée

[Invité]

l'erreur venait de la ligne 22 With Worksheets(i).Name qui pour le coup n'est pas un objet mais un string!

plus Set myRange = Range(Cells(10, 3), Cells(derniereLigne, 10)) qui fait référence à la feuille active et pas au With , oui il fallait également [.]

For Each S In Worksheets c'est plus propre et ça référence la feuille sur un objet!

Code :

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

1
2
derniereLigne = Range("E7").End(xlDown).Row
     For i = 1 To Worksheets.Count

Faisait toujours référencer à la feuille active!
je fais référencer à la feuille de la boucle For Each S In Worksheets

Code :

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

1
2
With S
             derniereLigne = .Range("E7").End(xlDown).Row

With permet d’énumérer toutes les propriétés et méthode d'un objet sens devoir réécrire toute sa déclinaison!

[Esculape]

Super!

Merci beaucoup pour ces réponses rdurupt. Petit à petit je commence à mieux cerner les subtilités de VBA!
Merci aussi MacKay77!

C'est toujours un plaisir d'apprendre sur developpez!

A bientôt le forum .

Esculape.