Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel Remplacer 70 000 formules par leur avleur

Remplacer 70 000 formules par leur avleur

5 réponses
Avatar
Patrick BASTARD
Bonsoir à toutes et tous.

Je reformule ma question initiée le 12 juin, "La méthode GoTo a échoué" et
pour laquelle, malgré les efforts d'Isabelle, que je remercie au passage, je
n'ai pas encore de solution.

Je joins un exemple qui sera plus parlant qu'une explication hasardeuse :

http://cjoint.com/?goxx7Mwujk

Je souhaite remplacer les formules des feuilles de ce classeur (Lignes 4 à
40, fixes, colonnes P à dernière utilisée) par leur valeur lorsque je
sélectionne une autre feuille,

et recopier les formules de la ligne 3 sur les Lignes 4 à 40, fixes,
colonnes P à dernière utilisée) en écrasant leur valeur lorsque j'active
cette feuille.

Le but de la manip :
Alléger le classeur, car 114 formules*50 salariés*30 feuilles, cela fait 170
000 formules... pour en fin de compte que 3 formules différentes (P3,Q3 et
R3) (ou A3, B3 et C3).

Toutes les idées et toutes les remarques sont les bienvenues,
j'avais pensé à effectuer un copier-collage spécial, valeurs à partir de P4,
lors de la désactivation de la feuille, et une recopie vers le bas de la
zone P3:xx3 jusqu'à la ligne 40 lors de la sélection de la feuille, mais,
même avec l'aide d'Isabelle, cela ne fonctionne pas.
Une boucle me conviendrait également ( copier les formules de A3:C3 sur
chacun des salariés sur chacune des 38 lignes), mais atteins ici mon seuil
d'incompétence...

D'avance, merci à ceux qui voudront bien m'aiguiller.

--
Bien amicordialement,
P. Bastard
Signaler un problème avec ce contenu

5 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
docm
Bonjour Patrick.


Avec la variable NePasExecuterSi afin d'éviter que les opérations ne se
répètent inutilement.
Préférer ici Me.Name à ActiveSheet.Name.

Option Explicit

Private Sub Worksheet_Activate()
'Recopie les formules sur les valeurs
If NePasExecuterSi = Me.Name Then Exit Sub
If NePasExecuterSi <> "" Then
Dim NomDeFeuille As String

NomDeFeuille = Me.Name 'ActiveSheet.Name
Sheets(NomDeFeuille).Range("RecopieFormules").FillDown
Debug.Print "FillDown"
End If

End Sub
Private Sub Worksheet_Deactivate()
'Remplace les formules par les valeurs quand on quitte
'la feuille, uniquement si les feuilles du mois sont créées
If NePasExecuterSi <> "" Then Exit Sub
Dim NomDeFeuille As String

NomDeFeuille = Me.Name 'ActiveSheet.Name
NePasExecuterSi = Me.Name
Debug.Print "------------------"
If Sheets.Count > 1 Then
Sheets(NomDeFeuille).Range("RecopieValeurs").Copy
Sheets(NomDeFeuille).Range("RecopieValeurs").Item(1).PasteSpecial
Paste:=xlPasteValues
Debug.Print "Paste special"
Application.CutCopyMode = False
End If
NePasExecuterSi = ""

End Sub


docm

"Patrick BASTARD" wrote in message
news:OLCkvn$
Bonsoir à toutes et tous.

Je reformule ma question initiée le 12 juin, "La méthode GoTo a échoué" et
pour laquelle, malgré les efforts d'Isabelle, que je remercie au passage,
je

n'ai pas encore de solution.

Je joins un exemple qui sera plus parlant qu'une explication hasardeuse :

http://cjoint.com/?goxx7Mwujk

Je souhaite remplacer les formules des feuilles de ce classeur (Lignes 4 à
40, fixes, colonnes P à dernière utilisée) par leur valeur lorsque je
sélectionne une autre feuille,

et recopier les formules de la ligne 3 sur les Lignes 4 à 40, fixes,
colonnes P à dernière utilisée) en écrasant leur valeur lorsque j'active
cette feuille.

