Aide Macro : filtrer données + résultats sur nouvelles feuilles (ces-2010, desire, coller, selection)

Djorge84

11 Novembre 2010 13:58:22

Bonjour,

j'ai regardé sur le forum et je n'ai trouvé aucune discussion qui réponde à mon problème. Je vous explique donc de quoi il s'agit et ce que j'ai pu faire jusqu'à maintenant. Si quelqu'un peut me conseiller ensuite...

J'ai une base de donnée dans laquelle la colonne A renseigne sur les années et les premières lignes de la feuille indiquent différents calculs statistiques à partir des données contenues dans les colonnes à partir de F sur la période globale renseignée dans la colonne A (1984-2010).

Or j'aimerai pouvoir obtenir ces mêmes résultats statisitques pour chaque année.

Je sais que des fonctions d'Excel, type Sommeprod, prennent en compte l'utilisation d'un filtre pour calculer des statistiques sur un ensemble de données. Sauf que là des valeurs aberrantes devaient être exclues sur chaque colonnes en fonction d'un critère donné et que le fichier a été construit à partir de fonctions matricielles d'Excel... L'utilisation d'un filtre classique ne permet donc pas d'obtenir les valeurs statistiques sur les éléments retenus.

J'ai donc pensé tout bêtement à copier cette feuille plusieurs fois et à supprimer toutes les valeurs correspondant à l'année que je ne désire pas dans les calculs. Les calculs statisques ne prennent ainsi en compte que les valeurs correspondant à l'année conservée et j'ai donc ce que je souhaite!

Or comme il y a beaucoup d'années (et beaucoup de fichiers...), c'est beaucoup trop long à faire à la main... D'où mon idée de recourir à une macro pour automatiser cette tâche.

Comme je suis débutant en VBA, j'ai utilisé l'enregistreur de macro d'Excel et j'ai obtenu un code qui fonctionne. Pas de problème là-dessus, même si je pense qu'il pourrait êtr eplus joli, on est d'accord... Le problème c'est qu'il fonctionne bien évidemment uniquement pour l'année que j'ai utilisé lors de l'enregistrement : c'est logique (ne vous inquiétez pas j'attendais pas à ce qu'il me résolve toute ma problématique

). Et comme changer à la main l'année dans la macro ne permettrait pas de gagner du temps par rapport à une opération sans macro, j'aurai voulu faire une boucle pour que dans l'ordre la macro :

- copie la feuille qui m'intéresse en dernière position (feuille 2) ;
- supprime toutes les données contenues dans les colonnes à partir de la colonne F ;
- copie la feuille initiale une nouvelle fois (feuille 3) en y insérant 3 lignes tout en haut pour y coller les intitulés des différentes colonnes de données dans le but de réaliser un filtre élaboré;
- copie en A3 l'année que l'on souhaite garder pour le filtre élaboré;
- copier sur la feuille 2, les données provenant de l'utilisation du filtre sur la feuille 3
- renommer les feuille 2 du nom de l'année correspondante aux données;
- supprimer la feuille 3 ;

- et recommencer pour une autre année de la liste...

Pour qu'au final j'ai dans mon fichier, la feuille initiale avec l'ensemble des données et autant d'autres feuilles que d'année indiquée dans la base de données.

J'avais pensé insérer une feuille supplémentaire en 1ère position du classeur dans laquelle j'aurai indiqué sur la première colonne toutes années pour ensuite renvoyer vers ces cellules pour indiquer au filtre élaboré l'année à utiliser (feuille appelée "Année"). Mais je n'arrive pas à faire la boucle...

J'espère avoir été clair.

Si quelqu'un veut bien me filer un coup de main...

Merci par avance

J'indique le code que me donne l'enregistreur de macro :

Sub Test()

' Préparation de la feuille finale

 
' Copie de la feuille principale en dernière position

    Sheets("Régénération").Select

    Sheets("Régénération").Copy After:=Sheets(2)

' Sélection de l'ensemble des données sur cette feuille

    Rows("27:27").Select

    Range(Selection, Selection.End(xlDown)).Select

' Suppression de ces données pour avoir une feuille vide avec la mise en forme et la zone des données  statistiques voulues conservée

    Selection.ClearContents

 
' Création d'une feuille intermédiaire pour permettre la manipulation des données

 
' Copie de la feuille principale en dernière position

    Sheets("Régénération").Select

    Sheets("Régénération").Copy After:=Sheets(3)

' Insertion de 3 lignes

    Rows("1:3").Select

    Selection.Insert Shift:=xlDown

' Copie des titres des différents champs pour préparer l'utilisation d'un filtre élaboré    

    Rows("28:29").Select

    Range("F28").Activate

    Selection.Copy

' Collage des titres des différents champs sur les deux premières lignes

    Rows("1:1").Select

    ActiveSheet.Paste

    Range("A3").Select

    Application.CutCopyMode = False

' Choix de l'année à utiliser pour le filtre élaboré

    ActiveCell.Value = Worksheets("Année").Range("A25").Value

    Range("A4").Select

