Microsoft 365 Copier depuis plusieurs pages d'un fichier une plage de colonnes entre deux cellules comportant un texte particulier

[softy69]

Bonjour à tous,

Je me lance dans un projet de simplification de process Excel en passant par les macros.

Mise en contexte :
Je dispose d'un fichier de X feuilles, sur chaque feuille la partie qui m'intéresse est celle de droite (les 6 dernières colonnes remplies). Je voudrais trouver le code pour identifier cette zone pour chaque feuille. Mon but, une fois cette étape franchie, est de copier cette zone (depuis chaque feuille) et les coller les une en dessous des autres dans une feuille vierge préalablement créée.

Problèmes que je rencontre :
- "Les 6 dernières colonnes" n'ont pas la même adresse sur toutes les feuilles.
- La première ligne des 6 colonnes est une cellule fusionnée avec les 6 colonnes. Cette cellule est non importante à copier.
==> la deuxième ligne des 6 colonnes est une ligne d'en-tête, contenant dans chaque cellule que du texte et ces cellules sont identiques sur toutes les feuilles. (Je me disais qu’utiliser cette plage de valeur en référence serait utile).

Je peux vous joindre un extrait de mon fichier anonymisé et réduit à 3 feuilles pour expliciter mon texte si le projet vous tente. Je profite pour vous dire que le nombre de feuilles peut atteindre 200/250.

Merci d'avance pour votre aide et/ou propositions.

 

[softy69]

Bonjour a tous,

Après vérification ce qui pose problème maintenant c'est les cellules fusionnée et les lignes blanches, comme l'a souligné @soan .

Je suis à la recherche de ligne de code qu'on pourrait utiliser, pour dire :
"sur chaque feuille de ce classeur
sélectionner toutes les cellules de la feuille
si la cellule est fusionnée alors defusionner
cellule suivante
feuilles suivant"

À la suite de ce code je viendrai supprimer les ligne blanches et dérouler la sélection de zones à copier/coller et le faire sur la feuille PASTE précédemment créé.

Merci pour votre aide

 

[soan]

@softy69

Ouvre le fichier ci-joint, et fais Ctrl d

(la nouvelle macro est dans Module3)

soan

 

[softy69]

Wow quelle réactivité !

Merci a toi, ca fonctionne tres bien. En revanche quand je l'integre avec les autres operations je me rends compte que c'est la fonction "suprimer les ligne blanches de toutes les feuilles" qui cloche... .

Est-ce que tu aurais une idee ?

sub cleaning()
Dim P As Long
Dim Ws As Worksheet
Dim sh As Worksheet

On Error Resume Next
Application.ScreenUpdating = False
'Application.DisplayAlerts = False

    For Each Ws In Application.Worksheets
        If Application.WorksheetFunction.CountA(Ws.UsedRange) = 0 Then
         Ws.Delete
        End If
    Next
Application.ScreenUpdating = True
'Application.DisplayAlerts = True


Application.ScreenUpdating = False
    For Each sh In Worksheets: sh.Cells.UnMerge:
    Next
Application.ScreenUpdating = True

    For Each Ws In Worksheets: Ws
    For P = Range("A65536").End(xlUp).Row To 1 Step -1
        If Application.CountA(Rows(P)) = 0 Then Rows(P).Delete Shift:=xlUp

    Next
    Next
    
end sub

 

[soan]

Voici ton code corrigé et optimisé (ça a été très long à faire !) :

Option Explicit

Sub cleaning()
  On Error Resume Next
  Dim Ws As Worksheet, lig&, k&, n%, i%: n = Worksheets.Count
  Application.ScreenUpdating = 0: Application.DisplayAlerts = 0
  For Each Ws In Application.Worksheets
    If Application.WorksheetFunction.CountA(Ws.UsedRange) = 0 Then Ws.Delete
  Next Ws
  For i = 1 To n
    With Worksheets(i)
      .Cells.UnMerge: k = .[A65536].End(3).Row
      For lig = k To 1 Step -1
        If Application.CountA(.Rows(lig)) = 0 Then .Rows(lig).Delete 3
      Next lig
    End With
  Next i
  Application.DisplayAlerts = -1
End Sub

Attention : pour le 1er .CountA c'est ok ; pour le 2ème, même si la compilation est ok,
je ne suis pas sûr que ton Application.CountA() soit correct à l'exécution ; ce sera
peut-être plutôt : Application.WorksheetFunction.CountA(.Rows(lig))