Le but de la manip :
Alléger le classeur, car 114 formules*50 salariés*30 feuilles, cela fait
170

000 formules... pour en fin de compte que 3 formules différentes (P3,Q3 et
R3) (ou A3, B3 et C3).

Toutes les idées et toutes les remarques sont les bienvenues,
j'avais pensé à effectuer un copier-collage spécial, valeurs à partir de
P4,

lors de la désactivation de la feuille, et une recopie vers le bas de la
zone P3:xx3 jusqu'à la ligne 40 lors de la sélection de la feuille, mais,
même avec l'aide d'Isabelle, cela ne fonctionne pas.
Une boucle me conviendrait également ( copier les formules de A3:C3 sur
chacun des salariés sur chacune des 38 lignes), mais atteins ici mon seuil
d'incompétence...

D'avance, merci à ceux qui voudront bien m'aiguiller.

--
Bien amicordialement,
P. Bastard




Avatar
Patrick BASTARD
Bonjour, *docm*

Je te remercie d'avoir bien voulu prendre mon soucis en compte.
Je teste et reviens te dire.

Bien cordialement,

Patrick.


Bonjour Patrick.


Avec la variable NePasExecuterSi afin d'éviter que les opérations ne
se répètent inutilement.
Préférer ici Me.Name à ActiveSheet.Name.

Option Explicit

Private Sub Worksheet_Activate()
'Recopie les formules sur les valeurs
If NePasExecuterSi = Me.Name Then Exit Sub
If NePasExecuterSi <> "" Then
Dim NomDeFeuille As String

NomDeFeuille = Me.Name 'ActiveSheet.Name
Sheets(NomDeFeuille).Range("RecopieFormules").FillDown
Debug.Print "FillDown"
End If

End Sub
Private Sub Worksheet_Deactivate()
'Remplace les formules par les valeurs quand on quitte
'la feuille, uniquement si les feuilles du mois sont créées
If NePasExecuterSi <> "" Then Exit Sub
Dim NomDeFeuille As String

NomDeFeuille = Me.Name 'ActiveSheet.Name
NePasExecuterSi = Me.Name
Debug.Print "------------------"
If Sheets.Count > 1 Then
Sheets(NomDeFeuille).Range("RecopieValeurs").Copy

Sheets(NomDeFeuille).Range("RecopieValeurs").Item(1).PasteSpecial
Paste:=xlPasteValues Debug.Print "Paste special"
Application.CutCopyMode = False
End If
NePasExecuterSi = ""

End Sub


docm

"Patrick BASTARD" wrote in
message news:OLCkvn$
Bonsoir à toutes et tous.

Je reformule ma question initiée le 12 juin, "La méthode GoTo a
échoué" et pour laquelle, malgré les efforts d'Isabelle, que je
remercie au passage, je n'ai pas encore de solution.

Je joins un exemple qui sera plus parlant qu'une explication
hasardeuse :

http://cjoint.com/?goxx7Mwujk

Je souhaite remplacer les formules des feuilles de ce classeur
(Lignes 4 à 40, fixes, colonnes P à dernière utilisée) par leur
valeur lorsque je sélectionne une autre feuille,

et recopier les formules de la ligne 3 sur les Lignes 4 à 40, fixes,
colonnes P à dernière utilisée) en écrasant leur valeur lorsque
j'active cette feuille.

Le but de la manip :
Alléger le classeur, car 114 formules*50 salariés*30 feuilles, cela
fait 170 000 formules... pour en fin de compte que 3 formules
différentes (P3,Q3 et R3) (ou A3, B3 et C3).

Toutes les idées et toutes les remarques sont les bienvenues,
j'avais pensé à effectuer un copier-collage spécial, valeurs à
partir de P4, lors de la désactivation de la feuille, et une recopie
vers le bas de la zone P3:xx3 jusqu'à la ligne 40 lors de la
sélection de la feuille, mais, même avec l'aide d'Isabelle, cela ne
fonctionne pas.
Une boucle me conviendrait également ( copier les formules de A3:C3
sur chacun des salariés sur chacune des 38 lignes), mais atteins ici
mon seuil d'incompétence...