' Sélection de la plage de données et de la plage de critères pour  le filtre élaboré

    Range("A28:AA2036").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

    Range("A1:AA3"), Unique:=False

' Sélection des données filtrées pour copie 

    Rows("1212:1212").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

 
' Collage des données sur la feuille finale

    Sheets("Régénération (2)").Select

    Rows("27:27").Select

    ActiveSheet.Paste

 
' Changement du nom de la feuille (année correspondante)

    Sheets("Régénération (2)").Name = Sheets("Année").[A25]

 
' Suppression de la feuille intermédiaire

    Sheets("Régénération (3)").Select

    Application.CutCopyMode = False

    ActiveWindow.SelectedSheets.Delete

 
    End Sub

PS. J'aurai bien mis un fichier en exemple mais je ne sais pas comment ajouter une PJ à ce post...

Autres pages sur : aide macro filtrer donnees resultats nouvelles feuilles

| Etre averti des réponses
| Alerter

Répondre à Djorge84

oozenot

Expert Programmation

11 Novembre 2010 19:47:32

salut Djorge84,

Bon, avant de commencer a trouver une solution a ton probleme je te propose de d'appliquer quelques "règles" a ton code pour le rendre plus lisible, court et donc compréhensible.

Tout d'abord, tout les . select et les Selection. peuvent être rassemblé. exemple avec tes lignes 9 et 11:

Range(Selection, Selection.End(xlDown)).Select

'// Suppression de ces données pour avoir une feuille vide avec la mise en forme et la zone des données  statistiques voulues conservée

Selection.ClearContents

deviens:

Range(Selection, Selection.End(xlDown)).ClearContents

et tu peux appliquer ca a tout ton code... ce n'en sera que plus lisible !!

ensuite, pour éviter d'avoir des Active-machin (activesheet) déclare tes feuilles!!!
utilise ce modèle pour la feuille Régénération (2) par exemple:

dim ws_regeneration2 as Worksheet

set ws_regeneration2 = Worksheets("Régénération (2)")

et dans la suite de ton code tu n'utilisera que ws_regeneration2 pour désigner ta feuille !

Les lignes 5 et 16 ne servant a rien.
Pour la copie (.copy) il est bien utilisé ligne 17 et moins bien par la suite
utilise la syntaxe suivante : ce_que_tu_veux_copier.Copy la_destination_de_ta_copie

n'hésite pas a user de l'aide Excel F1 sur les fonction comme Copy, l'aide te donne la syntaxe et te dis ce a quoi elle sert !!!!

C'est tout pour le code (pour l'instant).

Maintenant je pensais en lisant le début de ton roman que tu pourrais demander a l'utilisateur d'entrer la date puis la macro exécuterai pour cette date. Mais je me dis que si ton tableau (et oui, Excel est un tableur, pas une base de données!!!!!!) est grands et que ta macro doit s'utiliser sur qqch comme 1000 années différentes... ca va pas le faire !!
Autre solution : demander a l'utilisateur la colonne contenant toute les années et créer une macro qui fais ton analyse pour chaque année contenue dans la colonne (en excluant les doublons, bien sûr !)

au final tu veux copier la ligne qui contient une certaine année dans une autre feuille non?

| Alerter

Répondre à oozenot

Djorge84

12 Novembre 2010 13:11:25

oozenot a dit :

salut Djorge84,

Bon, avant de commencer a trouver une solution a ton probleme je te propose de d'appliquer quelques "règles" a ton code pour le rendre plus lisible, court et donc compréhensible.

Tout d'abord, tout les . select et les Selection. peuvent être rassemblé. exemple avec tes lignes 9 et 11:

Range(Selection, Selection.End(xlDown)).Select

'// Suppression de ces données pour avoir une feuille vide avec la mise en forme et la zone des données  statistiques voulues conservée

Selection.ClearContents

deviens:

Range(Selection, Selection.End(xlDown)).ClearContents

et tu peux appliquer ca a tout ton code... ce n'en sera que plus lisible !!

ensuite, pour éviter d'avoir des Active-machin (activesheet) déclare tes feuilles!!!
utilise ce modèle pour la feuille Régénération (2) par exemple:

dim ws_regeneration2 as Worksheet

set ws_regeneration2 = Worksheets("Régénération (2)")

et dans la suite de ton code tu n'utilisera que ws_regeneration2 pour désigner ta feuille !

Les lignes 5 et 16 ne servant a rien.
Pour la copie (.copy) il est bien utilisé ligne 17 et moins bien par la suite
utilise la syntaxe suivante : ce_que_tu_veux_copier.Copy la_destination_de_ta_copie

n'hésite pas a user de l'aide Excel F1 sur les fonction comme Copy, l'aide te donne la syntaxe et te dis ce a quoi elle sert !!!!

C'est tout pour le code (pour l'instant).

