[RESOLU] VBA EXCEL - Problème d'exécution de boucle et collage valeur formule

[Minh]

Bonjour,


Je débute en VBA et ai un niveau excel intermédiaire. Je bute sur un problème qui me dépasse (je ne le trouve pas logique à mon goût, mais c'est sans doute parce que je ne suis pas encore un initié).

J'espère que vous pourrez m'apporter quelques explications, et je souhaite remercier ceux qui prendront le temps de lire mon problème ainsi que ceux qui apporteront leur contribution pour éclairer ma lanterne


--------------

Je souhaite créer une macro qui me permettent d'exécuter des taches identiques sur l'ensemble des feuilles du classeur excel actif (copier/coller/effacer). Pour cela, j'ai bien compris qu'une "boucle" serait utile. En plus, comme je possède des fichiers similaires mais avec un nombre de feuilles différentes, je dois être le plus "général" possible.

Si vous le pouvez, téléchargez le fichier excel pour mieux visualiser mon problème.

Il faut que je copie le solde du 10/09/2012 et le colle en valeur (car la cellule copiée contient une formule) à la place du solde du 07/09/2012. Et cela sur toutes les feuilles du classeur (même structure).

Méthode 1 : en passant par l'enregistreur (pour bien comprendre ce qu'il se passe)

Range("L3").Select
Selection.Copy
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("SOLDE 2").Select
Range("L3").Select
Application.CutCopyMode = False
Selection.Copy
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("SOLDE 3").Select
Range("L3").Select
Application.CutCopyMode = False
Selection.Copy
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

en l'exécutant, ok elle fonctionne, elle fait "bien" les choses. Sauf que l'on voit que si le nom des feuilles change, la macro n'est plus utilisable.

D'où la boucle. J'ai trouvé une macro qui contient justement ce principe et qui a pour but de répéter une action sur l'ensemble des feuilles du classeur. Celle-ci fait juste un copier collage valeur de l'ensemble de la feuille, puis passe à la suivante (enfin c'est ce que j'ai compris). Elle est accompagné de quelques astuces pour faire les choses "propre".

Sub CopColVal_Classeur()
Dim Feuille As Worksheet, Mémo As Boolean, FeuilleRef As Worksheet

Application.ScreenUpdating = False

' mémorisation de la feuille active
Set FeuilleRef = ActiveSheet

' parcours des feuilles de travail
For Each Feuille In ActiveWorkbook.Worksheets
With Feuille

' cas d'une feuille masquée
If .Visible = False Then
Mémo = True ' mémorisation
.Visible = True ' affichage
End If

' coeur de la macro
.Select
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
.Range("A1").Select

' cas d'une feuille qui était masquée au début
If Mémo = True Then
.Visible = False ' re-masquage
Mémo = False ' neutralisation
End If

End With
Next Feuille

' réactivation de la feuille active initiale
FeuilleRef.Activate

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Avec ma logique, je me suis dit que si je remplaçai la partie "coeur de la macro" par ce que j'ai vu en enregistrement, j'obtiendrai ce que je veux.

Méthode 2 :

Sub CopColVal_Classeur2()
Dim Feuille As Worksheet, Mémo As Boolean, FeuilleRef As Worksheet

Application.ScreenUpdating = False

' mémorisation de la feuille active
Set FeuilleRef = ActiveSheet

' parcours des feuilles de travail
For Each Feuille In ActiveWorkbook.Worksheets
With Feuille

' cas d'une feuille masquée
If .Visible = False Then
Mémo = True ' mémorisation
.Visible = True ' affichage
End If

' coeur de la macro
Range("L3").Select
Selection.Copy
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' cas d'une feuille qui était masquée au début
If Mémo = True Then
.Visible = False ' re-masquage
Mémo = False ' neutralisation
End If

End With
Next Feuille

' réactivation de la feuille active initiale
FeuilleRef.Activate

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Et bien non. En exécutant la macro, celle-ci :
1- ne travaille que sur la feuille active
2- tourne peut être plusieurs fois pour le copier/collage valeur, car elle ne me donne pas le montant attendu en L1 (1100 pour la première feuille - 1200 pour la deuxième - 1300 pour la troisième).


Je me dis que le problème peut venir du fait que la formule du solde du 10/09/2010 (qui reprend le solde du 07/09/2012) et est : =L1+SOMME(L6:L100)-SOMME(K6:K100)

Pourtant, la VBA obtenu grâce à l'enregistrement n'a rien de spécial. Et il me faut juste un copier/coller une fois sur la feuille puis passer à la suivante...


Bref, je suis perdu

Merci d'avoir pris la peine de lire mon problème, en espérant que c'est intelligible. Je suis tout ouïe de vos commentaires.


Bien cordialement

 

[frangy]

Re : VBA EXCEL - Problème d'exécution de boucle et collage valeur formule

Bonjour,

Dans ta boucle For...Next, ce qui permet de faire référence à la feuille concernée par le traitement est l’instruction With.
Dans la partie de la procédure encadrée par With Feuille... End With, on indique que les instructions sont appliquées à l’objet Feuille en plaçant un point en début d’instruction.
Exemple : .Range("L3").Copy est équivalent à Feuille.Range("L3").Copy.

Sub CopColVal_Classeur2()
Dim Feuille As Worksheet, Mémo As Boolean, FeuilleRef As Worksheet
    Application.ScreenUpdating = False
    ' mémorisation de la feuille active
    Set FeuilleRef = ActiveSheet
    ' parcours des feuilles de travail
    For Each Feuille In ActiveWorkbook.Worksheets
        With Feuille
            ' cas d'une feuille masquée
            If .Visible = False Then
                Mémo = True ' mémorisation
                .Visible = True ' affichage
            End If
            ' coeur de la macro
            .Activate
            .Range("L3").Copy
            .Range("L1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            ' cas d'une feuille qui était masquée au début
            If Mémo = True Then
                .Visible = False ' re-masquage
                Mémo = False ' neutralisation
            End If
        End With
    Next Feuille
    ' réactivation de la feuille active initiale
    FeuilleRef.Activate
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Cordialement.

 

[Minh]

Re : VBA EXCEL - Problème d'exécution de boucle et collage valeur formule

merci beaucoup Frangy

Et merci pour l'explication clair. J'avais dû essayer au cours de mes recherches avec le ".", mais j'avais un bug et je pense que c'est parce qu'il devait manquer le ".Select" ou ".Activate" (à noter que j'ai recherché la différence qui existait entre les deux). J'ai encore beaucoup à apprendre.

J'ai honte, tellement j'ai l'impression que c'était simple à résoudre ...


En tout cas merci et bonne soirée


Bien cordialement