VBA EXCEL - BUG : Copie feuille Résolu

Question

Bonjour,
Bonjour à tous,
Voilà j'ai un petit soucis avec une de mes macros sur un classeur: 

Le 1 er classeur est composé d'un tableau dont j'aimerai dispatcher chaque ligne dans des feuilles d'un 2ème classeur. (Ce classeur étant composé d'autant de feuilles que mon tableau a de lignes)

Voila comment se passe la macro :
J ai créé dans le 2ème classeur une feuille modèle cachée :
A chaque ligne la macro copie ma feuille, la remplit, passe à la ligne suivante. J'ai créé une boucle...

Malheureusement cette boucle ne marche que 56 fois alors que mon tableau comprend 250 lignes (au maximum).  
Apparait alors cette erreur:

Erreur d'execution 1004
La méthode Copy de la classe Worksheet a échoué.

sur cette ligne :
Classeur.Sheets("Modele").Copy after:=Classeur.Sheets("Accueil") 'Acceuil étant une feuille d'acceuil dans le 2ème classeur


Cette ligne est pourtant dans la boucle!!
- Je n'ai pourtant pas dépasser le nombre maximal de feuilles autorisées dans un classeur. Je peux d'ailleurs rajouter des onglets manuellement. 
- La feuille modèle ne "pèse" que 200Ko

HELP !!
Merci beaucoup d'avance

Yoann

PS : j'espère que j'ai été plutot clair. Je peux donner plus d'info si besoin, mais je pense que l'essentiel du problème est la.

calborno · Accepted Answer

Voici ce que j'ai trouvé sur le site microsoft:

Cause:
Ce problème se produit car à chaque fois que vous copiez la même feuille, un nom de code est affecté à la copie, et à chaque copie de la feuille ce nom de code s'allonge. Par exemple, si vous copiez sheet1 une fois, le nom de code affecté à la feuille est sheet11. Lorsque vous copiez à nouveau cette feuille, le nom sheet111 lui est affecté, et ainsi de suite. La longueur maximale du nom de code est d'environ 35 caractères. Lorsque cette limite est atteinte, Excel cesse de répondre.

Solution proposée:
Pour contourner ce problème, essayez d'utiliser un code semblable au suivant pour copier des feuilles de calcul : 
***
 Sub Copy Sheets()

    For x = 1 To 300
        Sheets.Add
        Workbooks("YourWorkBook.xls").Sheets("sheet1").Cells.Copy
        ActiveSheet.Paste
        Workbooks("YourWorkBook.xls").Sheets("sheet1").Select
    Next x

 End Sub
***

Voilà...

gbinforme · Answer

bonjour

- Je n'ai pourtant pas dépasser le nombre maximal de feuilles autorisées dans un classeur. 

 le nombre maximal de feuilles dans un classeur est fonction de la mémoire disponible...

Je peux d'ailleurs rajouter des onglets manuellement.

Sans doute que ta procédure VBA occupe de la mémoire qui est ensuite libérée.


- La feuille modèle ne "pèse" que 200Ko

C'est la taille disque mais en mémoire ce n'est pas forcément pareil.

Tu devrais regarder si tu peux optimiser ta procédure pour ne pas créer de réservations de zones inutiles ou non libérées.

Si tout est optimum, il faut aller voir le marchant de barrettes...

nnaoy33 · Answer

Coucou
Tout d'abord merci de m'avoir répondu.

J ai "allégé" mon classeur contenant  la macro. J ai supprimé mes userforms et autres macro inutiles au fonctionnement de celle ci, de sorte qu'il ne reste plus que l'essentiel.
Malheureusement aucune feuille de plus n'est créée. 
J ai peut etre une solution mais j'aimerai avoir votre avis avant : de refaire partir une autre boucle à partir de la 55ème ligne de mon tableau. Boucle qui aurait la meme allure.
 Est ce inutile? Je vais essayer, je vous tiens au courant. Je suis débutant en programmation j'en ai donc pour un petit moment...

Yoann

nnaoy33 · Answer

Me revoilou
J ai donc essayé de recréer la meme boucle pour les lignes restantes.
J'ai été obligé de réouvrir un 2ème classeur bis. Cependant il ne peut me créer que 18 feuilles dans ce nouveau classeur.
J'ai enlevé :
Application.ScreenUpdating
pour libérer de la mémoire.

Mais toujours pas : le 2ème classeur bloque à 56 feuilles et le 2ème bis à 18.

GRRRR.....
Une idée?
MErci d'avance.
Yoann

dan_ange · Answer

