[VBA] Créer un nouveau classeur + copier coller feuilles d'un classeur dans le nouveau

**AlexisLe** · 11/11/2020, 20h22

Bonjour/Bonsoir à tous,

J'espère que vous allez bien et que le confinement n'est pas trop difficile. De mon côté, je passe plus temps à m'entrainer en VBA. C'est un bon point, non ?

Actuellement, je cherche à créer une macro me permettant de :

1) Créer un classeur
2) Copier des feuilles d'un classeur déjà ouvert
3) Coller les feuilles dans le nouveau classeur
4) Demander où enregistrer le nouveau document

J'ai essayé de développer quelque chose mais je bloque sur certains points. Je précise que je débute, cela fait maintenant 5-6 jours que j'ai commencé le VBA en "autoformation" donc ce n'est pas à la hauteur d'une personne expérimentée mais ça marche (de temps en temps

)
Voici les lignes de codes avec quelques annotations : :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Sub Newfichier()
 
Application.ScreenUpdating = False
 
' Créer le nouveau classeur
Workbooks.Add ' Problème : Le classeur s'appelle "Classeur (X)" avec X le nombre de classeur déjà créé par l'utilisateur
 
 
Windows("Fichier source.xlsm").Activate
 
Sheets("TCD_Caché1").Visible = True ' Rendre visible les feuilles TCD du fichier source
Sheets("TCD_Caché2").Visible = True ' Rendre visible les feuilles TCD du fichier source
Sheets("TCD_Caché3").Visible = True ' Rendre visible les feuilles TCD du fichier source
 
Sheets("Feuille1").Select ' Feuille n°1 à copier
Sheets("Feuille1").Copy Before:=Workbooks("Classeur6").Sheets(1)
Windows("Fichier source_Octobre.xlsm").Activate
Sheets("TCD_Caché1").Select ' TCD_Caché n°1 à copier
Sheets("TCD_Caché1").Copy Before:=Workbooks("Classeur6").Sheets(1)
 
Windows("Fichier source_Octobre.xlsm").Activate
Sheets("Feuille2").Select
Sheets("Feuille2").Copy Before:=Workbooks("Classeur6").Sheets(1)
Windows("Fichier source_Octobre.xlsm").Activate
Sheets("TCD_Caché2").Select ' TCD_Caché n°1 à copier
Sheets("TCD_Caché2").Copy Before:=Workbooks("Classeur6").Sheets(1)
 
Windows("Fichier source_Octobre.xlsm").Activate
Sheets("Feuille3").Select
Sheets("Feuille3").Copy Before:=Workbooks("Classeur6").Sheets(1)
Windows("Fichier source_Octobre.xlsm").Activate
Sheets("TCD_Caché3").Select
Sheets("TCD_Caché3").Copy Before:=Workbooks("Classeur6").Sheets(1)
 
Windows("Fichier source_Octobre.xlsm").Activate
 
Sheets("TCD_Caché1").Select
ActiveWindow.SelectedSheets.Visible = False ' Cache les feuilles TCD_Caché du fichier sources
Sheets("TCD_Caché2").Select
ActiveWindow.SelectedSheets.Visible = False ' Cache les feuilles TCD_Caché du fichier sources
Sheets("TCD_Caché3").Select
ActiveWindow.SelectedSheets.Visible = False ' Cache les feuilles TCD_Caché du fichier sources
 
 
Application.ScreenUpdating = True
 
 
 
Workbooks("Classeur6").Activate
Application.Dialogs(xlDialogSaveAs).Show 
 
 
 
End Sub

Mes problèmes :

1) Concernant les lignes pour coller les feuilles d'un classeur à un autre :

La destination s'appelle " Before:=Workbooks("ClasseurX") " . Cela veut dire que si je mets Classeur1 et qu'auparavant j'ai déjà ouvert 3 nouveaux classeurs, lorsque je vais appuyer sur le bouton de cette macro, elle va me créer un " classeur4 ". Donc si dans la macro je nomme le fichier "Classeur1", elle ne va pas vouloir fonctionner.
Est-ce qu'il y a un moyen de créer un fichier puis de lui donner directement un nom pour régler ce problème ?

2) Le nom du fichier source :

Le nom du classeur évolue de mois en mois. Au mois de Janvier, il va s'appeler, par exemple : "Fichier source_Janvier.xlsm". Puis, en février : " Fichier source_Février.xlsm " ...
Dans la macro, le nom du fichier source est fixe donc si je mets Janvier, ça restera "Fichier source_Janvier.xlsm" même si je vais février. Je dois donc modifier la macro chaque mois ...
Est-ce qu'il y a un moyen de ne pas citer le nom du classeur ? Par exemple : Application.ThisWorkbooks représente uniquement le fichier source ?

Voilà ... la macro fonctionne mais des petits détails comme le nom du fichier me dérangent ...
En tout cas, j'espère avoir été assez clair dans les explications.

Je vous remercie d'avance,

Alexis

**Pierre Fauconnier** · 11/11/2020, 21h10

Salut.

Pas sûr d'avoir tout compris. Basiquement, lorsque tu copies une feuille sans préciser sa destination, elle est copiée dans un nouveau classeur. Le code de base suivant permet de copier des feuilles dans un nouveau classeur et de fermer ce dernier en l'enregistrant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub Test()
  Dim s
 
  s = Array("2020_01", "2020_02", "2020_03")
  Sheets(s).Copy
  ActiveWorkbook.Close True, "c:\data\temp\Classeur_2020.xlsx"
End Sub

Le problème se situe donc à deux autres endroits:

Comment sélectionner les feuilles;
Comment déterminer le nom et l'emplacement du nouveau classeur.

