Résolu Macro Excel 2013 : Récupérer plusieurs feuilles dans une seule

[Shibanubis]

Bonjour à toi lecteur assidu porteur (je l'espère) d'une réponse salvatrice!

Je traine depuis quelques heures sur le net avec mes faibles notions en VB pour me sortir de mon pétrin :

J'ai un fichier excel avec 334 feuilles de 200 lignes chacune dont je dois vérifier si les valeurs sont en doublon ou non (oui c'est un amusement particulier).
Donc la première étape (que je vais utiliser avec une macro) est de prendre toutes les feuilles pour les réunir en une seule, puis de faire une mise en forme conditionnelle avec détection de doublons.
J'ai trouvé plusieurs codes qui me semblaient convenir pour finir sur celui qu'on trouve sur votre forum qui me semblait plus abordable

Code de base

Sub Macro1()

Dim ws As Worksheet
Dim ws_recap As Worksheet
Dim cell_source_1er As Range
Dim cell_source_der As Range
Dim cell_recap_cible As Range

Set ws_recap = Worksheets("recap")
    For Each ws In Worksheets
        If ws.Name <> ws_recap.Name Then
            Set cell_source_1er = ws.Range("A3")
            Set cell_source_der = cell_source_1er.SpecialCells(xlCellTypeLastCell)
            Set cell_recap_cible = ws_recap.Range("A65536").End(xlUp).Offset(1, 0)
            ws.Range(cell_source_1er, cell_source_der).Copy Destination:=cell_recap_cible
        End If
    Next
End Sub

Résultat :

Ce qui se passe c'est que la macro ne se décale pas après chaque copie, en gros je copie les 200 chiffres de la feuille 1 et le code ne déplace pas le curseur (n'est-ce pourtant pas le travail du paramètre offset?) ce qui fait que quand la macro colle les chiffres de la feuille 2, ils écrasent ceux qui étaient déjà là etc etc.

J'ai donc à la fin les 200 chiffres de la feuille 334 dans ma feuille nommée "Recap"

Comme je voulais comprendre j'ai codé pour qu'après chaque collage le curseur se déplace de 200 en 200 (oui c'est fait à la hussarde, mais c'était pour voir).

Sub Macro1()

Dim ws As Worksheet
Dim ws_recap As Worksheet
Dim cell_source_1er As Range
Dim cell_source_der As Range
Dim cell_recap_cible As Range
Dim compteur As Long
Dim i As Integer

i = 0
compteur = 0

Set ws_recap = Worksheets("recap")
    For Each ws In Worksheets
        If ws.Name <> ws_recap.Name Then
            compteur = 1 + 200 * i
            Set cell_source_1er = ws.Range("A3")
            Set cell_source_der = cell_source_1er.SpecialCells(xlCellTypeLastCell)
            Set cell_recap_cible = ws_recap.Range("A" & compteur)
        
            ws.Range(cell_source_1er, cell_source_der).Copy Destination:=cell_recap_cible
            i = i + 1
        End If
    Next
End Sub

Résultat :

Le curseur se décale bien mais à un moment de l’exécution du code je pars hors des limites de ma variable "compteur" et la macro s'arrête à la ligne 32800, je croyais pourtant que le type Long dépassait cette valeur?

Donc qu'est-ce qui se passe avec mon code? J'ai un problème de compatibilité avec excel 2013 et ses anciennes versions?

Merci de votre aide!

Edit : Rajout d'éléments informatif en italique (description de la manière de faire et rajout de la valeur de ligne maximale de la seconde macro).

 

[zeb]

Salut,

Il me semble très très bien ce premier bout de code.
Mais il est adapté à un Excel 32bits.
Pour qu'il soit idéal (toutes versions), il faut remplacer Range("A65536") par Cells(Rows.Count, 1).

