VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

[alexsamurai]

Bonjour à vous!
étant débutant en vba, j'ai quelques petits soucis à résoudre le problème suivant:

La société souhaite :
- Saisir le chiffre d’affaires de chaque vendeur dans une feuille intitulée Saisie CA à l'aide d'un formulaire.
- Que le formulaire contienne des menus déroulants permettant de sélectionner les secteurs les magasins et les vendeurs.
- Que l'utilisateur puisse alimenter les menus déroulant sans saisir dans la feuille EXCEL.
- Qu'après avoir cliqué sur un bouton, l’application ajoutera dans chaque feuille (une feuille par vendeur), le cumul du CA par secteur et par magasin avec bien sûr la date.
- Un bouton devra être présent pour effacer la saisie du jour-1. En revanche, ce bouton n’effacera pas le contenu des feuilles correspondant à chaque représentant.


Les trois premières questions, c'est ok, mais la ou ça pose problème c'est la quatrième, je ne vois pas du tout comment procéder... J'espère que l'un d'entre vous pourra me donner un coup de main! merci d'avance!

 

[vmax01]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

bonjour alexsamurai et bienvenue

un petit fichier exemple serai le bienvenue... essai de donner un bout de ton classeur sans les valeur réelle car là c'est dure de t'aider ....

bonne journée.

 

[Denis]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

Bonjour le fil,
un élément de réponse peut-être a adapter :

Sub macro()
With Worksheets("feuil2")
.Range("a1") = Application.WorksheetFunction.Sum(.Range("b1:b5"))
.Range("a2") = Format(CDate(Date), "dd-mm-yyyy")
End With
End Sub

Bon courage et à+
Denis

 

[alexsamurai]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

salut!
Merci d'avoir répondu si rapidement!
Je voudrais essayer d'être plus clair pour ce qui est de mon soucis!

en gros, l'idée:

Quand on ouvre le fichier excel, un formulaire s'affiche, on entre la date, le ca, le secteur, le magasin et le nom du vendeur.
Quand c'est fait, cela ajoute une ligne dans une feuille. Une fois qu'on a rentré tout ce qu'on voulait, il faut cliquer sur un bouton, qui va sélectionner les données de la feuille, et créer autant de feuilles qu'il y a de vendeurs différents.

Donc, les lignes correspondantes au même vendeurs seront regroupées dans la même feuille. De plus, les CA devront être cumulés si le secteur et le magasin sont les mêmes... Voila voila, j'espère que je suis clair!

Pour ce qui est de la proposition de denis, cela ne permet d'être automatisé. Mon problème c'est que je ne sais pas trop comment dire que la macro scan chaque ligne de mon formulaire, vérifie le nom, si c'est un nouveau nom, elle ajoute une feuille ayant le nom du vendeur, sinon, elle l'ajoute dans la feuille qui a le nom du vendeur correspondant, puis elle somme les données identiques(même secteur, et même magasin).

Merci encore pour le temps consacré à me répondre, et à m'aider! je joins le fichier vierge pour que vous voyez la maquette a laquelle on doit arriver, ça sera plus clair je pense pour montrer ce que je veux faire.la feuille que j'ai faite c'est "saisie ca". Si vous avez besoin de la macro que j'ai faite pour y voir plus clair, pas de soucis
En tout cas, c'est vraiment sympa, même si je me répète!

 

[vmax01]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

bonjour alexsamurai et bonjour le forum

voici un premier jet tout fonctionne sauf que je n'ai pas encor fait le tri et les mises en forme..... si tu te sent le courrage de continuer le code tu peux.

bonne journée.

edit: tu peux enlever et supprimer la ligne de code et la variable "essai" elle me servait juste pour voir mon resustat.... donc elle ne sert plus.

 

[vmax01]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

re bonjour le fil

voila j'ai terminé tout a l'aire de fonctionner.
le tri, les bordure et la couleur d'enete.... tout est fait.

bon c'est pas de la pur macro de pro mais bon ça marche et ça tiens la route.

bonne journée.

 

[Denis]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

Bonjour Alex, Vmax, le forum et meilleurs voeux à tous
Ci-joint une petite application vba avec mes délires du réveillon.
Pas de mise en page comme l'a fait Vmax mais le coté saisie me semble plus simple (choix dans des listes).
Pour ma part en excel2000, j'ai du ajouter des références dans vba, pour toi en 2010 à voir.
Relance le fil si besoin de modif ou un bug (testé mais sait on jamais).
Bon courage et à+
Denis

 

