XL 2016 Référence Gestionnaire de noms

L

loicoss

XLDnaute Junior
Bonjour le Forum,

Je travaille avec 2 fichiers Excel.
Le premier est une base de données (Données.xlsx) avec 2 onglets (onglet1 ; onglet2).
Mon second fichier sert a traiter les données (Export.xslm).

Dans ce second j'ai une macro pour copier et coller les 2 onglets du fichier (Donnée.xlsx).
Or pour chaque import, j'efface les onglets d'origines pour mettre les nouveaux à jour.

Dans mon fichier export, dans le gestionnaire de noms, j'ai 2 noms qui font références à ces onglets.

Le problème est qu'à chaque fois que j'actualise l'import des ces onglets la référence nom change pour devenir #REF!

1613487866485.png


Ma question, est-il possible de verrouiller le nom d'un onglet dans la référence d'un nom, dans le gestionnaire de nom ?

J'espère avoir été le plus clair possible.

En vous remerciant par avance.
 
Solution
job75
Cela dit on peut faire un copier-coller complet, les noms définis restent intacts :
VB: 
Private Sub CommandButton1_Click()
Dim chemin$, fichier$, n%, nn%, i%
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Données.xlsx" 'à adapter
If Dir(chemin & fichier) = "" Then MsgBox "Fichier '" & fichier & "' introuvable !", 48: Exit Sub
Application.ScreenUpdating = False
With Workbooks.Open(chemin & fichier) 'ouverture du fichier source
    n = .Worksheets.Count
    nn = ThisWorkbook.Worksheets.Count - 1
    If n > nn Then MsgBox n - nn & " feuille(s) source(s) non copiée(s)..."
    For i = 1 To Application.Min(n, nn)
        ThisWorkbook.Worksheets(i + 1).Cells.Clear 'RAZ
        .Worksheets(i).UsedRange.Copy ThisWorkbook.Worksheets(i +...
Trier par date Trier par votes
Jacky67

Jacky67

XLDnaute Barbatruc
loicoss à dit:
Bonjour le Forum,

Je travaille avec 2 fichiers Excel.
Le premier est une base de données (Données.xlsx) avec 2 onglets (onglet1 ; onglet2).
Mon second fichier sert a traiter les données (Export.xslm).

Dans ce second j'ai une macro pour copier et coller les 2 onglets du fichier (Donnée.xlsx).
Or pour chaque import, j'efface les onglets d'origines pour mettre les nouveaux à jour.

Dans mon fichier export, dans le gestionnaire de noms, j'ai 2 noms qui font références à ces onglets.

Le problème est qu'à chaque fois que j'actualise l'import des ces onglets la référence nom change pour devenir #REF!

Regarde la pièce jointe 1095811

Ma question, est-il possible de verrouiller le nom d'un onglet dans la référence d'un nom, dans le gestionnaire de nom ?

J'espère avoir été le plus clair possible.

En vous remerciant par avance.
Cliquez pour agrandir...
Bonjour,
Dans la macro d'import des onglets, redéfinir les noms par VBA
Les codes devraient ressembler à ceci
En adaptant "Lenom" et le nom des feuilles
VB: 
ActiveWorkbook.Names.Add Name:="Lenom", RefersTo:="=OFFSET(Feuil1!$A$2,,,COUNTA(Feuil1!$A:$A)-1)"

OU ne pas copier les onglets, mais seulement leurs contenus.
 
Dernière édition:
Vote positif 0 Vote négatif
L

loicoss

XLDnaute Junior
Bonjour Jacky et merci pour votre réponse.

La macro que j'utilise est la suivante :
VB: 
Private Sub Workbook_open()
Dim CD As Workbook 'déclare la variable CD (Classeur Destination)
Dim CA As String 'déclare la variable CA (Chemin d'Accès)
Dim CS As Workbook 'déclare la variable CS (Classeur Source)
Dim O As Worksheet 'déclare la variable O (Onglet)

Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set CD = ThisWorkbook 'définit le classeur destination CD
Application.DisplayAlerts = False 'masque les messages d'Excel
For Each O In CD.Sheets 'boucle sur tous les onglets O du classeur destination
    If O.Name <> "PRDE" Then O.Delete 'si le nom de l'onglet n'est pas "PRDE", supprime l'onglet
Next O 'prochain onglet de la boucle
Application.DisplayAlerts = True 'affiche les messages d'Excel
CA = CD.Path & "\" 'définit le chemin d'accès CA
On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligbe suivante)
Set CS = Workbooks("Données.xlsx") '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.Clear 'supprime l'erreur
    Set CS = Workbooks.Open(CA & "Données.xlsx") 'définit le classeur source en l'ouvrant
