Copie de feuille dans le nom contenu ds une liste déroulante

Bonjour à tous,

voilà, je suis encore débutante en VBA et je suis en mission de création d'un nouveau outil de reporting dans mon stage.

Y'a t-il une ame charitable qui pourrait me fournir un code ou des indications pour avoir ceci :

Dans un classeur, j'ai une feuille "Data" et une feuille "Dashbord". Dans un autre classeur nommé "Historique" j'ai des feuilles nommées par mois (exp : Janvier-15, Février-15...) contenant les données. Sur la feuille "Dashbord", je veux créer une zone de liste déroulante (contrôle formulaire). Ce que j'aimerai avoir c'est à chaque fois qu'on choisit le mois (exp: Février-15), la macro aille copier la feuille "Février-15" du classeur "Historique" et vient la coller dans la feuille "Data" qui alimente mon tableau de bord.

Merci d'avance à ceux qui prendront le temps de lire mon problème, et surtout, d'y répondre.

Bonsoir Youssy, bonsoir le forum,

Comme tu n'as pas daigné fournir les fichiers exemple il a fallu tout faire pour toi ! Mais vous êtes tellement nombreux dans ce cas que j'ai pris l'habitude et quand je suis bien luné, ça donne...

En pièce jointe le fichier Cible, donc celui qui reçoit les données du fichier source Historique.xlsx

Voici le fonctionnement :

• Ouvre le fichier cible (je l'ai nommé Cible.xlsm, tu adapteras à ton cas)

À l'ouverture de ce classeur :

• Historique.xlsx est ouvert automatiquement

• La liste de ses onglets sert de liste à la validation de données créée automatiquement dans la cellule A1 de l'onglet Dashbord

• L'onglet Dashbord du classeur cible est affiché avec A1 comme cellule active...

Au changement dans la cette cellule active A1 :

• Si tu veux d'abord supprimer d'éventuelles anciennes données de l'onglet Data, commence par effacer A1 avec le touche [Delete] (désolé j'ai un clavier anglais...)

• Sélectionne l'onglet (du classeur Historique) dont tu veux récupérer les donnés dans la liste de A1

• Si il existe déjà des anciennes données dans Data tu auras un message d'avertissement

• Si Oui au message les anciennes données sont écrasées par les nouvelles
• Si Non, rien ne se passe...

À la fermeture du classeur cible :

• Le classeur Historique est fermé automatiquement. Attention ! sans enregistrer les changements, pense à la faire avant, si il y a eu des modifications.

Qu'est ce qui pourrait planter ? j'ai testé chez moi et ça tourne mais il faut respecter deux conditions :

• Les deux classeurs doivent se trouver dans le même dossier

• J'ai attribué à la constante NCS la valeur "Historique.xlsx". Si ton fichier a une autre extension .xls ou .xlsm, il te faut absolument modifier cette constante ! Elle se trouve dans le composant ThisWorkbook, ligne 5.

• Il est préférable d'ouvrir le classeur Cible en premier (puisqu'il ouvre l'autre automatiquement, mais si tu fais l'inverse ça marche aussi).

Si tu veux récupérer le code pour le mettre dans ton classeur à toi (tu sais, celui que tu aurais dû fournir...) il te faut aller à trois endroits différents :

• Le composant ThisWorkbook qui contient :

Option Base 1 'définit la limite inférieure des tableaux (1 au lieu de 0)

Private Sub Workbook_Open() 'à l'ouverture du Classeur
Dim CH As String 'déclare la variable CH (Chemin d'accès)
Const NCS As String = "Historique.xlsx" 'déclare la constante NCS (Nom du Classeur Source) - Extension à adapter à ton cas
Dim I As Byte 'déclare la variable I (Incrément)
Dim TOS() As String 'déclare la variable TOS (Tableau des Onglets Source)

'************************************************************************************************************************
'à l'ouverture de ce fichier le classeur "Historique" est lui aussi ouvert automatiquement, une validation de données est
'alors créée automatiquement dans la cellule A1 de l'onglet "DashBord" avec la liste des onglets du classeur "Historique"
'************************************************************************************************************************

Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set CC = ThisWorkbook 'définit le classeur cible CC (variable déclarée publique dans le Module1)
CH = CC.Path & "\" 'définit le chemin d'accès CH
Set OC = CC.Sheets("Data") 'définit l'onglet cible OC (variable déclarée publique dans le Module1)
On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
Set CS = Workbooks(NCS) 'définit le classeur source CS (génère une erreur si ce classeur n'est pas ouvert)
If Err <> 0 Then 'condition : si une erreur a été générée
    Err = 0 'annule l'erreur
    Workbooks.Open (CH & NCS) 'ouvre le classeur NCS
    Set CS = ActiveWorkbook 'définit la classeur source CS (variable déclarée publique dans le Module1)
End If 'fin de la condition
On Error GoTo 0 'annule la gestion des erreurs
For I = 1 To CS.Sheets.Count 'boucle sur tous les onglets du classeur source
    ReDim Preserve TOS(I) 'redimensionne le tableau des onglets source TOS
    TOS(I) = " " & CS.Sheets(I).Name 'définit la variable indéxée I du tableau des onglets source TOS
    'j'ai rajouté un espace devant car sinon la valeur de la celluile A1 est apparentée à une date...