Maintenant je pensais en lisant le début de ton roman que tu pourrais demander a l'utilisateur d'entrer la date puis la macro exécuterai pour cette date. Mais je me dis que si ton tableau (et oui, Excel est un tableur, pas une base de données!!!!!!) est grands et que ta macro doit s'utiliser sur qqch comme 1000 années différentes... ca va pas le faire !!
Autre solution : demander a l'utilisateur la colonne contenant toute les années et créer une macro qui fais ton analyse pour chaque année contenue dans la colonne (en excluant les doublons, bien sûr !)

au final tu veux copier la ligne qui contient une certaine année dans une autre feuille non?

Merci pour ta réponse et pour ces éléments.
Je vais prendre en compte tes remarques pour alléger le code dès que j'aurai un moment et je posterai ce que j'aurai obtenu pour qu'on y voit plus clair.

Pour préciser des choses suite à tes diverses propositions, j'ai toutes les années comprises entre 1980 et 2010 dans ma colonne A, donc ça fait 31 années si je ne me trompe pas.

Au départ la solution que tu évoques de faire une macro qui demande à l'utilisateur quelle année il veut traiter était ce que j'envisageais. Mais compte tenu du fait que je ne voyais pas du tout comment on peut faire ça, je me suis plutôt orienté vers l'option "ma macro fait toutes les années qui sont renseignées dans la colonne", d'où mon envie de faire une boucle sur les années.

Donc si tu as des propositions de code à me proposer une fois que j'aurai alléger le code donné par l'enregistreur de macro, je suis preneur des deux solutions! ça pourra toujours m'être utile pour ma culture générale en VBA!

Merci encore, je reviens vers toi le plus vite possible

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

12 Novembre 2010 15:18:53

(drapal)

| Alerter

Répondre à zeb

Djorge84

13 Novembre 2010 14:56:26

Re-bonjour,

donc j'ai essayé les différentes choses que oozenot m'a proposé (faut encore que je me penche sur la fonction copy), mais il y a déjà un problème suite à la déclaration de mes feuilles.

En effet mon classeur Excel a au départ 2 feuilles : Régénération et Années

Donc je déclare ces deux feuilles sans problème (à ce propos pour info pourquoi Worksheet prend un s une fois et pas l'autre dans la déclaration et lorsqu'on attribue un nom à la feuille?)

Les deux autres feuilles Régénération (2) et Régénération (3) n'existent en revanche pas au départ et sont créées par la macro. Et j'obtiens un message d'erreur lorsque je lance la macro (je pense dû à ça) :

ws_regeneration2 = Nothing et Worksheets("Régénération (2)") = <L'indice n'appartient pas à la sélection.>

je pense que ça ferait la même choseà la feuille "Régénération (3)" si ça passait le cap de la feuille "Régénération (2)"

Savez-vous comment résoudre ça?

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

13 Novembre 2010 23:20:25

Salut,

Citation :

à ce propos pour info pourquoi Worksheet prend un s une fois et pas l'autre dans la déclaration et lorsqu'on attribue un nom à la feuille?

Je réponds à cette question existentielle :

Dim ws_regeneration2 as Worksheet

ws_regeneration2 sera une et une seule feuille.

set ws_regeneration2 = Worksheets("Régénération (2)" )

Parmi la collection de toutes les feuilles, on choisit celle qui porte le nom Régénération (2)

Si les bons conseils de oozenot te semblent avisés, refais ton code en les prenant en compte et publie-le. On pourra reprendre à partir de là et mieux comprendre tes explications.

| Alerter

Répondre à zeb

Djorge84

14 Novembre 2010 16:25:23

Bonjour,

après diverses simplifications/modifications, voilà le code que j'obtiens.

Sub NouveauTest()

 
Dim ws_regeneration As Worksheet

Dim ws_regeneration2 As Worksheet

Dim ws_regeneration3 As Worksheet

Dim ws_annees As Worksheet

Set ws_regeneration = Worksheets("Régénération")

Set ws_regeneration2 = Worksheets("Régénération (2)")

Set ws_regeneration3 = Worksheets("Régénération (3)")

Set ws_annees = Worksheets("Année")

 
    ws_regeneration.Copy After:=Sheets(2)

    Rows("27:27").Select

    Range(Selection, Selection.End(xlDown)).ClearContents

    ws_regeneration.Copy After:=Sheets(3)

    Rows("1:3").Insert Shift:=xlDown

    Rows("28:29").Copy Rows("1:1")

    Range("A3").Select

    ActiveCell.Value = ws_annees.Range("A25").Value

    Range("A28:AA2036").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

        Range("A1:AA3"), Unique:=False

    Rows("1212:1212").Select

    Range(Selection, Selection.End(xlDown)).Copy ws_regeneration2.Rows("27:27")

    ws_regeneration2.Name = ws_annees.[A25]

    ws_regeneration3.Delete

 
    End Sub

Effectivement il y avait des choses à supprimer par rapport à la première version...

Le code marche bien tant que je laisse les noms de feuilles donnés par l'enregistreur de macro.
En revanche dès que je déclare les différentes feuilles utilisées (version du code ci-dessus), ça ne marche plus... et il bloque sur la première feuille qui au départ n’existe pas et qui doit être créée par la macro on dirait (ligne 8 du code).