End If 'fin de la condition
On Error GoTo 0 'annule la gestion des erreurs
For Each O In CS.Sheets 'boucle sur tous les onglets O du classeur source
    O.Copy After:=CD.Sheets(CD.Sheets.Count) 'copy l'onglet de la boucle en dernière position dans le classeur destintion
Next O 'prochain onglet de la boucle
CD.Save 'sauve le classeur destination
CS.Close False 'ferme le classeur source sans enregistrer
    Sheets("PRDE").Select
    Range("A4").Select
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
End Sub

Effectivement, copier seulement le contenu me conviendrai.
Comment dois-je modifier ce code pour copier uniquement le contenu des onglets et non les onglets complets ?

Ou sinon le modifier avec les code vba ?

En vous remerciant par avance.
 
Vote positif 0 Vote négatif
Jacky67

Jacky67

XLDnaute Barbatruc
loicoss à dit:
Bonjour Jacky et merci pour votre réponse.

La macro que j'utilise est la suivante :
VB: 
Private Sub Workbook_open()
Dim CD As Workbook 'déclare la variable CD (Classeur Destination)
Dim CA As String 'déclare la variable CA (Chemin d'Accès)
Dim CS As Workbook 'déclare la variable CS (Classeur Source)
Dim O As Worksheet 'déclare la variable O (Onglet)

Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set CD = ThisWorkbook 'définit le classeur destination CD
Application.DisplayAlerts = False 'masque les messages d'Excel
For Each O In CD.Sheets 'boucle sur tous les onglets O du classeur destination
    If O.Name <> "PRDE" Then O.Delete 'si le nom de l'onglet n'est pas "PRDE", supprime l'onglet
Next O 'prochain onglet de la boucle
Application.DisplayAlerts = True 'affiche les messages d'Excel
CA = CD.Path & "\" 'définit le chemin d'accès CA
On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligbe suivante)
Set CS = Workbooks("Données.xlsx") '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.Clear 'supprime l'erreur
    Set CS = Workbooks.Open(CA & "Données.xlsx") 'définit le classeur source en l'ouvrant
End If 'fin de la condition
On Error GoTo 0 'annule la gestion des erreurs
For Each O In CS.Sheets 'boucle sur tous les onglets O du classeur source
    O.Copy After:=CD.Sheets(CD.Sheets.Count) 'copy l'onglet de la boucle en dernière position dans le classeur destintion
Next O 'prochain onglet de la boucle
CD.Save 'sauve le classeur destination
CS.Close False 'ferme le classeur source sans enregistrer
    Sheets("PRDE").Select
    Range("A4").Select
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
End Sub

Effectivement, copier seulement le contenu me conviendrai.
Comment dois-je modifier ce code pour copier uniquement le contenu des onglets et non les onglets complets ?

Ou sinon le modifier avec les code vba ?

En vous remerciant par avance.
Cliquez pour agrandir...
RE..
Il faudrait le classeur contenant les noms définis.
Celui ou les références changent
1613496731145.png

Mais avec les noms d'onglet
**Pour ne copier que le contenu, il faut être certain que le nombre et le nom des onglets soient toujours identique dans les 2 classeurs
 
Dernière édition:
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Bonsoir loicoss, Jacky67,

En utilisant les fichiers du fil précédent :
VB: 
Private Sub CommandButton1_Click()
Dim chemin$, fichier$, n%, nn%, i%
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Données.xlsx" 'à adapter
If Dir(chemin & fichier) = "" Then MsgBox "Fichier '" & fichier & "' introuvable !", 48: Exit Sub
Application.ScreenUpdating = False
With Workbooks.Open(chemin & fichier) 'ouverture du fichier source
    n = .Worksheets.Count
    nn = ThisWorkbook.Worksheets.Count - 1
    If n > nn Then MsgBox n - nn & " feuille(s) source(s) non copiée(s)..."
    For i = 1 To Application.Min(n, nn)
        ThisWorkbook.Worksheets(i + 1).UsedRange.ClearContents 'RAZ
        With .Worksheets(i).UsedRange
            ThisWorkbook.Worksheets(i + 1).Cells(1).Resize(.Rows.Count, .Columns.Count) = .Value 'colle les valeurs
        End With
    Next
    .Close False 'fermeture du fichier source
End With
End Sub
A+
 