Cells(Rows.Count, 1) descend à la dernière ligne de la feuille.
End(xpUp) remonte à la dernière ligne non vide (qu'il ne faut pas écraser).
Offset(1) redescends d'une ligne.

Et voilà comment l'on se retrouve sur la première ligne vide !

-----------------------------

Mais pourquoi cela ne marche-t-il pas chez toi ?
Peut-être que se fier à ce qui se trouve dans la colonne 1 n'est pas une bonne idée.

Et avec cette variation :

Set cell_source_1er = ws.Range("A3")
Set cell_source_der = cell_source_1er.SpecialCells(xlCellTypeLastCell)
Set cell_recap_cible = ws_recap.Cells(Rows.Count, 1).End(xlUp).Offset(10)

MsgBox "Copie de " & ws.Range(cell_source_1er, cell_source_der).Address(0, 0) & vbCrLf &
       "vers " & cell_recap_cible.Address(0, 0)

ws.Range(cell_source_1er, cell_source_der).Copy Destination:=cell_recap_cible

?

 

[Shibanubis]

Salut,

Il me semble très très bien ce premier bout de code.
Mais il est adapté à un Excel 32bits.
Pour qu'il soit idéal (toutes versions), il faut remplacer Range("A65536") par Cells(Rows.Count, 1).

Bonjour!

Tout d'abord merci de bien vouloir répondre, l'urgence est passée (finalement on a du faire les feuilles une par une ^^*) mais j'aimerai bien comprendre ce qu'il se passe pour que le code ne se décale pas comme il devrait.

Comme je l'ai dit je n'ai pas vraiment de mérite, j'ai trouvé ce code sur ce forum et je me demande d'ailleurs s'il n'est pas de toi

Cells(Rows.Count, 1) descend à la dernière ligne de la feuille.
End(xpUp) remonte à la dernière ligne non vide (qu'il ne faut pas écraser).
Offset(1) redescends d'une ligne.

Et voilà comment l'on se retrouve sur la première ligne vide !

Allright! Je comprend mieux comment ça fonctionne alors, merci de tes lumières.

Mais pourquoi cela ne marche-t-il pas chez toi ?
Peut-être que se fier à ce qui se trouve dans la colonne 1 n'est pas une bonne idée.

Humph, voilà à quoi ressemble une feuille de mon fichier excel....

Vous devez être connecté pour voir les images.

...
...
Je viens de comprendre, je suis un idiot.

C'est bien normal que la macro revienne à chaque fois sur la ligne de la cellule A3 si celle-ci est vide!
Si je met le paramètre

Set cell_source_1er = ws.Range("B3")

ça va marcher aux petits oignons, sauf que je n'aurai que les colonnes B et C de copiées...Ce qui n'est pas grave en soi, je vais chercher comment on pourrait faire pour prendre l'ensemble des colonnes jusqu'à la dernière colonne non vide

Et avec cette variation :

Set cell_source_1er = ws.Range("A3")
Set cell_source_der = cell_source_1er.SpecialCells(xlCellTypeLastCell)
Set cell_recap_cible = ws_recap.Cells(Rows.Count, 1).End(xlUp).Offset(10)

MsgBox "Copie de " & ws.Range(cell_source_1er, cell_source_der).Address(0, 0) & vbCrLf &
       "vers " & cell_recap_cible.Address(0, 0)

ws.Range(cell_source_1er, cell_source_der).Copy Destination:=cell_recap_cible

?

Mes réactions à ton code :

Set cell_recap_cible = ws_recap.Cells(Rows.Count, 1).End(xlUp).Offset(10)

offset(10)? C'est généreux un saut de 10 lignes, je pense que tu voulais dire offset(1, 0) ^^

Par contre mon excel n'apprécie pas du tout ton Rows.count, 1, il m'affiche ce message à chaque compilation :
"La méthode 'Range' de l'objet '_Worksheet' à échoué"
Ce qui n'arrive pas quand je le travaille à la barbare avec Range("A65536"), une dernière idée concernant ça?

EDIT J'ai rien dit!
C'est moi qui, ne voulant pas faire un bête copié collé, ait mal adapté le code que tu m'as donné!
J'avais mis Range(Rows.count ,1) au lieu de Cells(rows.count,1), my bad!

Tout marche donc très bien, merci beaucoup de ton aide!

 

[zeb]

Euh, il manquait une virgule. 10 -> 1,0...
En général, je ne donne pas de code tout fait, je le fais construire par le demandeur. Ainsi, il comprend bien au fur et à mesure.
Et si je lui fais comprendre qu'on traite la colonne 1 et qu'il n'a des données que dans la colonne 2, il adapte de lui-même.

Quant à savoir si le code est de moi, il y a de grande chance


Bon, ben j'ai mis 5 jours à te répondre, c'est dommage. T'as mis autant de temps à tout faire à la mimine.

N'hésite pas ni à revenir, ni à critiquer (objectivement) tout morceau de code qu'on te proposerait !

 

[Shibanubis]

Par rapport au temps que tu as mis, c'est pas grave du tout.
Merci au contraire, je cerne mieux le fonctionnement du code maintenant!

Je resterai dans les environs, ce forum va m'aider, je pense. =)