Note que maintenant, il y a 2 boucles For au lieu de 3 ; d'autre part, avec ton code VBA,
des opérations ne se faisaient que sur la feuille active car tu avais oublié de préfixer
avec Ws (dans la 3ème boucle For).

soan

 

[soan]

Edit n° 1 : attention, n'utilise pas le code VBA ci-dessus ; j'ai fait une très grosse erreur !
attends un moment, je vais la corriger.

Edit n° 2 : dans le post #19 ci-dessus, j'ai remplacé mon ancien code VBA (qui avait
un très gros bug) par le bon code VBA ; maintenant, c'est OK.

 

[soan]

@softy69

C'est ok, j'ai corrigé ; j'avais fait une très grosse erreur en mettant la suppression
d'une feuille dans la même boucle que celle des autres opérations, car j'avais
oublié que ça « dérègle » le compteur i de la boucle For i = 1 To n ; donc
il faut 2 boucles en tout, pas qu'une seule.

Dans le post #19 précédent :

* je n'ai pas laissé le mauvais code VBA, car ce n'était absolument pas un bon exemple
à suivre, et ça aurait pu induire en erreur certains lecteurs du forum ; aussi, je l'ai
remplacé par le bon code VBA (correction de l'ancien).

* ma remarque pour le .CountA() est toujours valable : c'est à vérifier !

soan

 

[soan]

@softy69

* relis d'abord tout mon post #21

* relis le dernier paragraphe du post #19, car je l'ai modifié

* tu peux essayer le code VBA du post #19, mais reste
prudent par rapport au 2ème .CountA() !

soan

 

[softy69]

@softy69

* relis d'abord tout mon post #21

* relis le dernier paragraphe du post #19, car je l'ai modifié

* tu peux essayer le code VBA du post #19, mais reste
prudent par rapport au 2ème .CountA() !

soan

je suis en train ... je reviens à toi avec des nouvelles, je te remercie énormément !

 

[softy69]

Merci @soan , c'est net on y voit plus clair.

Le 2ᵉ .CountA() est bien à remplacer par :

 Application.WorksheetFunction.CountA(.Rows(lig))

Si tu peux m'aider à coder encore un souhait pour aujourd’hui ça serait TOP


Je veux ajouter juste avant le msg box une ligne qui dit:

"Dans feuille "PASTE"
garder que le texte contenu dans une cellule (supprimer images, figures, groupes...)
ET
si la valeur de la cellule A de chaque ligne n'est PAS "ID" or PAS un nombre or PAS de la forme "D123456" alors supprimer"

Ci-joint code actuel. Est-ce que ligne 1 et 2 peuvent être supprimées ?

Merci par avance de ton/votre aide.

Option Explicit
Dim nlm&, lg2&

Private Sub Job(i%)
  Dim coID%, dlg&
  With Worksheets(i)
    coID = .Cells(2, Columns.Count).End(1).Column - 6
    dlg = .Cells(2, coID).End(4).Row
    .Cells(2, coID).Resize(dlg - 1, 11).Copy Cells(lg2, 1)
    lg2 = lg2 + dlg - 1
    dlg = .Cells(nlm, coID).End(3).Row
    .Cells(dlg, coID).Resize(, 11).Copy Cells(lg2, 1)
    Cells(lg2, 1).Resize(, 11).Borders.LineStyle = 1
    lg2 = lg2 + 1
  End With
End Sub

Sub CpyTbl()

  On Error Resume Next
  Dim Ws As Worksheet, lig&, k&, n%, p%: n = Worksheets.Count
  Application.ScreenUpdating = 0: Application.DisplayAlerts = 0
  For Each Ws In Application.Worksheets
    If Application.WorksheetFunction.CountA(Ws.UsedRange) = 0 Then Ws.Delete
  Next Ws
  For p = 1 To n
    With Worksheets(p)
      .Cells.UnMerge: k = .[A65536].End(3).Row
      For lig = k To 1 Step -1
        If Application.CountA(.Rows(lig)) = 0 Then .Rows(lig).Delete 3
      Next lig
    End With
  Next p
  Application.DisplayAlerts = -1


Sheets.Add Before:=Worksheets(1)
Sheets(1).Name = "PASTE" '

  Dim i%: Application.ScreenUpdating = 0
  Worksheets("PASTE").Select: Cells.Clear
  nlm = Rows.Count: lg2 = Cells(nlm, 1).End(3).Row + 1
  If lg2 = 2 And [A1] = "" Then lg2 = lg2 - 1
  For i = 2 To Worksheets.Count: Job i: Next i
  Cells(lg2 + 1, 1).Select: Application.CutCopyMode = 0

MsgBox ("MTO ESTABLISHED !")
End Sub

 

[soan]

@softy69

1) merci pour ta confirmation concernant le .CountA()