[alexsamurai]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

Bonne année à vous tous!

C'est assez ouf ce que vous avez fait les gars.. Je suis en train de regarder c'est super!
Denis, tu as refait l'ensemble du problème, c'est très gentil! maintenant, mon tableau fait pâle figure... lol.
Je pense que je vais garder cependant la version de vmax qui est plus proche de la façon dont on a travaillé en cours. Je vous redis ça je finis de regarder tout ça, je voudrais comprendre tout! merci en tout cas... Combien de temps de VBA pour arriver à ce niveau?^^

 

[vmax01]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

bonjour le fil.

perso je préfère le version de denis même si je ne comprend pas le quart de la moitié de ses codes.....

de plus je n'avais pas lue tes indication jusqu'au bout alexsamurai car je n'avais pas vue que tu voulais un USF avec menu déroulant, et j'avoue que ça aurai été plus simple au niveau code que ce que je t'ai fait.
si tu veux je peux essayer de te faire une version pourquoi pas (un peut comme celle de denis c'est toi qui voit).

pour ce qui est d'explications reviens sur le fil et je t'expliquerai ce que j'ai fait et a quoi ça sert.

Bonne journée.

 

[alexsamurai]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

La version de Denis est sympa oui, mais trop complexe pour moi, je préfère la tienne qui est plus en accord avec ce que j'ai pu voir. (merci encore pour ton travail, je regarderai plus en détail ta version quand j'aurais plus de temps, car il y a plein de choses que je ne connais pas, et je trouve cela intéressant )Il y a d'ailleurs des bugs que je ne sais pas vraiment corrigé, peut être du au fait que j'utilise excel 2010?

Sinon, vmax, ton fichier, c'est exactement ce que je demandais ^^ (c'est quoi un usf??) Par contre, je veux bien que tu me l'expliques un peu, j'ai un peu de mal à tout comprendre, il y a trop de boucles. Et des fonctions que je ne sais pas trop utiliser, comme la fonction countif.
Et à quoi sert vraiment .row?
Si cela t'embêtes cela ne fait rien, je te suis déjà très reconnaissant!

Afin de parfaire ce code, il faudrait que j'ajoute une petite modification.

Saurais tu comment je pourrais faire en sorte que si je clique sur le bouton pour répartir les représentants une deuxième fois, il n'y ait pas de bug? en effet, si je clique deux fois de suite, cela ne fonctionne pas, il faut que j'efface les nouvelles feuilles si je veux relancer la procédure... autrement dit, si j'ajoute un représentant dans mon tableau Saisie CA, et que je clique à nouveau sur le bouton, cela ne marchera pas sauf si je supprime toute les feuilles nouvellement crées.

run time error 1004
cannot rename a sheet to the same name as another sheet,...

