Modification de référence lors d'un copié collé

Question

J'ai besoin de votre aide. Je ne suis pas sûre des termes que j'emplois mais je vais essayer d'être compréhensible... J'ai un fichier qui comporte plusieurs référence d'une feuille à l'autre ou d'un fichier à un autre et je voudrais savoir comment lorsque que je crée une nouvelle feuille je pourrais copier la feuille précédente en modifiant la page de référence. Par exemple: Quand je suis sur la deuxième feuille ma formule est la suivante :=NB.SI(F4:AG4;"VISITE")+ Feuil1 !AL4 et je voudrais la copier sur la troisième feuille en changeant la référence de page automatiquement pour devenir : =NB.SI(F4:AG4;"VISITE")+ Feuil2 !AL4 et pour l'autre chose Entre deux fichier liés, même chose changer la feuille de référence automatiquement La feuille 1 du premier fichier est lié à la feuille 1 du deuxième fichier Exemple ='&#91;POSTE DE TRAVAIL 2022.xlw&#93; Feuil1 '!$G91:$H91 Pour devenir ='&#91;POSTE DE TRAVAIL 2022.xlw&#93; Feuil2 '!$G91:$H91 j'ai énormément de formule d'où ma détresse Je vous joins mes deux fichier. Poste de travail est normalement en format "workspace" mais je n'ai pas pu le partager sous ce format. j'espère avoir été clair Merci...

Eric Kergresse · Accepted Answer

J'ai pris l'exemple sur la cellule G10 : =@INDIRECT("'&#91;"&$I$1&"&#93;"&$L$1&"'!$G97:$H97") Attention à la position des ' dans la formule...

Eric Kergresse · Answer

Il vous faut utiliser la fonction indirect. Le principe est de noter dans une cellule (I1) le nom de l'onglet en référence (Feuil1) et de modifier votre formule comme dans la barre de formule de la capture d'écran ci-dessous sur Feuil2 : Pour plus de documentation sur la fonction indirect, regardez son utilisation chez Jacques Boisgontier : Site de Jacques BOISGONTIER Le soucis c'est la quantité de formules à modifier, car avec les "", la formule n'est pas duplicable avec un glisser de la souris. Des macros seront nécessaires...

Eric Kergresse · Answer

Conseil : Faites vos essais sur une copie de votre Feuil2

Un modèle de macro pour modifier votre formule en colonne AL

Sub ModifierLaColonneAL()

Dim I As Integer
Dim AireAModifier As Range
Dim Chaine As String

    With Application
         .ScreenUpdating = False
         .Calculation = xlCalculationManual
    End With

    With Sheets("Feuil2 (2)") ' Copie de l'onglet Feuil2
         Set AireAModifier = .Range(.Cells(4, "AL"), .Cells(89, "AL"))

         For I = 1 To AireAModifier.Count
             With AireAModifier(I)

                 .Formula2R1C1 = "=COUNTIF(RC[-32]:RC[-5],""VISITE"")+INDIRECT(R1C9&""!AL""&ROW(RC))"

             End With
         Next I
         Set AireAModifier = Nothing
    End With

    With Application
         .ScreenUpdating = True
         .Calculation = xlCalculationAutomatic
    End With

End Sub

Pour les autres colonnes :

MPS :

Formule existante :

=COUNTIF(RC[-33]:RC[-6],"MPS 0-4")+COUNTIF(RC[-33]:RC[-6],"MPS 5-9")+COUNTIF(RC[-33]:RC[-6],"MPS 0-4-")+COUNTIF(RC[-33]:RC[-6],"MPS 5-9-")+Feuil1!RC

La ligne de code associée :

Pour l'aire :

Set AireAModifier = .Range(.Cells(4, "AM"), .Cells(89, "AM"))

 =COUNTIF(RC[-33]:RC[-6],"MPS 0-4")+COUNTIF(RC[-33]:RC[-6],"MPS 5-9")+COUNTIF(RC[-33]:RC[-6],"MPS 0-4-")+COUNTIF(RC[-33]:RC[-6],"MPS 5-9-")+INDIRECT(R1C9&""!AM""&ROW(RC))"

Pour connaître la formule existante dans vote tableau :

Placez-vous en cellule AN4 de Feuil2 par exemple.

Basculez sur l'éditeur VBA Alt-F11

Si la fenêtre Exécution n'est pas ouverte, ouvrez là avec Ctrl-G

Dans la fenêtre, tapez

? activecell.Formula2r1c1

Dans la macro, c'est cette formule qu'il faut modifier en remplaçant

+Feuil1!RC

par la formule ci-dessous en modifiant le numéro de colonne, ici AN et en modifiant à chaque fois la colonne de l'aire à modifier

+INDIRECT(R1C9&""!AN""&ROW(RC))"

Set AireAModifier = .Range(.Cells(4, "AN"), .Cells(89, "AN"))

Eric Kergresse · Answer

L'exemple sur le site de Jacques BOISGONTIER ...