Adapter mon code déplacement d'une feuille vers nouveau classeur en valeur

**capi81** · 13/09/2014, 15h19

Salut le forum

Le code ci-dessous me permet de déplacer une feuille vers un nouveau classeur.
J'ai remarqué que les formules contenues dans la feuille à déplacer vers le nouveau classeur affichent des erreurs.
J'ai essayé par l'enregistreur pour avoir une piste de solution mais je ne vois pas une option qui permet de faire en sorte que le déplacement vers le nouveau classeur se fasse en valeur (pour éviter les formules sur le classeur de destination).
Quelqu'un aurait-il une solution pour moi?
Merci

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
Sub Enreg_Proforma()
Dim Dossier As String, Exercice As String, Proforma As String, eee As String
 
Feuille_à_copier = Sheets("PARAMETRE").Range("S6")
 
'Dossier = "\\serveur01\commercial"
Dossier = "C:"
 
Exercice = Dossier & "\EXERCICES " & Format(Date, "yyyy")
If Dir(Exercice, vbDirectory) = "" Then MkDir Exercice
 
Proforma = Exercice & "\PROFORMA " & Format(Date, "yyyy")
If Dir(Proforma, vbDirectory) = "" Then MkDir Proforma
 
' Copier la feuille dans un nouveau classeur
Sheets(Feuille_à_copier).Copy
 
'Enregistrer vers le nouveau classeur
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Proforma & "\" & Feuil1.Range("R1")
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

**Philippe Tulliez** · 13/09/2014, 15h42

Bonjour,
Quand tu écris que tu déplaces une feuille, je suppose que tu voulais dire "copier" car la méthode Copy fait une copie et Move déplace.
Donc si c'est un déplacement, tu peux convertir avant la méthode Move par l'instruction Plage.Value = Plage.Value. Par contre si c'est une copie, il faut utiliser l'instruction après la copie. Il faudrait donc savoir si le message d'erreur renvoyé par la formule se fait directement après la copie. Dans l'affirmative, tu n'as pas d'autre choix que d'utiliser le Copier/Collage Special - Valeur.

**capi81** · 13/09/2014, 16h29

Salut Philippe

Merci pour votre feedback.
Votre conseil m'a permis de comprendre la ou je dois agir.
Il faut dire que j'ai un premier code qui utilise la méthode Copy dans un premier temps.
Je pense que c'est dans ce code que je dois corriger par cette méthode

Code :

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

Copier/Collage Special - Valeur

Je teste et vous reviens pour le résultat.

**Philippe Tulliez** · 13/09/2014, 21h20

Bonjour,
Avec l'instruction Plage.value = Plage.Value comme dans les deux exemples ci-dessous on obtient le même résultat qu'un Copier/Collage Special - Valeur et pour moi est plus intéressant à utiliser.
Toutefois cette dernière instruction sera indispensable si une erreur dans les formules s'est produite lors de la copie ou du déplacement ce qui m'étonne tout de même parce-que Excel ajoute le lien au classeur source en cas de copie ou déplacement.
Exemple avec la méthode Copy

Code :

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

1
2
3
4
5
6
7
8
Sub ProcessCopy()
 Dim shtSource As Worksheet
 Set shtSource = ThisWorkbook.Worksheets("NomFeuille")
 shtSource.Copy ' Copie la feuille vers une nouveau classeur
 With ActiveSheet.UsedRange
 .Value = .Value ' Conserve las valeurs
 End With
End Sub

Exemple avec la méthode Move

Code :

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

1
2
3
4
5
6
7
8
Sub ProcessMove()
 Dim shtSource As Worksheet
 Set shtSource = ThisWorkbook.Worksheets("NomFeuille")
 shtSource.Move ' Déplace la feuille vers une nouveau classeur
 With ActiveSheet.UsedRange
 .Value = .Value ' Conserve las valeurs
 End With
End Sub

**capi81** · 14/09/2014, 15h46

Salut Philippe

Merci pour vos solutions.
J'ai essayé de fusionner le 2è code avec mon code initial(Post#1) mais il y'a un message d'erreur:

L'indice n'appartient pas à la sélection

Cette partie du code a été soulignée :

Code :

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

 Set shtSource = ThisWorkbook.Worksheets(Feuille_à_copier)

Comment fusionner l'un de vos codes avec le mien et obtenir le même résultat?
Voici ce que j'ai bricolé et qui ne fonctionne pas:

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
Sub Enreg_Proforma()
Dim Dossier As String, Exercice As String, Proforma As String, eee As String
Dim shtSource As Worksheet
Feuille_à_copier = Sheets("PARAMETRE").Range("S6")
 
'Dossier = "\\serveur01\commercial"
Dossier = "C:"
 
Exercice = Dossier & "\EXERCICES " & Format(Date, "yyyy")
If Dir(Exercice, vbDirectory) = "" Then MkDir Exercice
 
Proforma = Exercice & "\PROFORMA " & Format(Date, "yyyy")
If Dir(Proforma, vbDirectory) = "" Then MkDir Proforma
 
' Copier la feuille dans un nouveau classeur
 
 Set shtSource = ThisWorkbook.Worksheets(Feuille_à_copier)
 shtSource.Move ' Déplace la feuille vers une nouveau classeur
 With ActiveSheet.UsedRange
 ActiveSheet.Unprotect "eservicessa"
 .Value = .Value ' Conserve las valeurs
 
'Enregistrer le nouveau classeur
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Proforma & "\" & Feuil1.Range("R1")
Application.DisplayAlerts = True
ActiveWorkbook.Close
End With
End Sub

**Philippe Tulliez** · 14/09/2014, 16h12

Bonjour,
Dans ces deux lignes de code

Code :

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

1
2
Dim shtSource As Worksheet
Feuille_à_copier = Sheets("PARAMETRE").Range("S6")

Tu déclares une variable objet Worksheet mais tu attribues comme référence à cet objet un Range et pas un Worksheet
donc si tu veux vraiment que ce soit un Range déclare cette variable comme tel, soit

Code :

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

Dim shtSource As Range

Cependant, il serait évidemment plus simple de rebaptiser cette variable par exemple rngSource ce qui sera plus cohérent pour la lecture.

**AlainTech** · 15/09/2014, 16h35

Bonjour,

Je crois que Feuille_à_copier est une String contenant le nom de la feuille.
Mais comme on ne voit pas de déclaration de cette String...

De plus, dans Feuille_à_copier = Sheets("PARAMETRE").Range("S6"), on peut supposer que c'est le contenu de la cellule qui doit être assigné à la variable.
Il manque donc le .Value

**capi81** · 17/09/2014, 16h01

Salut Philippe et Alain

Merci pour vos solutions.
Tenant compte de vos observations, je suis toujours entrain de réaliser des tests.
Pour le moment je ne suis pas encore arrivé à mes fins.
Je vous reviendrai.

**capi81** · 26/09/2014, 11h03

Bonjour Philippe, Alain et le forum

Voici les observations issues de tes codes du post #4:
J'ai réalisé un test à partir du premier code mais le résultat n'est toujours pas satisfaisant. Le collage des champs contenant les formules affiche le résultat suivant #NOM?. Il en est de même pour le 2è code.
Aussi le 2è code ne crée pas de copie lors du déplacement ce qui entraine la perte de la feuille déplacée à partir fichier original.
Pouvez-vous revoir?
Encore merci vos codes

**Philippe Tulliez** · 26/09/2014, 11h16

Bonjour,
Le message #NOM? signifie que l'on utilise un nom inconnu par excel (formule, référence de cellule, plage ou cellule nommée, tableau). Cela peut provenir d'un argument alphanumérique qui ne se trouve pas entre les guillemets

**capi81** · 26/09/2014, 12h15

Je vous joint le fichier à copier pour test.

**Philippe Tulliez** · 26/09/2014, 12h20

Bonjour,
Je n'ai pas installé sur ma machine le programme permettant de décompressé le format rar.

**capi81** · 26/09/2014, 12h28