D'avance, merci à ceux qui voudront bien m'aiguiller.

--
Bien amicordialement,
P. Bastard




Avatar
Patrick BASTARD
Bonjour, *docm*

Je reviens pour te dire que ton code fonctionne, et je t'en remercie

Deux questions encore, si ce n'est pas abuser de ton temps :

********************
Private Sub Worksheet_Deactivate()
'Remplace les formules par les valeurs quand on quitte
'la feuille, uniquement si les feuilles du mois sont créées
Dim NomDeFeuille As String
NomDeFeuille = Me.Name 'ActiveSheet.Name
Sheets(NomDeFeuille).Range("RecopieValeurs").Copy "RecopieValeurs"
="P4:DP40"
Sheets(NomDeFeuille).Range("RecopieValeurs").Item(1).PasteSpecial
Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
********************

1° -
Ce code, sur 105 colonne et 37 lignes, est très long à s'exécuter, et les
cellules clignotent.
Application.ScreenUpdating = False et Application.Calculation = xlManual
n'y font rien (ou pas grand chose), alors que :
**********
Sub recopievaleur()
Range("P4:DP40").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
End Sub
**********
est réalisé instantanément.
Sais-tu pourquoi ?

2° -
- Je n'ai pas compris le debug.print, et l'aide est pour le moins laconique.
- A quoi sert le test If NePasExécuterSi ? Me.Name peut-il être vide ?
Pour info, j'ai ôté les références à cette variable, car je ne savais pas
comment la déclarer, et mon OptionExplicit me criait des injures.

Encore merci pour ton aide,

Bien cordialement,
Patrick.

Bonjour Patrick.


Avec la variable NePasExecuterSi afin d'éviter que les opérations ne
se répètent inutilement.
Préférer ici Me.Name à ActiveSheet.Name.

Option Explicit

Private Sub Worksheet_Activate()
'Recopie les formules sur les valeurs
If NePasExecuterSi = Me.Name Then Exit Sub
If NePasExecuterSi <> "" Then
Dim NomDeFeuille As String

NomDeFeuille = Me.Name 'ActiveSheet.Name
Sheets(NomDeFeuille).Range("RecopieFormules").FillDown
Debug.Print "FillDown"
End If

End Sub
Private Sub Worksheet_Deactivate()
'Remplace les formules par les valeurs quand on quitte
'la feuille, uniquement si les feuilles du mois sont créées
If NePasExecuterSi <> "" Then Exit Sub
Dim NomDeFeuille As String

NomDeFeuille = Me.Name 'ActiveSheet.Name
NePasExecuterSi = Me.Name
Debug.Print "------------------"
If Sheets.Count > 1 Then
Sheets(NomDeFeuille).Range("RecopieValeurs").Copy

Sheets(NomDeFeuille).Range("RecopieValeurs").Item(1).PasteSpecial
Paste:=xlPasteValues Debug.Print "Paste special"
Application.CutCopyMode = False
End If
NePasExecuterSi = ""

End Sub


docm

"Patrick BASTARD" wrote in
message news:OLCkvn$
Bonsoir à toutes et tous.

Je reformule ma question initiée le 12 juin, "La méthode GoTo a
échoué" et pour laquelle, malgré les efforts d'Isabelle, que je
remercie au passage, je n'ai pas encore de solution.

Je joins un exemple qui sera plus parlant qu'une explication
hasardeuse :

http://cjoint.com/?goxx7Mwujk

Je souhaite remplacer les formules des feuilles de ce classeur
(Lignes 4 à 40, fixes, colonnes P à dernière utilisée) par leur
valeur lorsque je sélectionne une autre feuille,

et recopier les formules de la ligne 3 sur les Lignes 4 à 40, fixes,
colonnes P à dernière utilisée) en écrasant leur valeur lorsque
j'active cette feuille.

Le but de la manip :
Alléger le classeur, car 114 formules*50 salariés*30 feuilles, cela
fait 170 000 formules... pour en fin de compte que 3 formules
différentes (P3,Q3 et R3) (ou A3, B3 et C3).

