Copier plusieurs feuilles d'un classeur vers un autre

Le 13/04/2016 à 17:30

Bonjour,

Je suis en train de créer une macro qui permet à l’utilisateur (via un UserForm) de sélectionner divers éléments à copier à partir d'un classeur d'origine vers un nouveau classeur.

J’aimerais, entre autre, pouvoir effectuer la copie de feuilles entières.

Dans mes recherches, je suis tombé sur des solutions utilisant la méthode Worksheet.Copy.

J'ai donc essayé de la mettre en application. Mes essais se passent bien lorsqu'il s'agit de copier mes feuilles directement dans le classeur source ou encore lorsque je ne place pas d'argument "After" (création automatique d'un classeur de destination) .

Mais lorsque je veux copier mes feuilles dans un classeur de destination précis, je tombe sur l'erreur 1004:

"La méthode Copy de la classe Worksheet a échoué".

Je vous joins la partie de mon code qui pose problème :

Nbre_feuilles = wb.Worksheets.Count ' nombre de feuilles dans le classeur source (noté "wb")

k = 0
If Elements_etude.OptionButton1.Value = True Then ' si on choisit de copier des feuilles entières (option du UserForm)
    For i = 1 To Nbre_feuilles ' on parcourt les feuilles du classeur source
        k = k + 1
        If wb.Worksheets(i).Name = Elements_etude.Controls("Feuille" & k).Caption Then  ' si, dans le classeur source, on a trouvé une feuille dont le titre a été choisi dans le UserForm
            If Elements_etude.Controls("CheckBox" & k).Value = True Then ' et que la copie de cette feuille est demandée
                ' on copie la feuille correspondante dans un classeur précédemment créé par la macro ("Elements_etude.xlBook")
                wb.Worksheets(i).Copy After:=Elements_etude.xlBook.Worksheets(Elements_etude.xlBook.Worksheets.Count)
            End If
        Else
            k = k - 1
        End If
    Next
End If

(Les feuilles à copier proposées par le UserForm sont moins nombreuses que les feuilles du classeur source. D'où l'utilisation du nombre "k", qui me permet de "parcourir" les feuilles proposées dans le UserForm)

Je n'ai pas une très grande expérience en VBA. C'est possible que mon erreur soit toute bête

Bref, ma question est donc la suivante : quelqu'un aurait-il une idée de la cause de cette erreur 1004 ?

Je ne demande pas forcément une résolution détaillée, mais juste des pistes qui me permettraient de me débloquer.

Parce que là je dois avouer que je sèche.

Si jamais l'explication de mon code n'est pas claire, n'hésitez pas à me le faire savoir.

Merci d'avance

Le 13/04/2016 à 19:59

Bonsoir,

peu tu nous dire comment tu obtient, déclare...

cet objet :

Elements_etude

et comment tu initialise sa propriété xlBook ?

Le 13/04/2016 à 22:26

Bonsoir Rod, Pierre, bonsoir le forum,

Je ne connais pas de fichier Excel avec une extension "xlBook". Peut-être l'erreur vient de là...

De plus j'ai cru comprendre que tu utilisais des Labels ou des ComandButtons (Elements_etude.Controls("Feuille" & k).Caption) pour lister tes onglets et qu'ensuite tu mettais une Chechcbox en face que tu cochais pour définir si l'onglet allait être copié ou pas !

Une simple ListBox avec la Propriété [MultiSelect] = 2 - fmMultiSelectMulti et éventuellement sa propriété [ListStyle] = 1 - fmListStyleOption ferait tout aussi bien l'affaire et t'éviterait un code compliqué.

Je te propose un exemple dans la fichier en pièce jointe. Ouvre un fichier vierge, nomme le Test.xlsx (il correspond au fichier créé par la macro). Ouvre mon fichier et clique sur le bouton Copie d'Onglets.

Le code :

Private CS As Workbook 'déclare la variable CS (Classeur Source)
Private CD As Workbook 'déclare la variable CD (Classeur Destination)

Private Sub UserForm_Initialize() 'à l'initialisation de l'UserForm
Dim O As Worksheet ''déclare la variable O (Onglets)

Set CS = ThisWorkbook 'définit le classeur source CS
Set CD = Workbooks("Test.xlsx") 'définit le classeur destination CD (à adapter)
For Each O In CS.Sheets 'boucle sur tous les onglets du classeur source CS
    Me.ListBox1.AddItem O.Name 'ajoute le nom d el'onglet à la ListBox1
Next O 'prochain onglet de la boucle
End Sub

Private Sub CommandButton1_Click() 'bouton "Copier"
Dim TOS() As Variant 'déclare la varaible TOS (Tableau des Ongles Sélectionnées)

With Me.ListBox1 'prend en compte la ListBox1
    For I = 0 To .ListCount - 1 'boucle  sur tous les éléments
        If .Selected(I) = True Then 'condition : si l'élément est sélectionné
            ReDim Preserve TOS(J) 'redimensionne le tableau TOS
            TOS(J) = .List(I) 'récupère le nom de l'ong;et sélectionné
            J = J + 1 'incrémente J
        End If 'fin de la condition
    Next I 'prochain élément de la boucle
End With 'fin de la prise en compte de la ListBox1
'copie les onglets du tableau TOS après le dernier onglet du classeur de destination CD
Sheets(TOS).Copy After:=CD.Sheets(CD.Sheets.Count)
Unload Me 'vide et ferme l'UserForm1
End Sub

Private Sub CommandButton2_Click() 'bouton "Annuler"
Unload Me 'vide et ferme l'UserForm1
End Sub

Le fichier :

277classeur-source.xlsm (29.13 Ko)
Le 14/04/2016 à 09:15

Bonjour,

Pierre, concernant mon code :

  • "Elements_etude" est tout simplement le nom que j'ai donné à mon UserForm
  • "xlBook" est le nom de mon classeur de destination, celui dans le quel je souhaite copier mes feuilles.

ThauThème, merci beaucoup pour ton code, ça fonctionne parfaitement !

Je ne savais pas qu'il était possible de définir ainsi une ListBox, ça va m'être utile dans la suite de mon travail.

Je crois que grâce à ton code, j'ai aussi compris d'où vient mon erreur. En voulant exécuter ta macro je suis d'abord tombé sur une erreur.

En fait, j'avais ouvert "Classeur Source.xlsm" et "Test.xlsx" dans deux 'exécutions' Excel différentes (en ouvrant d'abord une nouvelle fois Excel et en sélectionnant le fichier à ouvrir à partir du menu Fichier). Ce n'est qu'après avoir ouvert les fichiers "Classeur Source.xlsm" et "Test.xlsx" dans la même 'exécution' Excel que ta macro a marché.

Je ne sais pas si je décris bien le problème, le mot 'exécution' n'étant certainement pas le plus explicite.

Si besoin, j'essaierai de reformuler.

Ma macro crée un nouveau classeur Excel (celui de destination donc) dans une nouvelle 'exécution', de sorte que l'on puisse afficher les deux classeurs (celui source et celui de destination) en même temps. Je pense que le problème vient de là.

Enfin bref, merci à tous les deux de vous avoir donné la peine de me répondre aussi rapidement. Je vais tâcher de régler le problème et je reviendrai confirmer si mon intuition est la bonne.

Le 14/04/2016 à 10:17

Le problème venait effectivement de là.

Ma macro créait effectivement un nouveau classeur ("xlBook"), mais ce dans une nouvelle 'exécution' Excel :

Set xlApp = CreateObject("Excel.Application") ' Creation de l'objet Excel
Set xlApp.xlBook = Workbooks.Add ' Ajout d'un classeur
xlApp.Visible = True

C'est pourquoi la copie échouait.

J'ai supprimé la partie qui faisait défaut et n'ai gardé que :

Set xlBook = Workbooks.Add

Tout fonctionne bien à présent.

Merci encore pour l'aide apportée

Rechercher des sujets similaires à "copier feuilles classeur"