VBA : Parcourir valeurs d'une plage nommée

Bonjour,

Dans une macro je cherche à parcourir les différentes valeurs d'une plage que j'ai nommé, seulement lorsque j'essaye la macro j'ai une erreur : "La méthode 'Range' de l'objet '_Global' a échoué". En cas de besoin voici la macro en question :

Sub test()
    For Each Ws In ThisWorkbook.Worksheets
        For Each c In Range("Instit")
            If Sheets("Ws").Range("A2").Value = c Then
                LastCell = Sheets("Ws").Range("A2").End(xlDown).Row
                Sheets("Ws").Range("A2:EK" & LastCell).Copy
                If Sheets("test").Range("A1").End(xlDown).Row = 1048576 Then
                    ToPaste = 2
                Else
                    ToPaste = Sheets("test").Range("A1").End(xlDown).Row + 1
                End If
                Sheets("test").Range("A" & ToPaste).Paste
            End If
        Next c
    Next Ws
End Sub

L'erreur a lieu sur le deuxième For. Quelqu'un sait où est mon erreur ?

Cordialement,

Nicolas

Note pour les utilisateurs de Power Query : en voyant le code vous comprenez que je cherche à coller conditionnellement des feuilles les unes à la suite des autres, ce qui se fait très bien en Power Query. Seulement mon document contient déjà 48 requêtes et tire ses données de 2 dossiers et Power Query est déjà fortement ralenti. Voilà pourquoi je cherche à passer par une macro à la place.

Bonjour,

Et si vous mettez ceci plutôt ?

For Each c In ws.Range("Instit")

Cordialement

Bonjour,
Question :
Pour quelles raisons les données ne sont-elles pas sous forme de tableaux structurés ?
Cdlt.

Bonjour Dan,

J'ai essayé mais ça ne marche pas. Par contre ça change l'erreur : pour

For Each c In Ws.Range("Instit")

On a "Erreur définie par l'application ou par l'objet" et pour

For Each c In Sheets("Ws").Range("Instit")

On a "L'indice n'appartient pas à la sélection". Si ça peut aider, La plage nommée est issue d'une formule :

=FILTRE(Fichiers[Code valeur];Fichiers[Libellé]="Temp")

et est étendue à tout le classeur.

Nicolas

Bonjour Jean-Eric,

Effectivement je ne l'ai pas précisé, mais les données sont bien dans des tableaux structurées et nommées (ils sont créés avec Power Query), désolé pour l'oubli.

Nicolas

Re,
Pour information :
Nouvelles propriétés VBA Excel 365

Cdlt.

On a "Erreur définie par l'application ou par l'objet" et pour......

Oui, j'avais d'ailleurs retiré cette proposition de mon post.

Effectivement je ne l'ai pas précisé, mais les données sont bien dans des tableaux structurées et nommées

Ah .. précision importante. Heureusement que Jean Eric a pensé à vous poser la question

Il faut revoir votre code en considérant les tableaux structurés. vous n'avez qu'un tableau structuré dans chaque feuille WS ?
Et aussi votre Range("Instit") est dans quelle colonne du tableau structuré : la colonne 1, 2, 3 ??

Sur 1 feuille il n'y a pas de tableau (je m'en sert pour y laisser quelques remarques et notes, elle peut être supprimé sans problème) et sur une feuille il y plusieurs tableaux structurés qui me servent d'attributs pour chercher et modifier certaines valeurs.

Autrement toutes les autres feuilles se composent d'un unique tableau structuré.

Autrement toutes les autres feuilles se composent d'un unique tableau structuré.

Donc votre code ne concerne que ces autres feuilles ?
Vous comparez la variable c avec l'info en A2 et si égal vous copiez A2:EK pour le coller plus bas

Ce serait bien de voir votre fichier (sans données confidentielles) ou un fichier modèle pour mieux comprendre

J'ai fait au mieux, voila ce que je peux vous proposer en simplifiant au maximum. Les lignes et colonnes sur lesquelles commencent les tableaux sont les vrais, d'où l'idée de faire le test sur A2 comme je sais qu'il ne contiendra les valeurs requises uniquement sur les bonnes feuilles.

EDIT: J'ai contourné le problème en affichant explicitement la plage nommée dans les attributs, la macro devient

Sub test()
    Fin = Sheets("Attributs").Range("H24").End(xlDown).Row
    For Each Ws In ThisWorkbook.Worksheets
        For c = 24 To Fin
            Code = Sheets("Attributs").Range("H" & c).Value
            If Sheets(Ws.Name).Range("A2").Value = Code Then
                LastCell = Sheets(Ws.Name).Range("A2").End(xlDown).Row
                Sheets(Ws.Name).Range("A2:EK" & LastCell).Copy
                If Sheets("test").Range("A1").End(xlDown).Row = 1048576 Then
                    ToPaste = 2
                Else
                    ToPaste = Sheets("test").Range("A1").End(xlDown).Row + 1
                End If
                Sheets("test").Range("A" & ToPaste).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            End If
        Next c
    Next Ws
End Sub

Et ça marche, merci tout de même pour le temps que vous m'avez accordé !

Nicolas

Re

d'où l'idée de faire le test sur A2

Ok mais au vu du fichier que vous avez posté je n'ai toujours pas compris ce que vous voulez faire exactement
Serait-ce de parcourir les lignes des feuilles TRP et de recopier dans la feuille Objectif ?

J'ai l'impression que vous faites des "tours" pour rien

Sinon, merci de cloturer votre fil

Cordialement

Serait-ce de parcourir les lignes des feuilles TRP et de recopier dans la feuille Objectif ?

C'est ça, mais uniquement celles qui ont en A2 un code qui appartient à la plage nommée. Donc effectivement on fait pleins de tours pour rien, vu qu'on va tester 30 codes par feuilles mais qu'un seul (ou zéro) ne sera le bon.

Question subsidiaire : comment on clôture le fil si la réponse adaptée vient de celui qui a posé la question ? Je ne vois pas de validation à côté de mon message

comment on clôture le fil si la réponse adaptée vient de celui qui a posé la question ? Je ne vois pas de validation à côté de mon message

Bizarre votre question parce que dans votre profil je vois que tout vos fils sont cloturés d'habitude...
Donc c'est le petit bouton "v" est dans la barre de menu à droite

mais uniquement celles qui ont en A2 un code qui appartient à la plage nommée. Donc effectivement on fait pleins de tours pour rien, vu qu'on va tester 30 codes par feuilles mais qu'un seul (ou zéro) ne sera le bon.

le souci c'est que votre fichier ne correspond pas au code posté. Là vous mettez H24.
Puis A2 de quelle feuille ?
Si vous voulez que je regarde, donnez moi un exemple concret par rapport au fichier exemple posté