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

jeff1494
  • Question
XL 2021 Comment supprimer des noms du gestionnaire de noms dans un classeur
Réponses
7
Affichages
312
jeff1494
jeff1494
Salmander
  • Question
Autres Remplacer une zone de texte par un nom du gestionnaire de noms
Réponses
13
Affichages
716
Salmander
Salmander
S
  • Question
Microsoft 365 Liaison de données sur deux tableurs
Réponses
6
Affichages
311
JHA
J
M
  • Résolu(e)
XL 2021 Copier une liste de cellules vers un second tableau
Réponses
8
Affichages
113
MATLEA
M
dionys0s
  • Question
Microsoft 365 Curiosité : enregistrement impossible à cause de la liste des erreurs (#NUL!, #DIV/0!, #REF!, etc.)
Réponses
1
Affichages
368
Simply
Simply
C
  • Résolu(e)
XL 2019 Nombre de noms dans une liste
Réponses
5
Affichages
251
Kingwithou
K
M
  • Résolu(e)
XL 2016 #REF ! et nom de cellule existant sur cellules nommées
2
Réponses
16
Affichages
944
Mak_tarmak
M
O
  • Question
Microsoft 365 Problème complexe de recherche de valeurs
Réponses
6
Affichages
329
Olivier Corbin
O
CISCO
  • Résolu(e)
XL 2010 Domaine d'action des noms définis dans le gestionnaire de noms
Réponses
7
Affichages
613
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
484
Astyanax
A

Membres actuellement en ligne

7 membres et 41 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
315 204
Messages
2 117 263
Membres
113 070
dernier inscrit
bprun