Je ne sais pas comment je peux vous mettre en ligne une version du fichier Excel pour que vous voyez ce que ça doit faire exactement. Ça serait plus simple sans doute.

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

15 Novembre 2010 10:28:34

Meuh non !

Ta feuille Régénération (2) n'existe pas encore en ligne 8. Elle est créé par copie en ligne 12.

Donc retire la ligne 8, et écris la ligne 12 comme ceci :

Set ws_regeneration2 = ws_regeneration.Copy(After:=Sheets(2))

Lignes 13 et 14. Non môssieu :non:

Il reste un couple Select/Selection. Spa bon. Et en plus, on ne sait pas de quelle feuille tu parles ! Et pis tu ne respectes pas la fonction Rows(), la pauvre. Tu lui fais faire trop de boulot (

).

' // Pas bon du tout

Rows("27:27" ).Select

Range(Selection, Selection.End(xlDown)).ClearContents

 
' // Un peu mieux

ws_xxxx.Rows(27).Select

ws_xxxx.Range(Selection, Selection.End(xlDown)).ClearContents

 
' // Correct

ws_xxxx.Range(ws_xxxx.Rows(27), ws_xxxx.Rows(27).End(xlDown)).ClearContents

 
' // Plus léger (ou pas)

With ws_xxxx

  .Range(.Rows(27), .Rows(27).End(xlDown)).ClearContents

End With

_________

Citation :

Je ne sais pas comment je peux vous mettre en ligne une version du fichier Excel pour que vous voyez ce que ça doit faire exactement. Ça serait plus simple sans doute.

Non plus môssieu. On ne met pas en ligne des fichiers pleins de macros. C'est pour le bien de tous !

| Alerter

Répondre à zeb

Djorge84

15 Novembre 2010 17:25:38

Re-

je ne savais pas comment faire pour supprimer les Select/Selection dans les Range, désolé

Bon en tout cas, voilà qui est fait et je pense que vous y verez plus clair maintenant.
Pour la déclaration des feuilles, je n'ai pas fait aussi concis que ce que tu m'as dit dans ton dernier post car ça ne fonctionnait pas... J'ai donc opté pour la solution en 2 étapes: je copie la feuille et je la déclare en tant que ws_xxxx ensuite.

Sub NouveauTest()

 
Dim ws_regeneration As Worksheet

Dim ws_regeneration2 As Worksheet

Dim ws_regeneration3 As Worksheet

Dim ws_annees As Worksheet

 
Set ws_regeneration = Worksheets("Régénération")

Set ws_annees = Worksheets("Année")

ws_regeneration.Copy After:=Sheets(2)

Set ws_regeneration2 = Worksheets("Régénération (2)")

ws_regeneration.Copy After:=Sheets(3)

Set ws_regeneration3 = Worksheets("Régénération (3)")

ws_regeneration2.Range(ws_regeneration2.Rows(27), ws_regeneration2.Rows(27).End(xlDown)).ClearContents

ws_regeneration3.Range(ws_regeneration3.Rows(1), ws_regeneration3.Rows(3)).Insert Shift:=xlDown

ws_regeneration3.Range(ws_regeneration3.Rows(28), ws_regeneration3.Rows(29)).Copy ws_regeneration3.Rows(1)

ws_regeneration3.Range("A3") = ws_annees.Range("A25").Value

ws_regeneration3.Range("A28:AA2036").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

        Range("A1:AA3"), Unique:=False

ws_regeneration3.Range(ws_regeneration3.Rows(1212), ws_regeneration3.Rows(1212).End(xlDown)).Copy ws_regeneration2.Rows("27:27")

ws_regeneration2.Name = ws_annees.[A25]

ws_regeneration3.Delete 

 
End Sub

Si vous pouvez me donner des pistes pour qu'à partir de ma feuille ws_regeneration (où j'ai toutes mes données triées par année --> colonne A), je puisse créer des feuilles relatives chacune à une année donnée en allant chercher celle que je veux dans ma feuille ws_annes, ça me sauve!

Merci de tous vos conseils en tout cas, j'apprends petit à petit ...

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

16 Novembre 2010 00:53:53

Alors ça, ce n'est pas une bonne idée :

ws_regeneration.Copy After:=Sheets(2)
Set ws_regeneration2 = Worksheets("Régénération (2)" )

Pourquoi ?
Et bien parce que tu présumes du nom de ta nouvelle feuille. Or, si une feuille de ce nom là existe déjà, le nom de la nouvelle n'est pas celui que tu attends. Et si ta macro devait être utilisée sur un Excel dans une autre langue ? Ou bien, ce nommage perdurera-t-il dans la prochaine version d'Excel ou avec le prochain système d'exploitation ? (Que de souvenirs pour moi dans les diverses entreprises où j'ai bossé :lol:

)

Donc :

Set ws_regeneration2 = ws_regeneration.Copy(After:=Sheets(2))

Bon maintenant que tu as copié trois fois tes données, il faut supprimer dans chacune des nouvelles feuilles les données en trop. Pour ça, parcours tes données en partant du bas et supprime les lignes qui ne te conviennent pas.