Toutes les idées et toutes les remarques sont les bienvenues,
j'avais pensé à effectuer un copier-collage spécial, valeurs à
partir de P4, lors de la désactivation de la feuille, et une recopie
vers le bas de la zone P3:xx3 jusqu'à la ligne 40 lors de la
sélection de la feuille, mais, même avec l'aide d'Isabelle, cela ne
fonctionne pas.
Une boucle me conviendrait également ( copier les formules de A3:C3
sur chacun des salariés sur chacune des 38 lignes), mais atteins ici
mon seuil d'incompétence...

D'avance, merci à ceux qui voudront bien m'aiguiller.

--
Bien amicordialement,
P. Bastard




Avatar
docm
C'est long parce qu'il manque la commande
Application.EnableEvents = False
avant la commande PasteSpecial ce qui provoque une longue suite d'événements
Activate-Deactivate ( le code s'exécute 797 fois exactement sur mon
ordinateur ).

Voici ton code original où Activesheet.Name est remplacé par Me.Name et où
j'ai ajouté les commandes
Application.EnableEvents = False
et Application.EnableEvents = True


Option Explicit
Private Sub Worksheet_Activate()
'Recopie les formules sur les valeurs

Dim NomDeFeuille As String
NomDeFeuille = Me.Name
Sheets(NomDeFeuille).Range("RecopieFormules").FillDown
End Sub
Private Sub Worksheet_Deactivate()
'Remplace les formules par les valeurs quand on quitte
'la feuille, uniquement si les feuilles du mois sont créées

Dim NomDeFeuille As String
NomDeFeuille = Me.Name
If Sheets.Count > 5 Then
Sheets(NomDeFeuille).Range("RecopieValeurs").Copy
Application.EnableEvents = False
Sheets(NomDeFeuille).Range("RecopieValeurs").Item(1).PasteSpecial
Paste:=xlPasteValues
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End Sub



"Patrick BASTARD" wrote in message
news:
Bonjour, *docm*

Je reviens pour te dire que ton code fonctionne, et je t'en remercie

Deux questions encore, si ce n'est pas abuser de ton temps :

********************
Private Sub Worksheet_Deactivate()
'Remplace les formules par les valeurs quand on quitte
'la feuille, uniquement si les feuilles du mois sont créées
Dim NomDeFeuille As String
NomDeFeuille = Me.Name 'ActiveSheet.Name
Sheets(NomDeFeuille).Range("RecopieValeurs").Copy "RecopieValeurs"
="P4:DP40"
Sheets(NomDeFeuille).Range("RecopieValeurs").Item(1).PasteSpecial
Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
********************

1° -
Ce code, sur 105 colonne et 37 lignes, est très long à s'exécuter, et les
cellules clignotent.
Application.ScreenUpdating = False et Application.Calculation = xlManual
n'y font rien (ou pas grand chose), alors que :
**********
Sub recopievaleur()
Range("P4:DP40").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
End Sub
**********
est réalisé instantanément.
Sais-tu pourquoi ?


Voir explication ci-dessus au sujet des 797 événements.


2° -
- Je n'ai pas compris le debug.print, et l'aide est pour le moins
laconique.

- A quoi sert le test If NePasExécuterSi ?


La variable NePasExécuterSi ne sert plus à rien si on utilise plutôt la
commande Application.EnableEvents = False.

Me.Name peut-il être vide ?

Non car c'est le nom de la feuille.

Pour info, j'ai ôté les références à cette variable, car je ne savais pas
comment la déclarer, et mon OptionExplicit me criait des injures.

Encore merci pour ton aide,

Bien cordialement,
Patrick.

Bonjour Patrick.


Avec la variable NePasExecuterSi afin d'éviter que les opérations ne
se répètent inutilement.
Préférer ici Me.Name à ActiveSheet.Name.

Option Explicit

Private Sub Worksheet_Activate()
'Recopie les formules sur les valeurs
If NePasExecuterSi = Me.Name Then Exit Sub
If NePasExecuterSi <> "" Then
Dim NomDeFeuille As String