Pièces jointes

  • Export(1).xlsm
    23.4 KB · Affichages: 10
  • Données.xlsx
    11.8 KB · Affichages: 5
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Cela dit on peut faire un copier-coller complet, les noms définis restent intacts :
VB: 
Private Sub CommandButton1_Click()
Dim chemin$, fichier$, n%, nn%, i%
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Données.xlsx" 'à adapter
If Dir(chemin & fichier) = "" Then MsgBox "Fichier '" & fichier & "' introuvable !", 48: Exit Sub
Application.ScreenUpdating = False
With Workbooks.Open(chemin & fichier) 'ouverture du fichier source
    n = .Worksheets.Count
    nn = ThisWorkbook.Worksheets.Count - 1
    If n > nn Then MsgBox n - nn & " feuille(s) source(s) non copiée(s)..."
    For i = 1 To Application.Min(n, nn)
        ThisWorkbook.Worksheets(i + 1).Cells.Clear 'RAZ
        .Worksheets(i).UsedRange.Copy ThisWorkbook.Worksheets(i + 1).Cells(1) 'copier-coller
    Next
    .Close False 'fermeture du fichier source
End With
End Sub
Fichier (2), les tableaux sources sont des tableaux structurés, leurs copies sont aussi des tableaux structurés.
 

Pièces jointes

  • Export(2).xlsm
    23.9 KB · Affichages: 7
  • Données.xlsx
    12.3 KB · Affichages: 4
Vote positif 0 Vote négatif
Solution
Jacky67

Jacky67

XLDnaute Barbatruc
job75 à dit:
Cela dit on peut faire un copier-coller complet, les noms définis restent intacts :
VB: 
Private Sub CommandButton1_Click()
Dim chemin$, fichier$, n%, nn%, i%
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Données.xlsx" 'à adapter
If Dir(chemin & fichier) = "" Then MsgBox "Fichier '" & fichier & "' introuvable !", 48: Exit Sub
Application.ScreenUpdating = False
With Workbooks.Open(chemin & fichier) 'ouverture du fichier source
    n = .Worksheets.Count
    nn = ThisWorkbook.Worksheets.Count - 1
    If n > nn Then MsgBox n - nn & " feuille(s) source(s) non copiée(s)..."
    For i = 1 To Application.Min(n, nn)
        ThisWorkbook.Worksheets(i + 1).Cells.Clear 'RAZ
        .Worksheets(i).UsedRange.Copy ThisWorkbook.Worksheets(i + 1).Cells(1) 'copier-coller
    Next
    .Close False 'fermeture du fichier source
End With
End Sub
Fichier (2), les tableaux sources sont des tableaux structurés, leurs copies sont aussi des tableaux structurés.
Cliquez pour agrandir...
Hello job75
C'est cela que j'avais l'intention de proposer, mais je n'ai pas été jusqu'à visiter les posts précédents.o_O;)
C'est le faites de virer les onglets qui fout la pagaille dans les plages nommées
If O.Name <> "PRDE" Then O.Delete:mad:

Bonne nuit
 
Vote positif 0 Vote négatif

Discussions similaires

Salmander
  • Question
Autres Remplacer une zone de texte par un nom du gestionnaire de noms
Réponses
9
Affichages
227
Staple1600
Staple1600
M
  • Résolu(e)
XL 2016 #REF ! et nom de cellule existant sur cellules nommées
2
Réponses
16
Affichages
499
Mak_tarmak
M
CISCO
  • Résolu(e)
XL 2010 Domaine d'action des noms définis dans le gestionnaire de noms
Réponses
7
Affichages
385
CISCO
CISCO
A
  • Question
XL 2021 Cellules Excel : Basculer de cellules référencées en A1 à cellules identifiées par noms
Réponses
5
Affichages
235
Astyanax
A
RDBAL41
  • Résolu(e)
XL 2021 Gestionnaire de noms
Réponses
12
Affichages
450
RDBAL41
RDBAL41
KTM
  • Question
XL 2016 Nettoyer le gestionnaire de Noms
Réponses
11
Affichages
460
cp4
cp4
C
  • Question
Microsoft 365 Transposer des valeurs en 2 colonnes en fonction d'une référence produit
Réponses
9
Affichages
303
Cagnet
C
FRYS
  • Résolu(e)
XL 2013 Netoyer la liste de noms du gestionnaire de noms
Réponses
5
Affichages
685
FRYS
FRYS
Cousinhub
  • Question
Power Query Source d'une requête dans le gestionnaire de noms
Réponses
5
Affichages
691
job75
job75
Julie-F
  • Question
XL 2016 Base puis création de fichiers multiples sous une présentation similaire
Réponses
5
Affichages
352
Julie-F
Julie-F

Membres actuellement en ligne

12 membres et 292 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 198
Messages
2 086 149
Membres
103 133
dernier inscrit
mtq