J'explique ici pourquoi il faut partir du bas.

Je te laisse trouver et nous proposer un truc pour le parcours de tes données. Si ça marche, tant mieux :sol:

sinon, on t'aidera à en faire quelque chose de fonctionnel

| Alerter

Répondre à zeb

Djorge84

16 Novembre 2010 08:55:53

Ok je comprends la différence entre les 2 façons de faire pour la copie et déclaration des nouvelles feuilles créées et le problème que peut entrainer la solution pour laquelle j'ai opté. Mais lorsque je fais comme tu me dis, cela conduit à une erreur de compilation et j'ai le message suivant:

Erreur de compilation:
Fonction ou variable attendue

Et dans la ligne de code

Set ws_regeneration2 = ws_regeneration.Copy(After:=Sheets(2))

j'ai .Copy qui apparait surligné en jaune.

C'est d'ailleurs pour cette raison que j'avais pris l'option de faire ça en 2 lignes (même je n'avais pas vu l'inconvénient que tu mentionne)

Je vais regarder ce que tu mentionne pour la sélection des données à partir du bas.

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

16 Novembre 2010 09:38:07

Tu as parfaitement raison, le code que je te proposais ne fonctionne pas. Le modèle objet d'Excel est incomplet. Quelle bande de nases chez Crosoft :fou:

Font les choses à moitié...

Bon, comme j'ai cité pleins de bonnes raison pour pas se fier au nom, il faut maintenant que je te montre comment faire autrement. C'est facile. Un classeur, ça contient des onglets de plusieurs natures, feuilles de calcul et graphiques essentiellement, que l'on retrouve respectivement dans les collections Worksheets et Charts. La collection de tous les onglets, quelque soit leur nature, est Sheets. Donc, si tu demandes à ce que ta nouvelle feuille soit copiée après (Copy After:=) la feuille 2, c'est que la nouvelle feuille va être numérotée 3, et que toutes les autres vont être décalées dans la collection. T'as tout compris ? :pt1cable:

Un exemple sera plus parlant :

Dim n As Integer
Dim ws_feuillecible_A As Worksheet
Dim ws_feuillecible_B As Worksheet
 
n = 2
ws_feuillesource.Copy After:=Sheets(n)
Set ws_feuillecible_A = Sheets(n + 1)
 
ws_feuillesource.Copy Before:=Sheets(n)
Set ws_feuillecible_B = Sheets(n)

Evidemment, dans ton code, tu te permettras de ne pas te servir d'une variable

Sauf que tu peux fixer la valeur de n à Sheets.Count, pour te référer à la dernière feuille, ce qui est particulièrement élégant

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

Bon, et ta proposition d'un truc pour le parcours de tes données ?

| Alerter

Répondre à zeb

Djorge84

16 Novembre 2010 11:29:46

Bon alors je pense avoir pris en compte tes différentes remarques et ça fonctionne, c'est bon.

A l'arrivée mon classeur contient bien mes feuilles ws_annees et ws_regeneration initiales, ainsi que la feuille qui m'intéresse, obtenue grâce à la macro et nommée ws_annees.[A25] (2004 en fait).

Le truc maintenant c'est qu'il faut que j'arrive à faire une boucle pour obtenir la même chose mais sur l'ensemble des années renseignées sur ma feuille ws_annees , sur laquelle des années sont indiquées sur la plage A2:A31. Donc il faut que la boucle porte sur l'intégralité du code pour avoir au final un onglet par année. Et là je bloque car je ne vois comment m'en sortir, notamment avec l'indexation des différentes feuilles et de celle que je crée de façon temporaire pour faire mon filtre.

Un conseil?

Sub NouveauTest()

 
Dim n As Integer

Dim ws_regeneration As Worksheet

Dim ws_regeneration2 As Worksheet

Dim ws_regeneration3 As Worksheet

Dim ws_annees As Worksheet

Dim derli As Long

Dim i    As Long

 
n = Sheets.Count

Set ws_regeneration = Worksheets("Régénération")

Set ws_annees = Worksheets("Année")

ws_regeneration.Copy After:=Sheets(n)

Set ws_regeneration2 = Sheets(n + 1)

ws_regeneration.Copy After:=Sheets(n + 1)

Set ws_regeneration3 = Sheets(n + 2)

 
ws_regeneration2.Range(ws_regeneration2.Rows(27), ws_regeneration2.Rows(27).End(xlDown)).ClearContents

ws_regeneration3.Range(ws_regeneration3.Rows(1), ws_regeneration3.Rows(3)).Insert Shift:=xlDown

ws_regeneration3.Range(ws_regeneration3.Rows(28), ws_regeneration3.Rows(29)).Copy ws_regeneration3.Rows(1)

ws_regeneration3.Range("A3") = ws_annees.Range("A25").Value

ws_regeneration3.Range("A28:AA2036").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

        Range("A1:AA3"), Unique:=False

 
derli = ws_regeneration3.Columns(1).Find("*", , , , , xlPrevious).Row

