Erreur de copie de worksheet

tonton33650 Messages postés 23 Date d'inscription vendredi 11 mai 2007 Statut Membre Dernière intervention 3 décembre 2008 - 7 juin 2007 à 13:11
sobullshit Messages postés 178 Date d'inscription vendredi 9 février 2007 Statut Membre Dernière intervention 8 juin 2007 - 8 juin 2007 à 08:10
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

jrivet Messages postés 7393 Date d'inscription mercredi 23 avril 2003 Statut Membre Dernière intervention 6 avril 2012 60
7 juin 2007 à 13:23
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
0
tonton33650 Messages postés 23 Date d'inscription vendredi 11 mai 2007 Statut Membre Dernière intervention 3 décembre 2008
7 juin 2007 à 14:01
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
0
drikce06 Messages postés 2237 Date d'inscription lundi 29 mai 2006 Statut Membre Dernière intervention 29 mai 2008 11
7 juin 2007 à 14:09
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)
0
drikce06 Messages postés 2237 Date d'inscription lundi 29 mai 2006 Statut Membre Dernière intervention 29 mai 2008 11
7 juin 2007 à 14:13
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
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
sobullshit Messages postés 178 Date d'inscription vendredi 9 février 2007 Statut Membre Dernière intervention 8 juin 2007
7 juin 2007 à 14:39
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...
0
drikce06 Messages postés 2237 Date d'inscription lundi 29 mai 2006 Statut Membre Dernière intervention 29 mai 2008 11
7 juin 2007 à 14:43
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)
0
sobullshit Messages postés 178 Date d'inscription vendredi 9 février 2007 Statut Membre Dernière intervention 8 juin 2007
7 juin 2007 à 14:44
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.
0
sobullshit Messages postés 178 Date d'inscription vendredi 9 février 2007 Statut Membre Dernière intervention 8 juin 2007
7 juin 2007 à 14:46
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.
0
tonton33650 Messages postés 23 Date d'inscription vendredi 11 mai 2007 Statut Membre Dernière intervention 3 décembre 2008
7 juin 2007 à 16:04
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
0
drikce06 Messages postés 2237 Date d'inscription lundi 29 mai 2006 Statut Membre Dernière intervention 29 mai 2008 11
7 juin 2007 à 16:10
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)
0
tonton33650 Messages postés 23 Date d'inscription vendredi 11 mai 2007 Statut Membre Dernière intervention 3 décembre 2008
7 juin 2007 à 16:39
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
0
drikce06 Messages postés 2237 Date d'inscription lundi 29 mai 2006 Statut Membre Dernière intervention 29 mai 2008 11
7 juin 2007 à 16:42
autant pour moi, je fatigue là!

 Drikce 06 (Nouveau !!! Forum Exclusivement Office Et VBA By Mortalino)
0
drikce06 Messages postés 2237 Date d'inscription lundi 29 mai 2006 Statut Membre Dernière intervention 29 mai 2008 11
7 juin 2007 à 16:45
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)
0
drikce06 Messages postés 2237 Date d'inscription lundi 29 mai 2006 Statut Membre Dernière intervention 29 mai 2008 11
7 juin 2007 à 16:47
0
sobullshit Messages postés 178 Date d'inscription vendredi 9 février 2007 Statut Membre Dernière intervention 8 juin 2007
8 juin 2007 à 08:10
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
0