j'ai pensé à effacer les feuilles pour les recréer à chaque fois que l'on reclique. ça résout bien le problème, mais cependant, cela ne résout pas la question posée puisque les feuilles doivent présenter un historique. (je suis pas sur d'être clair^^)

 

[vmax01]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

bonsoir alexsamurai et bonsoir le forum

pour les petites explications effectivement quand tu redémare la sub... comme les feuilles sont déjà crées, il ne peut pas renommer des feuilles qui existent déjà.... d'ou le bug.

pour le countif c'est l'équivalent de la formule nb.si() et je me suis servi de cette fonction pour eviter de marquer les doublons, donc a chaque fois qu'il trouve un doublon il passe a la ligne suivante et si il trouve une valeur sans doublon il continue la boucle normalement.

pour le .row cela permet d'identifier le N° de ligne d'une cellule donc quand tu vois .row c'est que je lui demande le N° de ligne et non la reference de la cellule... ex: range("B2") donne range("B2").row retourne donc la 2em ligne.

un USF c'est un userform comme ce qu'a fait denis

pour ton bug colle ce code a la place de l'autre j'ai rajouté des lignes qui verifie si tu as déjà tes feuilles et si oui je les suprime et relance le code pour repatire de zéro.(et c'est ce que tu imaginais donc je suis partis sur cette idée).

Sub test()
Dim i, j, k, l, M, O, P As Integer
Dim Somme, Total As Long
Dim cel As Range
Dim Feuil As Worksheet
Application.ScreenUpdating = False
If Worksheets.Count > 1 Then
    For Each Feuil In ThisWorkbook.Worksheets
        If Feuil.Name <> "Saisie CA" Then
        Application.DisplayAlerts = False: Feuil.Delete: Application.DisplayAlerts = True
        End If
    Next
End If
For Each cel In Sheets("Saisie CA").Range("B3:B" & Sheets("Saisie CA").[b65000].End(xlUp).Row)
    For i = 3 To Sheets("Saisie CA").[b65000].End(xlUp).Row
        If WorksheetFunction.CountIf(Sheets("Saisie CA").Range("B3:B" & i + j), cel) = 1 Then
            With Worksheets.Add
                .Name = cel: .Cells(1, 1) = "Nom du Représentant": .Cells(1, 4) = cel
                .Cells(2, 1) = "Date": .Cells(2, 2) = "Secteur": .Cells(2, 3) = "Magasin": .Cells(2, 4) = "CA Réalisé"
                For k = .Cells(cel.Row, 2).Row To Sheets("Saisie CA").[b65000].End(xlUp).Row
                    If Sheets("Saisie CA").Range("B" & k) = cel And _
                    WorksheetFunction.CountIf(.Range("B3:B" & 3 + l), Sheets("Saisie CA").Cells(cel.Row + M, 1)) < 1 Then
                        .Cells(3 + l, 1) = CDate(Sheets("Saisie CA").Range("B1"))
                        .Cells(3 + l, 2) = Sheets("Saisie CA").Cells(cel.Row + M, 1)
                        .Cells(3 + l, 3) = Sheets("Saisie CA").Cells(cel.Row + M, 3)
                        For O = .Cells(cel.Row, 2).Row To Sheets("Saisie CA").[b65000].End(xlUp).Row
                            If Sheets("Saisie CA").Cells(cel.Row + P, 1) = Sheets("Saisie CA").Cells(cel.Row + M, 1) And Sheets("Saisie CA").Cells(cel.Row + P, 2) = cel Then
                                Somme = Sheets("Saisie CA").Cells(cel.Row + P, 4)
                                Total = Total + Somme
                            End If
                            P = P + 1
                        Next
                        .Cells(3 + l, 4) = Total
                        l = l + 1
                        P = 0
                        Somme = 0
                        Total = 0
                    End If
                    M = M + 1
                Next
                Exit For
            End With
        End If
        Exit For
    Next
    With ActiveSheet
        .Range("B3:D" & [D65000].End(xlUp).Row).Sort key1:=.Range("B3:B" & [D65000].End(xlUp).Row)
        .Range("A2:D" & [D65000].End(xlUp).Row).Borders.LineStyle = 1
        .Range("A2:D2").Font.Color = RGB(0, 0, 200): .Range("A2:D2").Font.Bold = True
    End With
    j = j + 1
    k = 0: l = 0: M = 0
Next
Sheets("Saisie CA").Select
Application.ScreenUpdating = True
End Sub

si tu as d'autre questions n'hésite pas
bonne soirée

 

[Denis]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

Bonjour le fil,
pas mal ton code Vmax, mais je pensais que les feuilles vendeurs devaient conserver un historique.
Auquel cas il faudrait boucler sur le nom des vendeurs puis sur celui des feuilles pour créer les manquantes.
Bon courage et à+
Denis

 

[vmax01]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

bonsoir Denis.

Merci, mais j'imaginais plus le truc comme tu l'a fait car je serai parti plus sur ton idée.... maintenant si alexsamurail reçoit des bases de donnée qui viennent d'ailleur je peut comprendre ça démarche car cela reste juste du trie.

d'autre par toi qui t'y connais plus que moi.... sur la derniere boucle en fait je voulais utiliser la fonction sumproduct (pour le CA)mais sans succes, j'avais toujours des erreurs.... comme je n'ai pas réussi je me suis rabattu sur une boucle.
si tu pouvais m'éclairer sur cette fonction en vba je suis preneur. (car meme sur des forums je n'ai pas trouvé de solution).

bonne soirée.

 

[Denis]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

Re
je te filerais un petit tuto mais, demain AM.
Pour ce soir, je ferme la boutique (lol)
Bonne soirée

 

[alexsamurai]

Re : VBA, ajout d'une feuille+Filtre+Somme de Chiffre d'affaires

rebonsoir!

Justement oui, il faut bien garder un historique, et donc ne pas supprimer les feuilles pour résoudre le bug, mais utiliser un autre moyen... J'avais fait un truc similaire a toi vmax,(en moins bien ) mais j'aimerais ne pas effacement les feuilles nouvellement créees et garder l'historique.