Problème de référence formule et graphique lors de copie de feuilles excel pour réinitialiser données

mathi22 · 9 Juillet 2020

Bonjour,

Je vous explique ma situation:
J'ai un fichier excel avec plusieurs onglets contenant des données et des formules faisant intervenir les valeurs des différents feuilles entre elles. Je dois réaliser un bouton "Réinitialiser" pour que si un utilisateur remplace certaines valeurs par ses données (ce qui est le but du fichier), il puisse réinitialiser le fichier avec les valeurs de bases du fichier.
Pour ce faire, j'ai bien cherché sur internet et j'ai décidé de faire de la manière suivante.
J'ai fait une macro pour faire une copie de l'état initial des feuilles excel, puis de masquer ces feuilles "copies".
Voici la macro

VB:

Sub copier_feuilles()
  Application.DisplayAlerts = False
  'si la copie des feuilles n'a pas été faite, on la fait
    If Sheets(Sheets.Count).Visible = True Then
        For i = 1 To Sheets.Count   
            Sheets(i).Copy After:=Sheets(Sheets.Count)
        Next i
        'si la copie des feuilles a déja été faite, on supprime les feuilles copies pour refaire une copie à jour'
Else
    For i = 1 To Sheets.Count / 2
        Sheets(Sheets.Count).Delete
    Next i
    
    For i = 1 To Sheets.Count
        Sheets(i).Copy After:=Sheets(Sheets.Count)
        
    Next i
    
End If
  For i = 1 To Sheets.Count / 2
    Sheets(i + Sheets.Count / 2).Visible = False
  Next i
  Sheets(1).Activate
End Sub

Donc j'ai mes onglets, suivis des onglets "copie" masqués. Jusqu'ici, tout va bien: les formules des onglets copiés marchent toujours, les graphiques aussi.
le problème arrive lorsque je procède à la copie inverse, c'est à dire des feuilles initiales masquées vers les feuilles visible auxquelles l'utilisateur à accès.
1er cas:
Je supprime les onglets existants, puis je copie les feuilles que j'avais stockées et rend le résultat visible.

VB:

 Sub Reinitialiser()

    Dim nom_feuille As String
    
    Application.DisplayAlerts = False
    
    For i = 1 To Sheets.Count
        Sheets(i).Visible = True
    Next i
    For i = 1 To (Sheets.Count / 2)
    'je stocke le nom de l'onglet, fait une copie de l'onglet de réference correspondant, puis je supprime l'onglet modifié par l'utilisateur, et renomme l'onglet copié
        nom_feuille = Sheets(i).Name
        Sheets(i + (Sheets.Count / 2)).Copy After:=Sheets(i)
        Sheets(i).Delete
        Sheets(i).Name = nom_feuille
Next i
For i = 1 To Sheets.Count / 2
        Sheets(i + (Sheets.Count / 2)).Visible = False
    Next i

Sheets(1).Activate

End Sub

Problème: lorsque je supprime les onglets, les formules qui y faisaient référence ne marchent plus et j'ai des #REF! partout. Pour régler ceci, j'ai tenté plusieurs choses:
- copier les formules, changer le format de cellule en text avec une ligne de code genre: Selection.numberformat = '@' pour éviter que les formules perdent la référence, puis les recopier et les repasser en format normal: ca ne marche pas ( j'ai essayé avec plusieurs formats)
- j'ai tenté de remplacer les "=" des celles contenants des formules par des "n=" comme je l'ai vu sur des forums, pour que ce ne soit plus des formules et que le texte ne soit plus modifié: avec des macros je n'ai pas réussi, les "=" ne sont pas changés

J'ai donc changé de méthode : je n'ai plus supprimé les onglets actifs pour les remplacer, j'ai juste copié les cellules des onglets masqués contenant les valeurs initiales, vers les onglets actifs.
J'ai bien les formules ainsi que les chiffres, plus de problèmes de #REF! .

j'ai remplacé ma boucle For de copie de la macro précédente par le code suivant:

VB:

    For i = 1 To (Sheets.Count / 2)
        Sheets(i + (Sheets.Count / 2)).Cells.Copy Sheets(i).Cells
    Next i

Nouveau problème: mes graphiques ne marchent plus. alors qu'avec la méthode où je copiais l'onglet entier ils marchaient (quand je copiais l'onglet entier pour en faire une copie de référence et ensuite le masquer, sur les onglets masqués, les formules et les graphiques marchaient très bien. c'est quand je supprimais l'onglet modifié par l'utilisateur que je perdais mes références).
En effet, les graphiques deviennent tous de la forme suivante:
il perd en quelque sorte toutes ses données.

Du coup, si vous avez une idée ou une solution pour que je puisse faire un bouton pour réinitialiser les données de mon classeur (non pas les remettre à 0, mais remettre les valeurs d'origine, je précise bien), je en vous serais grandement reconnaissant.
Je vous remercie par avance,
Bonne journée

job75 · 9 Juillet 2020

Bonjour mathi22, bienvenue sur XLD,

Placez tout ce code dans un module standard et exécutez les 2 macros dans l'ordre :

VB:

Const Source$ = "Sauvegarde.xlsm" 'mémorise la variable, nom à adapter

Sub Sauvegarder()
With ThisWorkbook
    If .Name <> Source Then .SaveCopyAs .Path & "\" & Source 'chemin à adapter
End With
End Sub

Sub Restaurer()
With ThisWorkbook
    If .Name = Source Then Exit Sub
    If MsgBox("Etes-vous sûr de vouloir restaurer toutes les données ?", 4) = 7 Then Exit Sub
    Dim fn$
    fn = .FullName
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    .SaveAs Replace(fn, ".xlsm", " périmé le " & Format(Now, "dd-mm-yyyy hhmmss")) & ".xlsm"
    Workbooks.Open(.Path & "\" & Source).SaveAs fn 'chemin à adapter
    .Close
End With
End Sub

A+

mathi22 · 9 Juillet 2020

Merci beaucoup job75, c'est exactement ça!
Comment faire si je ne veux pas qu'il se fasse une copie "périmé le ..." à chaque restauration? Juste une fermeture sans sauvegarde du fichier modifié, et une ouverture du fichier de référence?

job75 · 9 Juillet 2020

Alors il faut que le fichier se suicide :

VB:

Const Source$ = "Sauvegarde.xlsm" 'mémorise la variable, nom à adapter

Sub Sauvegarder()
With ThisWorkbook
    If .Name <> Source Then .SaveCopyAs .Path & "\" & Source 'chemin à adapter
End With
End Sub

Sub Restaurer()
With ThisWorkbook
    If .Name = Source Then Exit Sub
    If MsgBox("Etes-vous sûr de vouloir restaurer toutes les données ?", 4) = 7 Then Exit Sub
    Dim fn$
    fn = .FullName
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    .SaveAs Replace(fn, ".xlsm", "µ.xlsm")
    Workbooks.Open(.Path & "\" & Source).SaveAs fn 'chemin à adapter
    .ChangeFileAccess xlReadOnly 'lecture seule
    Kill .FullName 'suicide
    .Close
End With
End Sub

mathi22 · 9 Juillet 2020

Parfait, merci encore!

Problème de référence formule et graphique lors de copie de feuilles excel pour réinitialiser données

mathi22

XLDnaute Nouveau

job75

XLDnaute Barbatruc

mathi22

XLDnaute Nouveau

job75

XLDnaute Barbatruc

mathi22

XLDnaute Nouveau

Discussions similaires