Erreur de copie de worksheet

Signaler
Messages postés
23
Date d'inscription
vendredi 11 mai 2007
Statut
Membre
Dernière intervention
3 décembre 2008
-
Messages postés
178
Date d'inscription
vendredi 9 février 2007
Statut
Membre
Dernière intervention
8 juin 2007
-
Bonjour,

Je viens de creer une macro qui me permet de creer un nouvel onglet pour chaque valeur (chaque valuer etant unique) qui se trouve dans mon portfeuille ( qui est lui Sheets(1) dans mon cas). Je cherche a obtenir, pour un portfeuille donne, une fiche descriptive de chacune de ses valeurs.
Lors du lancement de la macro, apres le chargement d'un nouveau portfeuille par exemple, chaque onglet correspondant aux valeurs du precedent portefuille est donc supprime et de nouvelles feuilles apparaissent avec les valeurs desormais souhaitees.

Voici le code que je viens d'ecrire :

Code :

Sub copy()


Dim j As Integer
Dim last_asset As Integer




i = 13
j = 3




Sheets(j - 1).Range("A3").Value = Sheet1.Cells(i, 2).Value
Sheets(j - 1).Range("B3").Value = Sheet1.Cells(i, 3).Value
Sheets(j - 1).Range("C3").Value = Sheet1.Cells(i, 7).Value
Sheets(j - 1).Range("D3").Value = Sheet1.Cells(i, 6).Value


last_asset = Sheets("Portfolio").Range("A13").End(xlDown).Row


 


For i = 14 To last_asset
   
    j = i - 12
   
    'duplication of previous sheet
   
    Sheets(j).copy Before:=Sheets("Chart CF")
    Application.Run "BLPLinkReset"
    Sheets(j + 1).Select
    Sheets(j + 1).Name = Sheets("Portfolio").Cells(i, 1).Value
   
    'reauired  data 
    
    Sheets(j + 1).Range("A3").Value = Sheet1.Cells(i, 2).Value
    Sheets(j + 1).Range("B3").Value = Sheet1.Cells(i, 3).Value
    Sheets(j + 1).Range("C3").Value = Sheet1.Cells(i, 7).Value
    Sheets(j + 1).Range("D3").Value = Sheet1.Cells(i, 6).Value
   
    'chart adjustment
   
    Sheets(j + 1).Columns("A:F").EntireColumn.AutoFit
   
   
Next i
End Sub

Comme vous pourrez le constater, toutes les feuilles sont identiques et sont une copie de la feuille precedente.
Lorsque j'ouvre mon fichier et que je lance ma macro une premiere fois, tout se passe tres bien. Mon probleme intervient lorsque je veux relancer la macro. A peine arrive a la moitie de mon portefeuille, un message d'alerte apparait au niveau de la ligne en gras sur mon code. 

Run'time error '1004'
Copy method of worksheet class failed

Quelqu'un aurait-il une idee sur ce probleme? Mon portfeuille comporte plus de 100 valeurs, est ce un probleme de saturation de la methode de copie ou est ce un probleme avec mon code?

Je vous remercie pour votre aide.

15 réponses

Messages postés
7393
Date d'inscription
mercredi 23 avril 2003
Statut
Membre
Dernière intervention
6 avril 2012
58
Salut,
Je dirait que j possède au moment ou cela plante, une valeur qui ne plait pas à Excel
OU bien Que la Feuille nommée Chart CF n'existe pas.
Ce que tu peux essayer c'est de mettre un point d'arret sur la ligne qui plante pour "espionner" tout ca.

@+: Ju£i?n
Pensez: Réponse acceptée
Messages postés
23
Date d'inscription
vendredi 11 mai 2007
Statut
Membre
Dernière intervention
3 décembre 2008

En fait la feuille Chart CF existe bien et elle se situe en derniere position quel que soit le nombre d'onglet que j'ai. Trois feuilles sont toujours presentes dans mon classeur. La sheets(1) qui est le portfeuille de ref, le sheets(2) qui est le modele de base sur lequel vont se baser les autres copies et la Sheets("CHART CF") aui se situe donc en derniere position.
Voici le code que j'ai oublie de preciser pour supprimer toutes les feuilles au debut de mon module, avant d'entamer la boucle sur toutes mes valuers:

CODE:

Do Until Sheets.Count = 3
Application.DisplayAlerts = False
Workbooks("CF_Analysis_O2").Sheets(3).Delete
Application.DisplayAlerts = True
Loop

J'ai donc en permanence au moins trois feuilles dans mon classeur.
J'ai place un watch sur ma variable j qui vient se bloquer sur la valeur 71 lors du deuxieme lancement. A partir de se moment la si je souhaite relancer la macro, la variable j ne decolera pas de la valeur 2.

Vraiment je suis completement bloque.

Merci pour votre aide
Messages postés
2237
Date d'inscription
lundi 29 mai 2006
Statut
Membre
Dernière intervention
29 mai 2008
11
Salut renomme la feuille, jamais d'espace dans les noms de feuille et réessaye.

 Drikce 06 (Nouveau !!! Forum Exclusivement Office Et VBA By Mortalino)
Messages postés
2237
Date d'inscription
lundi 29 mai 2006
Statut
Membre
Dernière intervention
29 mai 2008
11
Et ajoute un Sheets(j).Select juste avant la copie.

 Drikce 06 (Nouveau !!! Forum Exclusivement Office Et VBA By Mortalino)
Y devenir membre de la
Messages postés
178
Date d'inscription
vendredi 9 février 2007
Statut
Membre
Dernière intervention
8 juin 2007

Avis personnel :
le Sheets(j).Select n'est pas trop utile (mais comme précisé c'est purement personnel)

J'ai tenté de copier des feuilles dans un classeur Excel, et j'en étais à 258 avant que ça plante...
Messages postés
2237
Date d'inscription
lundi 29 mai 2006
Statut
Membre
Dernière intervention
29 mai 2008
11
J'ai souvent eu des problème avec des .copy ou d'autres fonctions dans ce style et chaque fois le sheet.Select réglait le problème.

 Drikce 06 (Nouveau !!! Forum Exclusivement Office Et VBA By Mortalino)
Messages postés
178
Date d'inscription
vendredi 9 février 2007
Statut
Membre
Dernière intervention
8 juin 2007

Je pense que ceci peux t'aider :

http://support.microsoft.com/kb/210684/fr
Apparemment c'est un bug Excel, normal que tu le comprennes pas.
Messages postés
178
Date d'inscription
vendredi 9 février 2007
Statut
Membre
Dernière intervention
8 juin 2007

Alors dans ce cas je dirais que le .Select est très utile ^^.
Les seuls erreurs que j'ai eu en employant ce genre de fonctions étaient dues à ma stupidité, donc un .Select ne pouvais m'aider.
Messages postés
23
Date d'inscription
vendredi 11 mai 2007
Statut
Membre
Dernière intervention
3 décembre 2008

Merci pour vos reponses, mais malheureusement je n'ai toujours pas reussi a trouver une solution. Peut etre ais-je besoin de qq conseils sur les reponses que vous venez de me donner.
J'ai dans une premier tps place un sheets(j).select avant mon .copy mais le resultat n'a pas ete convaincant. Je suis alle me balader sur lien que sobullshit m'a propose et voici le code que j'obtiens apres adaptation

CODE:
Sub copy()


Dim j As Integer
Dim last_asset As Integer




Do Until Sheets.Count = 3
Application.DisplayAlerts = False
Workbooks("CF_Analysis_O2").Sheets(3).Delete
Application.DisplayAlerts = True
Loop




i = 13
j = 3


Sheets(j - 1).Range("A3").Value = Sheet1.Cells(i, 2).Value
Sheets(j - 1).Range("B3").Value = Sheet1.Cells(i, 3).Value
Sheets(j - 1).Range("C3").Value = Sheet1.Cells(i, 7).Value
Sheets(j - 1).Range("D3").Value = Sheet1.Cells(i, 6).Value


last_asset = Sheets("Portfolio").Range("A13").End(xlDown).Row