2) dans la sub CpyTbl(), tu n'as pas mis la bonne indentation
pour ces 3 lignes qui sont contre le bord gauche :

Sheets.Add Before:=Worksheets(1)
Sheets(1).Name = "PASTE" '

MsgBox ("MTO ESTABLISHED !")

elles doivent commencer sous le A de : Application.DisplayAlerts = -1

ça a peut-être l'air de pas grand chose, mais c'est très important,
car une mauvaise indentation nuit à la bonne lisibilité du code.

rappel : une bonne indentation est un des principes de base de
la programmation structurée.

3) pour le MsgBox, inutile de mettre des parenthèses,
tu peux mettre : MsgBox "MTO ESTABLISHED !"

(il va y avoir 2 Suites A et B à ce post)

soan

 

[soan]

Suite A du post précédent :

Tu demandes si on peut supprimer les lignes 1 et 2 ; lesquelles ? celles du début du module ?
celles du début de la sub Job() ? ou celles du début de la sub CpyTbl() ? bon, c'est pas grave :
surtout, ne t'embête pas à m'répondre, car dans les 3 cas, la réponse est non !

bon, j'pense quand même que tu voulais parler des lignes 1 et 2 du début du module.

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

* la ligne Dim nlm&, lg2& ne doit pas être supprimée, car c'est pour déclarer 2 variables
qui sont utilisées par le code VBA ; des variables qui sont déclarées à ce niveau (au début
du module, et avant toute sub), sont des variables globales (c'est à dire de portée globale) ;
elles sont donc visibles (accessibles) par toutes les subs du module ; alors qu'à l'inverse,
les variables locales d'une sub ne sont visibles que par elle (portée locale) ; dans tout ce
paragraphe, là où j'ai écrit sub, c'est pareil pour une function ; alors petit jeu : relis bien
ce paragraphe en pensant « function » quand tu vois « sub ».

d'autre part, si j'ai déclaré nlm et lg2 en tant que variables globales, c'est pour simplifier
l'appel de la sub Job() ; ainsi, la sub CpyTbl() transmet à Job() uniquement l'indice i de
la feuille (qui servira d'index de feuille) ; et j'évite de devoir transmettre aussi nlm et lg2.

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

* la ligne Option Explicit pourrait être supprimée, mais je le déconseille très fortement !
bien sûr, ça oblige à devoir déclarer explicitement toutes les variables, mais trop souvent,
c'est vu comme une contrainte et un inconvénient, alors que bien au contraire, c'est un
très gros avantage ! voici un exemple bien concret :

Montant = 10 ... puis : MsgBox Montant ; aucun problème, ça va afficher la valeur correcte
de Montant ; mais si tu fais une faute de frappe et que tu écris MsgBox Montent :

a) sans Option Explicit, ça va créer une nouvelle variable en mémoire (RAM) nommée
Montent, qui sera initialisée à 0, donc le MsgBox va afficher 0 au lieu de 10 ; là, c'est pas
encore trop grave vu que c'est juste un affichage erroné ; mais imagine que la faute
d'orthographe soit par exemple pour un nom de variable de ligne ; tu connais, toi,
la ligne 0 ? pas moi : je sais seulement qu'elle doit être située, en principe, juste au-
dessus de la ligne 1, donc en dehors des limites réelles de la feuille de calcul ! c'est pas
d'bol, hein ? surtout qu'une tentative d'utiliser la ligne fantôme 0 va créer un beau
plantage en bonne et due forme ! VBA ne sera pas content ! ni ton patron !
(qui sera lui très mécontent !) ; bon, j'sais très bien c'que vont dire @patricktoulon et
@mapomme : « on peut toujours récupérer l'erreur en mettant en place une gestion
d'erreurs » ; mébon, ça sort du cadre de cet article, et on ne va pas s'aventurer aussi
loin ! (pour toute réclamation, prière de s'adresser au rédacteur en chef !) ;
mapomme, si tu veux occuper tes loisirs de dimanche prochain à écrire un article
détaillé sur la mise en place d'une gestion d'erreurs, c'est pas de refus !
(les poissons chanteront : « quand le chat n'est pas là, les souris dansent ! » il se pourrait même que
tu sois à l'origine d'une exceptionnelle mutation génétique darwinienne des poissons : il y aura plus tard
des poissons-souris en plus des poissons-chats ! )

b) avec Option Explicit : Dim Montant ➯ la compilation crée une variable Montant,
qui est initialisée à 0 ; puis il y a : Montant = 10 ; ok : ça met 10 dans la place
mémoire qui a été réservée pour Montant ; donc MsgBox Montant affiche bien 10 :
résultat correct ! mais si tu as écrit Montent = 10 : la compilation « se dit » :
« ah non ! moi j'suis pas d'accord !!! j'ai aucune place réservée en mémoire qui a
le nom Montent ! donc STOP !!! j'arrête de suite mon job de compilation !!! » ;
c'est ici qu'intervient le fameux blocage avec la ligne jaune (quoique cette ligne
jaune n'apparaît pas toujours forcément) ; la compil a eu tout à fait raison de
s'arrêter, car on a vu dans le point a) qu'une variable qui n'a pas le contenu
adéquat peut entraîner une catastrophe ; donc c'est inutile qu'elle poursuive
plus loin son travail !

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

Option Explicit permet donc de détecter des variables non déclarées (celles que le
programmeur a oublié de déclarer, ou celles qui sont mal orthographiées) ; mais
pas seulement : ça permet aussi de détecter des incohérences concernant le type
des variables ; ah oui ? par exemple ? ben ça, c'est un autre job pour l'dimanche
d'après l'prochain dimanche de mapomme ! (pas vrai, mapomme ? ah zut, ça va
être loupé pour faire la grasse matinée et aller à la pêche !!! j'en connais un qui va m'appeler
« le saboteur des week-ends » ! bon, p't'être que patricktoulon voudra bien l'faire à ta place ?)

alors, tu es pour ou contre l'utilisation d'Option Explicit ? tu as toujours envie de
l'enlever ? fais comme tu veux, mais moi, je le mets systématiquement ! sauf
dans les très rares cas où un module contient une petit sub qui n'utilise aucune
variable ; par exemple une sub Workbook_Open() qui contient juste :
MsgBox "Hello ! c'est l'heure d'éteindre le PC !!!" (comment ? mais j'viens juste d'ouvrir
mon classeur Excel ! à c'rythme-là, j'aurai jamais l'temps d'terminer mon boulot !!!)

soan

 

[patricktoulon]

bonsoir Soan

j'sais très bien c'que vont dire @patricktoulon et
@mapomme : « on peut toujours récupérer l'erreur en mettant en place une gestion
d'erreurs

tu a tout faux je suis le premier a la proscrire
je ne m'en sert uniquement si je n'ai pas le choix

 

[softy69]

[QUOTE

alors, tu es pour ou contre l'utilisation d'Option Explicit ? tu as toujours envie de
l'enlever ? fais comme tu veux, mais moi, je le mets systématiquement ! sauf
dans les très rares cas où un module contient une petit sub qui n'utilise aucune
variable ; par exemple une sub Workbook_Open() qui contient juste :
MsgBox "Hello ! c'est l'heure d'éteindre le PC !!!" (comment ? mais j'viens juste d'ouvrir
mon classeur Excel ! à c'rythme-là, j'aurai jamais l'temps d'terminer mon boulot !!!)

soan
[/QUOTE]


C'est assez pour moi maitre @soan, fonction explicit adoptée!

J'attends avec impatience la suite B

 

[soan]

@patricktoulon : ah, j'savais pas ! désolé pour l'erreur ! mais d'un autre côté, j'l'ai écrit
un peu en plaisantant, et aussi en pensant que si j'le mettais pas, quelqu'un aurait écrit
sous ce post : « mais non, même si une erreur se produit, on peut la récupérer par une
gestion d'erreurs ! »

soan

 

[soan]

@softy69 : quoi ? t'as d'jà tout lu ? (et en plus t'es encore debout à c't'heure-ci ? )
bon, c'est vrai qu'c'est bien plus long à écrire qu'à lire !!! (surtout qu'j'tape pas vite, moi,
avec seulement deux doigts sur le clavier !!!) ; bon, j'suis quand même bien content qu't'as
adopté comme moi le « Option Explicit ! »

soan