For i = derli To 30 Step -1

    If ws_regeneration3.Cells(i, 1) = "" Then

        ws_regeneration3.Rows(i).Delete

    End If

Next

 
ws_regeneration3.Range(ws_regeneration3.Rows(30), ws_regeneration3.Rows(30).End(xlDown)).Copy ws_regeneration2.Rows("27:27")

ws_regeneration2.Name = ws_annees.[A25]

ws_regeneration3.Delete

 
End Sub

PS. Je ne sais pas si la façon dont j'utilise Sheets.Count et l'incrémentation des Sheets(xx) est bonne (optimale ?) ou pas en revanche...

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

16 Novembre 2010 13:33:40

PS:

Oui, c'est bon.
Mais tu peux aussi faire comme ça :

ws_regeneration.Copy After:=Sheets(Sheets.Count)
Set ws_regeneration2 = Sheets(Sheets.Count)
ws_regeneration.Copy After:=Sheets(Sheets.Count)
Set ws_regeneration3 = Sheets(Sheets.Count)

Attention, c'est Sioux !

Juste comme ça, t'as pas envie d'appeler tes variables avec des noms plus courts et/ou des abréviations ?

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

Petits bouts de code à étudier :

Function SheetExists(ByVal name As String, Optional wb As Workbook) As Boolean
    Dim sheet As Object
 
    If IsMissing(wb) Or wb Is Nothing Then Set wb = ActiveWorkbook
 
    For Each sheet In wb.Sheets
        If sheet.name = name Then
            SheetExists = True
            Exit For
        End If
    Next
End Function

Dim zone      As Range
Dim cell      As Range
Dim annees()  As Boolean
Dim annee_min As Integer
Dim annee_max As Integer
Dim annee     As Integer
 
Dim ws_source As Worksheet
 
Set ws_source = Worksheets("xxxxxxxxx")
 
Set zone = ws_source.Range("A2:A31")
 
MsgBox "La zone prise en compte est : " & zone.Address
 
annee_min = WorksheetFunction.Min(zone)
annee_max = WorksheetFunction.Max(zone)
 
MsgBox "L'année la plus ancienne est " & annee_min & ", et la plus récente est " & annee_max
 
ReDim annees(annee_min To annee_max)
 
For Each cell In zone
    If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
        If Not annees(CInt(cell.Value)) Then MsgBox "Prise en compte de l'année " & CInt(cell.Value)
        annees(CInt(cell)) = True
    End If
Next
 
For annee = annee_min To annee_max
    If annees(annee) And Not SheetExists(CStr(annee)) Then
        ws_source.Copy , Sheets(Sheets.Count)
        Sheets(Sheets.Count).name = CStr(annee)
    End If
Next

| Alerter

Répondre à zeb

Djorge84

16 Novembre 2010 15:07:41

je viens de regarder ce que tu as répondu à mon dernier post et je sèche complètement sur le premier bout de code.
Je comprends que la fonction permet de vérifier si une feuille existe déjà sous un nom donné et que tu utilise cette fonction dans la dernière boucle du second bout de code. Mais pour ce qui est de la façon dont tu la code et de la première condition... :??:

Que signifient dans la fonction SheetExists : ByVal name As String, Optional wb As Workbook ?
Et la conditon : If IsMissing(wb) Or wb Is Nothing Then Set wb = ActiveWorkbook ?

Pour le second je vois à peu près de quoi il s'agit, excepté pour la boucle :

For Each cell In zone

    If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then

        If Not annees(CInt(cell.Value)) Then MsgBox "Prise en compte de l'année " & CInt(cell.Value)

        annees(CInt(cell)) = True

    End If

Next

C'est les conditions que tu utilises qui me perdent complètement...

Il faut que je fasse quoi de ces deux bouts de code maintenant: que je les adapte à mon cas ou que j'y insère ce que j'ai réussi à coder jusqu'à maintenant grâce à vous?

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

17 Novembre 2010 09:51:31

Ce que je te propose est à étudier. Donc tu dois te poser pleins de questions pour bien le comprendre. Je vois que certaines de ces questions sont pour moi

La fonction SheetExists accepte un ou deux paramètres, un nom de feuille, et optionnellement, un classeur. Connais-tu la notion de passage de paramètre par valeur ou par référence ? Si oui, ByVal devrait être maintenant explicite pour toi. Sinon, pour ta culture informatique, renseigne-toi sur le sujet. C'est tellement classique que tu ne manqueras pas de trouver.

Or donc, le classeur est optionnel. Donc s'il est omis ou s'il n'a pas de valeur, je lui en fournis une. Observes-en bien l'intérêt. Si tu ne traites qu'un seul classeur, tu peux ne pas t'en soucier, et ne rien préciser. C'est ce que je fais à la ligne 31. Par contre, si tu veux traiter plusieurs classeurs dans une même macro, plutôt que te fier à celui qui serait actif, tu peux explicitement le préciser.