C'est à toi à nous en dire plus au sujet de ces deux questions.

**AlexisLe** · 11/11/2020, 22h11

Bonsoir Pierre, bonsoir Thautheme,

@Pierre, ta solution convient à ce que je souhaite. Je ne pensais pas que l'on pouvait copier plusieurs feuilles en même temps et les coller dans un autre classeur. Je m'aperçois que "Array" permet de le faire.

Par contre, j'ai un message d'erreur avec ton code : Erreur d'exécution 9 : L'indice n'appartient pas... sur la ligne " Sheets(s).Copy ".

J'ai fait des recherches sur Internet concernant la fonction "Array" et j'ai adapté un code à mes besoins :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub Newfichier()
 
Application.ScreenUpdating = False
 
 
With ThisWorkbook
    .Sheets(Array("Feuille1", "Feuille2", "Feuille3", "Feuille4", "Feuille5", "Feuille6")).Copy
    ActiveWorkbook.Close
 
 
Application.ScreenUpdating = True
 
 
End With
 
End Sub

Pour répondre à ta question au sujet de l'emplacement du nouveau classeur, je voulais que la VBA me propose le menu "Enregistrer sous". Ensuite, c'est à moi de choisir l'endroit.
Le code ci-dessus me permet de le faire

Je te remercie, encore une fois, pour ton aide !

@Thautheme, Tu as raison, je n'ai pas expliqué clairement mon problème. En fait, il n'y a que 2 classeurs :

- 1 classeur source : Le nom de ce classeur change en fonction du mois. Le changement de nom est manuel -> je copie le classeur du mois précédent, je le colle dans le document du mois d'analyse et je le renomme.
Par exemple, je copie le classeur de Septembre, je le colle dans le fichier du mois d'octobre et je renomme le classeur avec "Octobre" à la fin. Ensuite, j'ajoute les données du mois d'Octobre (données que les RH m'envoient).

- 1 classeur destination : Fichier qui se créé automatiquement et qui reçoit 6 feuilles du classeur source grâce à la macro

Je suis désolé si les explications ne sont pas claires.

En tout cas merci à vous 2 pour votre aide !!

A bientôt !

**Thautheme** · 11/11/2020, 21h43

Bonsoir Alexis, Pierre, bonsoir le forum,

Ton code est très confus pour moi avec les classeurs Fichier Source.xlsm et Fichier Source_Octobre.xlsm. Si j'ai bien compris on peut simplifier comme ça (code à placer dans le classeur Fichier Source.xlsm):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub Newfichier()
Dim CS As Workbook 'déclare la variable CS (Classeur Source)
Dim CSM As Workbook 'déclare la variable CSM (Classeur Source Mois)
Dim CD As Workbook 'déclare la variable CD (Classeur Destination)
 
Application.ScreenUpdating = False
Set CS = ThisWorkbook 'definit le classeur source CS
Set CSM = Workbooks("Fichier source_Octobre.xlsm") 'definit le classeur source mois CSM
Set CD = Workbooks.Add 'définit le classeur destination en ouvrant un classeur vierge
CS.Sheets("TCD_Caché1").Visible = True ' Rend visible l'onglet "TCD_Caché1" du classeur source CS
CS.Sheets("TCD_Caché2").Visible = True ' Rend visible l'onglet "TCD_Caché2" du classeur source CS
CS.Sheets("TCD_Caché3").Visible = True ' ' Rend visible l'onglet "TCD_Caché1" du classeur source CS
 
CS.Sheets("Feuille1").Copy Before:=CD.Sheets(1) 'copie l'onglet "Feuille1" du classeur source CS avant le premier onglet du classeur destination
 
CSM.Sheets("Feuille2").Copy Before:=CD.Sheets(1) 'copie l'onglet "Feuille2" du classeur source mois CSM avant le premier onglet du classeur destination
CSM.Sheets("TCD_Caché2").Copy Before:=CD.Sheets(1) 'copie l'onglet "TCD_Caché2" du classeur source mois CSM avant le premier onglet du classeur destination
CSM.Sheets("Feuille3").Copy Before:=CD.Sheets(1) 'copie l'onglet "Feuille3" du classeur source mois CSM avant le premier onglet du classeur destination
CSM.Sheets("TCD_Caché3").Copy Before:=CD.Sheets(1)'copie l'onglet "TCD_Caché3" du classeur source mois CSM avant le premier onglet du classeur destination
 
CSM.Sheets("TCD_Caché1").Visible = False ' Cache l'onglet "TCD_Caché1" du classeur source mois CSM ? 
CSM.Sheets("TCD_Caché2").Visible = False ' Cache l'onglet "TCD_Caché2" du classeur source mois CSM ?
CSM.Sheets("TCD_Caché3").Visible = False ' Cache l'onglet "TCD_Caché2" du classeur source mois CSM ?
Application.ScreenUpdating = True
CD.Activate 'active l'onglet destination CD
Application.Dialogs(xlDialogSaveAs).Show
End Sub

**Pierre Fauconnier** · 12/11/2020, 07h18

L'erreur 9 apparait si un des noms passés dans l'array ne correspond à aucune feuille. Il faut être très attentif à ce niveau.

J'ose espérer que ton code est un exemple car nommer les feuilles feuill1, Feuill2 n'est pas très académique

Au delà de ces considérations, je me pose la question de toutes ces manipulations. Tu tagues Excel 2016, donc tu pourrais par exemple garder tes classeurs mensuels et les consolider dans un classeur de travail ou d'analyse grâce notamment à Power Query. Sans connaître mieux les finalités et l'organisation de tes classeurs, on ne saura pas mieux t'aider, mais je pense qu'il y a plus simple que ce que tu fais.