Ci-joint un fichier excel

**Philippe Tulliez** · 26/09/2014, 12h33

Bonjour,
Finalement j'avais le décompresseur rar sur ma machine UBUNTU.
J'ai testé la procédure de copie (ProcessCopy) et je n'ai rencontré aucun problème et il n'y a aucun message #NOM?.
Cependant lors de la copie les noms générés par le gestionnaire des noms sont restés ce qui est normal. Il faudrait les supprimer.

**capi81** · 26/09/2014, 13h13

Sur la feuille de destination plus précisement sur la palge C41:C44 vous retrouvez les montants en valeur comme ci-dessous?

Code :

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

1
2
3
4
Quatre Cent Trente-Quatre Mille(434000) Francs Cfa Hd
Zéro(0) Francs Des Droits De Douane
Soixante-Dix-Huit Mille Cent Vingt(78120) Francs Cfa Montant Tva 18%
Soixante-Dix-Huit Mille Cent Vingt(78120) Francs Cfa Toute Taxes Comprises

Je ne sais pas pourquoi chez moi ca ne donne pas le résultat attendu.

**Philippe Tulliez** · 26/09/2014, 13h26

Bonjour,

Sur la feuille de destination plus précisement sur la palge C41:C44 vous retrouvez les montants en valeur comme ci-dessous?

Absolument. J'ai fait le test avec la méthode Copy et Move

[EDIT]
En travaillant sur un classeur cet après-midi, j'ai constaté que les options de calcul étaient sur Manuel. Du coup pris d'un doute j'ai repris ton classeur, refait le test et constaté qu'effectivement j'ai le même message que toi.
Je vais me pencher sur le problème et te tiens au courant.

**Philippe Tulliez** · 26/09/2014, 17h22

Bonjour,
Le problème provenait du fait que tu utilises une fonction personnalisée dans ta feuille et que cette fonction n'est plus appelable depuis un autre classeur et du coup cela provoque le message #NOM?.
Voici ma proposition pour pallier ce problème.
Scénario 1 - Si l'on souhaite copier la feuille vers un autre classeur, on passe par une feuille intermédiaire pour garder les valeurs en lieu et place des formules

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub ProcessCopy()
 Dim shtSource As Worksheet
 With ThisWorkbook
  Set shtSource = .Worksheets("Devis_HT_HD_ZC")
 End With
 shtSource.Copy Before:=Sheets(1) ' Copie vers une feuille temporaire
 With ActiveSheet
  With .UsedRange: .Value = .Value: End With ' Conserve les valeurs
 .Move ' Déplace la feuille temporaire vers un nouveau classeur
 End With
End Sub

Scénario 2 - Si l'on souhaite déplacer la feuille vers un autre classeur, on garde les valeurs en lieu et place des formules avant le transfert vers un autre classeur.

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub ProcessMove()
 Dim shtSource As Worksheet
 With ThisWorkbook
  Set shtSource = .Worksheets("Devis_HT_HD_ZC")
 End With
 With shtSource
  With .UsedRange: .Value = .Value: End With ' Conserve les valeurs
  .Move ' Déplace la feuille vers un nouveau classeur
 End With
End Sub

Attention que pour transférer une feuille vers un nouveau classeur, le classeur source doit avoir au moins deux feuilles avant d'effectuer ce transfert.

Il y a lieu encore de supprimer dans le gestionnaire des noms les formules nommées Numero_AV, Numero_BL, Numero_Devis, Numero_Facture et la plage nommée Zone_d_impression qui se trouve dans le classeur cible.

**capi81** · 06/10/2014, 10h23

Salut Philippe

Je m'excuse du temps mis pour te revenir.
Grande est ma joie après les tests concluant que j'ai réalisé avec tes nouvelles propositions.
Je te tire mon chapeau.

**Philippe Tulliez** · 06/10/2014, 12h52

Bonjour et merci pour ton retour.

Adapter mon code déplacement d'une feuille vers nouveau classeur en valeur [XL-2007]

Macros et VBA Excel

Discussions similaires

Partager

Partager