Ce type de fonction, très générique, qui ne fait pas grand'chose mais qui le fait bien, s'appelle une primitive. J'en ai plein, et je te les ressors au besoin, sans les simplifier, ce qui peut te dérouter. N'en prends pas ombrage au contraire, profites-en

Maintenant la boucle. J'ai fait un tableau avec comme indice, les valeurs trouvées dans la colonne des années. Je ne pars pas de zéro, ni de un, et je ne vais pas jusqu'à l'an 3000, ce que j'aurais pu faire. Je le taille au plus juste.

Ensuite, je boucle. Ah, mais si il n'y a pas que des nombres dans ta colonne ? Et si une cellule est vide ? On ne sait jamais ! Donc je vérifie que la cellule est non vide et que la valeur est numérique. Je ne peux pas me contenter de vérifier que la valeur est numérique, parce que cette andouille de fonction Excel IsNumeric() renvoie vrai quand la cellule est vide :pfff:

Réétudie tout ça à la lumière de ces quelques renseignements supplémentaires. Et surtout n'hésite pas à reposer des questions.

Et tu me dois toujours une proposition de code pour parcourir tes données pour supprimer celles qui ne nous intéressent pas.

(Je ne t'en donnerai pas le code, je partirai du tien pour l'améliorer si ce que tu proposes ne fonctionne pas parfaitement

)

| Alerter

Répondre à zeb

Djorge84

17 Novembre 2010 10:22:43

OK merci des précisions qui m'aident grandement!

Je vais regarder les bouts de code que tu m'as proposé à la lumière de tes explications.

Pour ce qui concerne la proposition de code que je dois te faire, je pensais que celle que j'ai ajouté dans le code que j'ai posté correspondait à ce que tu me demandais... Je remets la partie en question:

derli = ws_regeneration3.Columns(1).Find("*", , , , , xlPrevious).Row

For i = derli To 30 Step -1

    If ws_regeneration3.Cells(i, 1) = "" Then

        ws_regeneration3.Rows(i).Delete

    End If

Next

J'ai également essayé de faire quelquechose du genre sur ma feuille source ws_regene (ici dans le cas de l'année 2004) :

derli = ws_regene.Columns(1).Find("*", , , , , xlPrevious).Row

For i = derli To 27 Step -1

    If ws_regene.Cells(i, 1) <> 2004 Then

        ws_regene.Rows(i).Delete

    End If

Next

sauf que vu que j'ai quelquechose comme 10 000 lignes, cette opération doit être trop gourmande en mémoire et au bout de 15 min Excel moulinait toujours... Je l'ai donc killé. Il y a peut être une astuce pour faciliter cette action, mais mes faibles compétences en VB ne me permettent pas de voir quoi...

PS. Je ne connais pas la notion de passage de paramètre par valeur ou par référence --> je vais me renseigner

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

17 Novembre 2010 13:33:08

Arf, au temps pour moi. Je l'avais raté.
Oui, c'est plutôt pas mal comme solution.

Renseigne-toi aussi sur ScreenUpdating. Il se pourrait que tu apprécies

| Alerter

Répondre à zeb

zeb

Expert Programmation

17 Novembre 2010 13:41:41

Si tes traitements sont un peu longs, tu peux mettre la progression dans la barre de statut d'Excel, c'est sympa :

For i = derli To 27 Step -1

    Application.StatusBar = "Travail en cours. Progression : " & (derli - i) * 100 \ (derli - 27) & "%"

    ...

Next

Application.StatusBar = False

| Alerter

Répondre à zeb

Djorge84

18 Novembre 2010 11:07:41

zeb a dit :

Arf, au temps pour moi. Je l'avais raté.
Oui, c'est plutôt pas mal comme solution.

Renseigne-toi aussi sur ScreenUpdating. Il se pourrait que tu apprécies

Effectivement ça change les choses de désactiver le ScreenUpdating...

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

18 Novembre 2010 12:40:22

Et quand on l'a désactivé, que faut-il ne pas oublier à la fin de la macro ?

| Alerter

Répondre à zeb

Djorge84

18 Novembre 2010 12:49:26

zeb a dit :

Et quand on l'a désactivé, que faut-il ne pas oublier à la fin de la macro ?

de l'activer à nouveau, j'ai vu ça

| Alerter

Répondre à Djorge84

zeb

Expert Programmation

18 Novembre 2010 12:50:49

Bien vu.

C'est donc résolu ?

| Alerter

Répondre à zeb

Djorge84

18 Novembre 2010 13:22:14

zeb a dit :

Bien vu.

C'est donc résolu ?

je n'ai pas encore eu le temps de mepencher réellement sur les bouts de code que tu m'as proposé et de les adapter précisément à mon cas depuis mon dernier post.

Je te donne des nouvelles dès que j'ai fait ça (très bientôt j'espère) !

| Alerter

Répondre à Djorge84

Djorge84

22 Novembre 2010 15:55:37

ça y est me revoilà!

Bon ben j'ai pu me pencher ce week end sur ce dont tu m'avais parlé et j'avoue que je sèche :heink:

sur la façon dont je dois inclure tes bouts de code dans ce que j'ai déjà fait (cf. post précédent).