NomDeFeuille = Me.Name 'ActiveSheet.Name
Sheets(NomDeFeuille).Range("RecopieFormules").FillDown
Debug.Print "FillDown"
End If

End Sub
Private Sub Worksheet_Deactivate()
'Remplace les formules par les valeurs quand on quitte
'la feuille, uniquement si les feuilles du mois sont créées
If NePasExecuterSi <> "" Then Exit Sub
Dim NomDeFeuille As String

NomDeFeuille = Me.Name 'ActiveSheet.Name
NePasExecuterSi = Me.Name
Debug.Print "------------------"
If Sheets.Count > 1 Then
Sheets(NomDeFeuille).Range("RecopieValeurs").Copy

Sheets(NomDeFeuille).Range("RecopieValeurs").Item(1).PasteSpecial
Paste:=xlPasteValues Debug.Print "Paste special"
Application.CutCopyMode = False
End If
NePasExecuterSi = ""

End Sub


docm

"Patrick BASTARD" wrote in
message news:OLCkvn$
Bonsoir à toutes et tous.

Je reformule ma question initiée le 12 juin, "La méthode GoTo a
échoué" et pour laquelle, malgré les efforts d'Isabelle, que je
remercie au passage, je n'ai pas encore de solution.

Je joins un exemple qui sera plus parlant qu'une explication
hasardeuse :

http://cjoint.com/?goxx7Mwujk

Je souhaite remplacer les formules des feuilles de ce classeur
(Lignes 4 à 40, fixes, colonnes P à dernière utilisée) par leur
valeur lorsque je sélectionne une autre feuille,

et recopier les formules de la ligne 3 sur les Lignes 4 à 40, fixes,
colonnes P à dernière utilisée) en écrasant leur valeur lorsque
j'active cette feuille.

Le but de la manip :
Alléger le classeur, car 114 formules*50 salariés*30 feuilles, cela
fait 170 000 formules... pour en fin de compte que 3 formules
différentes (P3,Q3 et R3) (ou A3, B3 et C3).

Toutes les idées et toutes les remarques sont les bienvenues,
j'avais pensé à effectuer un copier-collage spécial, valeurs à
partir de P4, lors de la désactivation de la feuille, et une recopie
vers le bas de la zone P3:xx3 jusqu'à la ligne 40 lors de la
sélection de la feuille, mais, même avec l'aide d'Isabelle, cela ne
fonctionne pas.
Une boucle me conviendrait également ( copier les formules de A3:C3
sur chacun des salariés sur chacune des 38 lignes), mais atteins ici
mon seuil d'incompétence...

D'avance, merci à ceux qui voudront bien m'aiguiller.

--
Bien amicordialement,
P. Bastard








Avatar
Patrick BASTARD
Bonsoir, *docm*

Je te remercie infiniment.
Tout fonctionne, grâce à ton aide, comme je le souhaitais.

Bon week-end,

Bien cordialement,

Patrick.

C'est long parce qu'il manque la commande
Application.EnableEvents = False
avant la commande PasteSpecial ce qui provoque une longue suite
d'événements Activate-Deactivate ( le code s'exécute 797 fois
exactement sur mon ordinateur ).

Voici ton code original où Activesheet.Name est remplacé par Me.Name
et où j'ai ajouté les commandes
Application.EnableEvents = False
et Application.EnableEvents = True


Option Explicit
Private Sub Worksheet_Activate()
'Recopie les formules sur les valeurs

Dim NomDeFeuille As String
NomDeFeuille = Me.Name
Sheets(NomDeFeuille).Range("RecopieFormules").FillDown
End Sub
Private Sub Worksheet_Deactivate()
'Remplace les formules par les valeurs quand on quitte
'la feuille, uniquement si les feuilles du mois sont créées

Dim NomDeFeuille As String
NomDeFeuille = Me.Name
If Sheets.Count > 5 Then
Sheets(NomDeFeuille).Range("RecopieValeurs").Copy
Application.EnableEvents = False

Sheets(NomDeFeuille).Range("RecopieValeurs").Item(1).PasteSpecial
Paste:=xlPasteValues Application.CutCopyMode = False
Application.EnableEvents = True
End If
End Sub