For i = 14 To last_asset
   
    j = i - 12
   
    'duplication if previous sheet
   
    Sheets(j).Select
    Sheets(j).copy Before:=Sheets("Chart CF")
    Application.Run "BLPLinkReset"
    Sheets(j + 1).Select
    Sheets(j + 1).Name = Sheets("Portfolio").Cells(i, 1).Value
   
    'save close and reopen after every 150 iterations:
   
    If i Mod 150 = 0 Then
       
        Workbooks("CF_Analysis_O2").Close (True)
        Set Workbooks("CF_Analysis_O2") = Nothing
        Set Workbooks("CF_Analysis_O2") = Application.Workbooks.Open("H:\.....\O2_CF_Analysis.xls")
       
    End If
   
    'reauired bloomberg data transfer
   
    Sheets(j + 1).Range("A3").Value = Sheet1.Cells(i, 2).Value
    Sheets(j + 1).Range("B3").Value = Sheet1.Cells(i, 3).Value
    Sheets(j + 1).Range("C3").Value = Sheet1.Cells(i, 7).Value
    Sheets(j + 1).Range("D3").Value = Sheet1.Cells(i, 6).Value
   
    'chart adjustment
   
    Sheets(j + 1).Columns("A:F").EntireColumn.AutoFit
   
   
Next i




End Sub

Finalement mon i se reinitialise (du moins j'ai l'i;pression, mais peut n'ais je rien compris) a chaque lancement de macro donc j'ai du mal a voir comment il pourrait atteindre 150 et, placer mon seuil de fermeture plus bas, a 100 par exemle, m'empecherai de couvrir toutes les valeurs de mon portefeuille. 

Y a t il une erreur dans mon code. Avez  vous une suggestion pour que je puisse debloquer ma situation ou dois je  trouver une autre methode ?

Merci
Messages postés
2237
Date d'inscription
lundi 29 mai 2006
Statut
Membre
Dernière intervention
29 mai 2008
11
Enlève l'espace dans le nom de la feuille préfère le _,

Sinon cela parait bizzrad, cela m'étonnerai qu'un index de feuille soit égale à -1. A moins que j'ai zappé quelque chose!

i = 13
j = 3
Sheets(j - 1).Range("A3").Value = Sheet1.Cells(i, 2).Value
Sheets(j - 1).Range("B3").Value = Sheet1.Cells(i, 3).Value
Sheets(j - 1).Range("C3").Value = Sheet1.Cells(i, 7).Value
Sheets(j - 1).Range("D3").Value = Sheet1.Cells(i, 6).Value

last_asset = Sheets("Portfolio").Range("A13").End(xlDown).Row
i 13 et j 3
For i = 14 To last_asset
    j 13-12 donc -1
    j = i - 12

 Drikce 06 (Nouveau !!! Forum Exclusivement Office Et VBA By Mortalino)
Messages postés
23
Date d'inscription
vendredi 11 mai 2007
Statut
Membre
Dernière intervention
3 décembre 2008

excuse moi Drikce06 mais je ne comprends pas bien ce que tu m'as ecrit. Mon index de feuille n'atteint jamais -1. Pourrais tu me reexpliquer ce que tu me conseille de faire.
Ok pour les espaces dans le nom de feuille.
 
Merci
Messages postés
2237
Date d'inscription
lundi 29 mai 2006
Statut
Membre
Dernière intervention
29 mai 2008
11
autant pour moi, je fatigue là!

 Drikce 06 (Nouveau !!! Forum Exclusivement Office Et VBA By Mortalino)
Messages postés
2237
Date d'inscription
lundi 29 mai 2006
Statut
Membre
Dernière intervention
29 mai 2008
11
On reprend:

Au premier passage j=2 (j = i -12, j = 14-12)
Sheets(j).copy Before:=Sheets("Chart CF")

C'est bien ça?

Si oui fait-il la copy?

Si elle n'est pas faite chnage le nom de cette feuille comme ceci dans un premier temps: ("Chart_CF") et réessaye.

 Drikce 06 (Nouveau !!! Forum Exclusivement Office Et VBA By Mortalino)
Messages postés
2237
Date d'inscription
lundi 29 mai 2006
Statut
Membre
Dernière intervention
29 mai 2008
11
Messages postés
178
Date d'inscription
vendredi 9 février 2007
Statut
Membre
Dernière intervention
8 juin 2007

Si tu n'as encore résolu ton problème, essaie de déclarer tes variables en long au lieu de integer.

Si tu veux lire toutes les cellules remplies, au lieu de faire :

Sheets("Portfolio").Range("A13").End(xlDown).Row

met plutôt :

Sheets("Portfolio").Cells(SpecialCells).row (ou un truc ds le genre)

ça permet de lire jusqu'à la dernière ligne remplie