D'une part, ce qui me gène c'est qu'au final il va y avoir 3 boucles il me semble:

- les 2 tiennes :

For Each cell In zone

    If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then

        If Not annees(CInt(cell.Value)) Then MsgBox "Prise en compte de l'année " & CInt(cell.Value)

        annees(CInt(cell)) = True

    End If

Next

For annee = annee_min To annee_max

    If annees(annee) And Not SheetExists(CStr(annee)) Then

        ws_source.Copy , Sheets(Sheets.Count)

        Sheets(Sheets.Count).name = CStr(annee)

    End If

Next

- et celle que j'ai fait pour supprimer les lignes qui ne m'intéressent pas :

For i = derli To 30 Step -1

    If ws_regeneration3.Cells(i, 1) = "" Then

        ws_regeneration3.Rows(i).Delete

    End If

Next

Et là je me dis: ne faudrait-il pas que ces boucles soient imbriquées les unes dans les autres????

Ensuite ce qui me perd c'est ce qui se rapporte à la copie de ma feuille ws_regen.
Normalement pour chaque année que je veux traiter, je la copie 2 fois:
- une fois pour la vider de ces données ws_regen2, ok?
- une seconde fois (ws_regen3), pour y faire subir le filtre et copier les valeurs qui restent alors vers ws_regen2.

Sauf que avec ton code je ne vois pas comment faire pour que ce processus ait lieu pour chaque année.

En fait j'ai beau me retourner le cerveau, je ne comprends pas comment intégrer le ws_source.Copy , Sheets(Sheets.Count) (en fait pour moi ws_regen.Copy , Sheets(Sheets.Count)) qui apparait dans ta dernière boucle.

Chez moi ça donne ça et bien évidemment ça tourne pas...

Sub NouveauTest()

 
Function SheetExists(ByVal name As String, Optional wb As Workbook) As Boolean

    Dim sheet As Object

 
    If IsMissing(wb) Or wb Is Nothing Then Set wb = ActiveWorkbook

 
    For Each sheet In wb.Sheets

        If sheet.name = name Then

            SheetExists = True

            Exit For

        End If

    Next

End Function

 
Dim n As Integer

Dim ws_regen As Worksheet

Dim ws_regen2 As Worksheet

Dim ws_regen3 As Worksheet

Dim ws_annees As Worksheet

Dim zone      As Range

Dim cell      As Range

Dim annees()  As Boolean

Dim annee_min As Integer

Dim annee_max As Integer

Dim annee    As Integer

Dim derli As Long

Dim i    As Long

 
Set ws_regen = Worksheets("Régénération")

Set ws_annees = Worksheets("Année")

ws_regen.Copy After:=Sheets(Sheets.Count)

Set ws_regen2 = Sheets(Sheets.Count)

ws_regen.Copy After:=Sheets(Sheets.Count)

Set ws_regen3 = Sheets(Sheets.Count)

 
Set zone = ws_annees.Range("A2:A31")

 
MsgBox "La zone prise en compte est : " & zone.Address

 
annee_min = WorksheetFunction.Min(zone)

annee_max = WorksheetFunction.Max(zone)

 
MsgBox "L'année la plus ancienne est " & annee_min & ", et la plus récente est " & annee_max

 
ReDim annees(annee_min To annee_max)

 
For Each cell In zone

    If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then

        If Not annees(CInt(cell.Value)) Then MsgBox "Prise en compte de l'année " & CInt(cell.Value)

        annees(CInt(cell)) = True

    End If

Next

 
ws_regen2.Range(ws_regen2.Rows(27), ws_regen2.Rows(27).End(xlDown)).ClearContents

ws_regen3.Range(ws_regen3.Rows(1), ws_regen3.Rows(3)).Insert Shift:=xlDown

ws_regen3.Range(ws_regen3.Rows(28), ws_regen3.Rows(29)).Copy ws_regen3.Rows(1)

ws_regen3.Range("A3") = ws_annees.Range("A25").Value

ws_regen3.Range("A28:AA2036").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

                Range("A1:AA3"), Unique:=False

 
derli = ws_regen3.Columns(1).Find("*", , , , , xlPrevious).Row

 
For i = derli To 30 Step -1

            If ws_regen3.Cells(i, 1) = "" Then

                ws_regen3.Rows(i).Delete

            End If

Next

 
For annee = annee_min To annee_max

    If annees(annee) And Not SheetExists(CStr(annee)) Then

        ws_regen.Copy , Sheets(Sheets.Count)

        Sheets(Sheets.Count).name = CStr(annee)

    End If

Next

 
ws_regen3.Range(ws_regen3.Rows(30), ws_regen3.Rows(30).End(xlDown)).Copy ws_regen2.Rows("27:27")

ws_regen2.name = ws_annees.[A25]

ws_regen3.Delete

 
End Sub

Bon en gros c'est le bordel parce que je vois vraiment pas qu'est ce qu'il faut mettre où..... (j'essaie pourtant!)

| Alerter

Répondre à Djorge84