Bonjour, j'ai moi-même un résultat bizarre en voulant copier une feuille avec VBA dans excel 2000. Sur mon poste, les choses se déroulent comme prévu : insertion d'une copie d'une feuille juste après celle-ci et renommage. 
Mais sur d'autres postes la macro commence et...s'envole je ne sais où sans qu'il y ait copie. 
Apparemment, la sélection de la feuille se passe bien, mais la demande de copie entraine une sortie de la procédure. pendant quelques secondes, l'accès à la copie de la feuille (par le bouton droit "copier et déplacer") n'est pas accessible sur cette feuille.

Mon poste est équipé d'accès. Les autres non.
Dans les "outils " de Visual Basic Editor, il est écrit dans les modules cochés :" manquant CPSEtendedControls 1.0 type library".

Toutes les autres fonctions programmés marchent bien (apparition de calendrier, masquage ou affichage de lignes par un  bouton, etc).

dan_ange · Answer

Je rajoute que j'ai essayé après cela d'enregistrer basiquement une simple macro de copie de feuille (sur les postes où cela ne marche pas). 
L'enregistrement se passe bien, mais la macro agit de la même façon que mon programme dès qu'elle rencontre le terme "copy".

Utilisateur anonyme · Answer

Bonjour,

Suggestion :

Exemple type testé sous Office 2003 (WXP).


Option Explicit

Sub Test()
    
    Dim Classeur As Workbook
    Dim Boucle As Long
    
    Application.ScreenUpdating = False
    Set Classeur = ActiveWorkbook
    
    Boucle = 1
    Classeur.Sheets("Modele").Copy after:=Classeur.Sheets("Accueil")
    ActiveSheet.Name = "Copie" & Boucle
    For Boucle = 2 To 250
        Classeur.Sheets("Modele").Copy after:=Classeur.Sheets(Classeur.Sheets.Count)
        ActiveSheet.Name = "Copie" & Boucle
    Next Boucle
    Application.ScreenUpdating = True
    
End Sub
'


Lupin

commentcamarcheeay · Answer

Bonjour tout le monde,

J'utilise VB6 pour communiquer avec Excel 2000. J'ai le même soucis que vous. A chaque fois quand j'arrive à l'instruction .Copy une Exception se déclenche. Je ne cherche pas à copier une page plusieurs fois. Ça m'arrive même pour une seule reprise. C'est en fait irrégulier. Avec le même code, des fois ça marche et d'autres fois ça coince.

Voici mon code:

            ...
            Dim xlBook As Excel.Workbook
            Dim xlSheet As Excel.Worksheet
            Set xlBook = frmTrain.xlBook
            With xlBook
                Set xlSheet = .Worksheets(1)
                xlSheet.Copy after:=xlBook.Worksheets(.Worksheets.Count)
                Set xlSheet = xlBook.Worksheets(.Worksheets.Count)
                With xlSheet
                    .Name = "Train" & .Parent.Worksheets.Count
                    Dim i As Integer
                    ' Ecriture du Nom de train
                    .Cells(2, 3) = tempVar(0)
                    ' Vider les cellules
                    .Range(.Cells(4, 2), .Cells(4, 3).End(xlDown)).value = 0
                    ' Liste des positions
                    For i = LBound(tempVar(1)) To UBound(tempVar(1))
                        .Cells(4 + i, 2) = tempVar(1)(i)(0)
                        .Cells(4 + i, 3) = tempVar(2)(i)(0)
                    Next i
                End With
            End With
            Set xlBook = Nothing
            ...


La solution de calborno parait intéressante. Je vais la tester et vous en faire part.
Celle de Lupin.A ressemble à la mienne. Ça ne  fonctionne pas pour moi.

Bonne continuation!

cousinhub29 · Answer

Bonjour,

Je ne sais pas sous VB, mais en VBA, il existe une solution toute simple, pour éviter ce genre de "Bug"....

Le principe :
Tu créés un fichier ne comportant qu'un seul onglet, cet onglet servant de modèle (tu nommes par exemple ce fichier "modele.xls"). Cet onglet a bien évidemment la même structure que ton onglet 1.

Et ensuite, au lieu de recopier l'onglet 1 à suivre, tu insères un nouvel onglet, du même type que le fichier "modele.xls" par cette ligne de code :

Sheets.Add Type:=ActiveWorkbook.Path & "\modele.xls", _
           After:=Sheets(Sheets.Count)

Tu peux ainsi ajouter autant d'onglet identiques que tu veux....

Cette ligne de code remplace ta ligne :

 xlSheet.Copy after:=xlBook.Worksheets(.Worksheets.Count)


Bonne journée

VBA EXCEL - BUG : Copie feuille

9 réponses

Discussions similaires

Newsletters