Pb copier 150 feuilles excel sous vba

Question

Bonjour,

J'ai un problème excel et vba : j'aimerai copier à partir d'une feuille modèle un certain nombre de feuille (je ne connais pas ce nombre à l'avance, je le calcule dans la macro). La macro copie 45 feuilles et buggue à la 46ème. Je ne sais pas pourquoi car j'arrive à insérer une autre feuille. Voici le code :

For P = 0 to NbRespAff - 1
   SocieteCdp.Copy before:=SheetInconnu
   ActiveSheet.Name = ListeAffResp.Range("FHListeRespAff").offset(P,0)
   ActiveSheet.Range("CompteurLigneValo") = 0
Next P

Est-ce que quelqu'un peut m'aider.

Merci d'avance.

Renfield · Answer

"buggue à la 46ème"

un message d'erreur est apparu ? lequel...

Renfield
Admin CodeS-SourceS- MVP Visual Basic

tiph_101 · Answer

Le message est le suivant : erreur d'exécution 1004 La méthode Copy de l'objet worksheet a échoué.

cs_MPi · Answer

Lorsque ça bogue à la 46e, combien de feuilles y a-t-il à copier ?
et sur quelle ligne est-ce que ça plante ?

MPi

tiph_101 · Answer

Comme je l'ai dit dans mon premier message, je ne sais pas le nombre de feuille à copier à l'avance mais pour info lors du plantage de la macro j'en ai 131 et ce chiffre peut varier (aller par exemple jusqu'à 150 ou plus).
La macro plante sur la ligne SocieteCdp.Copy before:=SheetInconnu

cs_MPi · Answer

Donc ça plantait sur la 46e feuille sur 131, c'est bien ça ?

Parce qu'avec ce que tu donnes, on ne sait pas trop
For P = 0 to NbRespAff - 1  'que vaut NbRespAff ?
    'Est-ce que ce Range (FHListeRespAff) contient des espaces ? 
    'Que vaut ce Range ? quelle(s) cellule(s) désigne-t-il ?
   ActiveSheet.Name = ListeAffResp.Range("FHListeRespAff").offset(P,0)
   ActiveSheet.Range("CompteurLigneValo") = 0
Next P

MPi

tiph_101 · Answer

NbRespAff est de type integer calculé dans la macro (à ce stade NbRespAff = 131 mais peut être = 150 ou 100)
Range (FHListeRespAff) est de type string (Nom Prénom) et donc contient des espaces.

Mon problème est sur la ligne SocieteCdp.Copy before:=SheetInconnu quand P = 46

cs_MPi · Answer

>> Range("FHListeRespAff")

c'est bien un nom de plage que tu as créée ?
quelle est la plage que ce nom désigne ?  je pose la question à cause du  offset(P,0)

Es-tu certain d'avoir mis tout le code de cette boucle ?
Ça plante toujours à 46 ?

MPi

tiph_101 · Answer

Range("FHListeRespAff")  est un nom de cellule et oui dans cette boucle il y a tout le code; Pourquoi ?

Oui ca plante toujours à 46 et je n'arrive pas à trouver pourquoi.

cs_MPi · Answer

Si je te pose toutes sortes de questions qui semblent idiotes, c'est aussi pour comprendre...
Je ne vois pas de raison pour que ça bloque sur cette ligne à moins que SheetInconnu (qu'on ne connaît pas) change de valeur ou ait une valeur farfelue...(?)

Lorsque l'erreur apparaît, est-ce que tu as le bouton Débogage dans le message ? si oui et que tu le cliques, ça arrête sur la ligne dont tu parles, j'imagine. Si tu fais F8, est-ce que ça plante immédiatement ou si ça saute à l'autre ligne ?

MPi

tiph_101 · Answer

Ne t'inquiète pas pour les questions "idiotes". Je comprends que tu essaie de comprendre et donc à m'aider.

J'ai le bouton "Débogage" et quand je fais F8 ca plante tout de suite sur la ligne "SheetCopy...".
SheetInconnu est une feuille nommée "Inconnu" dans laquelle sont stockées des informations qui ne peuvent pas être dans les autres feuilles.

En fait mon vrai problème est : je passe par un copier pour pouvoir récupérer dans les feuilles le nom des zones qui se trouvent dans le modèle.
Lorsque j'insère avec Sheets.Add, j'ai bien mes 131 feuilles. Puis j'insère le bout de code suivant :

For Each Feuillle in Classeur.Worksheets
   Feuille = ActiveSheet
   Feuille.Range("A3").Name = "FHValoNumAff"
   Feuille.Range("B3").Name = "FHValoAff"
   ...
Next Feuille

Mais seule la dernière feuille a les zones de nom. Et je ne sais pas comment faire pour que toutes les cellules "A3", "B3"... de toutes les feuilles aient le même nom. Je me dis que ca doit être possible parce qu'avec copie d'une feuille vers une autre, on récupère ces noms.

cs_MPi · Answer

Je ne sais pas si ça pourra aider à comprendre quoique ce soit, mais bon... je viens de faire un test avec le code qui suit.
    Je pars avec un classeur qui contient une seule feuille.
    Je crée 150 feuilles. Je les supprime. Puis je redémarre la macro.
    Plantage à la 105e feuille
    150 + 105 + la feuille existante =  256

Je ferme le classeur et je refais le test avec une boucle à 100
    Premier passage: 100 feuilles copiées. Je les supprime.
    Deuxième passage: 100 feuilles copiées. Je les supprime.
    Troisième passage: 54 feuilles copiées >> plantage à i = 55

Donc, Excel se souvient des feuilles créées, même si elles sont supprimées.
Si je ferme le classeur et le rouvre, je peux recommencer et il y aura plantage de la même façon après 255 feuilles...

 Code utilisé: 
Sub Macro1()
    Dim I As Integer
    Dim Sht As Worksheet
    
    Set Sht  = Sheets("Feuil1")
    For I = 1 To 150
        Sht.Copy Before:=Sht
        ActiveSheet.Name = "F" & I
    Next
    
End Sub

MPi

tiph_101 · Answer

Ok je comprends mais j'ai résolu mon problème.
En fait je crée un nouveau classeur dans lequel je copie la feuille modèle. Puis j'insère mes 130 feuilles. Dans la cellule "A1" (ou une autre cellule) de chaque feuille, je met le numéro de la feuille et je nomme mes zones de la façon suivante :

For Each Feuille in Classeur.Worksheets
   Feuille.Range("A3").Name = "FHValoNumAff" & numero_feuille
   ...
Next Feuille

Et après dans la macro, pour faire référence aux noms de zones de la feuille sur laquelle je suis j'emploie la même technique.

Je ne sais pas si c'est la meilleure solution mais c'est la seule que j'ai pour le moment.

Pb copier 150 feuilles excel sous vba

12 réponses

Votre réponse

Discussions similaires