Next I 'prochain onglet de la boucle
'prend en compte la validation de données de cellule A1 de l'onglet "Dashbord" du classeur cible CC
With CC.Sheets("Dashbord").Range("A1").Validation
    .Delete 'efface une éventuelle validation existante
    'définit la validation de données avec le tableau TOS comme liste
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(TOS, ",")
End With 'fin de la prise en compte de...
CC.Activate
CC.Sheets("Dashbord").Activate
Range("A1").Select
Application.ScreenUpdating = True 'Affiche les rafraîchissements d'écran
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'à la fermeture de ce fichier
On Error Resume Next 'gestion des erreur (en cas d'erreur passe à la ligne suivante)
CS.Close SaveChanges:=False 'ferme le classeur source CS sans enregister les changement
End Sub

• Le Module1 qui ne contient que les variable publiques :

Public CC As Workbook 'déclare la variable CC (Classeur Cible)
Public OC As Worksheet 'déclare la variable OS (Onglet Cible)
Public CS As Workbook 'déclare la variable CS (Classeur Source)
Public OS As Worksheet 'déclare la variable OS (Onglet Source)

Le composant Feuil2(Dashbord) qui contient :

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim NOR As String 'déclare la variable NOR (Nom de l'Onglet de Référence)

If Target.Cells.Count > 1 Then Exit Sub 'si plusieurs celllules sélectionnées, sort de la procédure
'limite l'action à la cellule A1 de cet onglet (=> Target ne peut plus désormais être que la cellule A1)
If Target.Address <> "$A$1" Then Exit Sub 'si le changement a lieu ailleurs qu'en A1, sort de la procédure
'si A1 est effacée, efface alors le contenu de l'onglet cible et sort de la procédure
If Target.Value = "" Then OC.Cells.ClearContents: Exit Sub
NOR = Replace(Target.Value, " ", "") 'définit le nom de l'onglet de référence NOR en supprimant l'espace devant
Set OS = CS.Sheets(NOR) 'définit l'onglet source OS (variable déclarée publique dans le Module1)
If OC.Range("A1").Value <> "" Then 'condition : si la cellule A1 de l'onglet cible OC n'est pas vide
    'si "Non" au message, sort de la procédure
    If MsgBox("Les anciennes données de l'onglet Data vont être effacées ! Voulez-vous continuer ?", vbYesNo, "ATTENTION") = vbNo Then Exit Sub
End If 'fin de la condition
OS.Cells.Copy OC.Range("A1") 'copie les données de l'onglet source OS et les colle dans la cellule A1 de l'ongle cible OC
CC.Activate 'active le classeur cible CC
OC.Activate 'active l'onglet cible OC
End Sub

Et les coller aux mêmes endroits (adapté à ton fichier pour l'onglet Dashbord) dans ton classeur...

Ouf, j'ai fini...

Ha non m..., le fichier :

bonjour ThauThème,

Merci beaucoup pour ta réponse. Ton code fait exactement ce que je voulais.

j'ai juste remarqué une chose et voudrais plus d'explications. En fait, en plaçant le classeur cible dans différents dossiers qui contiennent un classeur "Historique", la liste déroulante de "cible" s'adapte au nom de feuilles que j'ai sur "Historique". càd par exemple si je place "Cible" dans un dossier avec une classeur "Historique" contenant 3 feuilles : "x","y,"z" , j'aurai sur ma liste déroulante : x, y, z et que si je mets le classeur"cible" dans un autre dossier avec un classeur "historique" comportant 2 feuilles "a" et "b", j'aurai sur ma liste déroulante : a, b. Je trouve cela très pratique. je voudrais savoir est-ce que ceci est du à une façon dont t'as définit ta liste déroulante (validation de données par exemple) ou c'est le code que tu m'as fournit qui en est responsable?

Merci

Bonjour ThauThème,

en fait, si j'ajoute une nouvelle feuille (mai-15 par exemple) à mon classeur "Historique", et que je clique dans ma liste déroulante sur "mai-15" ça ne s'execute pas. Un message s'affiche m'indiquant : La méthode 'Copy' de l'objet 'Range' a échoué.

Un problème à ce niveau

OS.Cells.Copy OC.Range("A1")

En effet, les feuilles de mon classeur "Historique" sont crées automatiquement chaque mois à l'aide d'une autre macro à partir d'un autre classeur. Y'a t-il une solution pour régler ce problème?

Merci par avance

Bonsoir Youssy, bonsoir le forum,

Question Nº 1 :

C'est le code qui fait qu'à l'ouverture il crée la liste de validation avec les onglets du classeur Historique

Question Nº 2 :

Chez moi ça marche mais on a peut-être pas utiliser la même procédure.

• J'ouvre Historique en premier

• je rajoute un onglet

• j'ouvre Cible

• Je sélectionne le nouvel onglet dans A1

ça marche !

• J'ouvre Cible en premier,

• je vais dans Historique

• Je rajoute un onglet

L'onglet n'apparaît pas dans la liste de A1 et je ne peux avoir l'erreur que tu signales.

Je vois que mes commentaires humoristiques sur la flemme dont tu fais preuve pour nous fournir les fichiers qui permettent de t'aider n'ont pas porté leur fruit... Si tu ne fais pas d'effort je ne compte plus en faire non plus...