• Publicité

création d'un lien vers feuille dont le nom est changeant

Dans ce forum, vous pouvez poser toutes vos questions concernant l'utilisation de logiciels de bureautique (Microsoft Office, Open Office, etc.)

Modérateurs: Sebastien, Stéphane, Christophe

création d'un lien vers feuille dont le nom est changeant

Messagepar dubdub22 » 10 Jan 2012 14:41

Bonjour à tous,

Je reviens à nouveau vers vous pour un problème de liaison vers une feuille excel dans le même fichier.

Je détaille :
J'ai une feuille qui me sert de formulaire dans laquelle je rentre des infos, que j'appelle devis.
Sur cette feuille, j'ai une macro, qui dans un premier temps copie le nom et prénom qui se trouve dans la celulle "D9" et la date qui se trouve en "A11" vers une autre feuille que j'appelle "listedevis", ensuite la macro continue, elle crée une copie du devis et nomme cette copie en fonction du contenu de la celulle "D9" de la feuille "devis".
Les copies ont donc des noms différents à chaque devis, et que je ne connais pas à l'avance.

Ce que je cherche, c'est comment créer un lien pour pouvoir ouvrir ces devis depuis la feuille " listedevis" sachant que les feuilles seront masquées, donc tout se passera par macros et clics.

Autre petit souci, quelle formule il me faudrait, pour qu'après avoir copié les données dans "listedevis", mon curseur revienne se placer sur la dernière celulle pleine de la colonne "A", pour que celà ne décale pas les données.

Je vous joins le fichier à cette adresse : http://cjoint.com/?BAkoKyDIA5i



Merci d'avance de votre aide à nouveau.

Dubdub
" Ont hérite pas de la terre de nos ancêtres, mais ont utilise celle de nos enfants " (St. Exupérit)
Avatar de l’utilisateur
dubdub22
Carotteur
Carotteur
 
Messages: 32
Enregistré le: 17 Fév 2011 12:02
Localisation: Côtes d'Armor

Re: création d'un lien vers feuille dont le nom est changea

Messagepar DeVline » 11 Jan 2012 00:40

Bonsoir Dubdub,

Pour le premier point, un exemple de code à inégrer dans le code de la feuille "Listedevis", qui affiche et sélectionne l'onglet choisi par double clic sur une cellule colonne A:

Code: Tout sélectionner
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Lig As Long, Ws As Worksheet
With ActiveSheet
    'calcul de la dernière ligne remplie colonne A
    Lig = WorksheetFunction.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 2)
    'Déclenchemet de la macro si double clic dans la zone remplie colonne A de lige 2 à Lig
    If Not Intersect(Target, .Range("A2:A" & Lig)) Is Nothing Then
       'gestion d 'erreur si l'onglet portant le nom de la valeur de la cellule n'existe pas
        On Error Resume Next
        Set Ws = ThisWorkbook.Worksheets(Target.Value)
        On Error GoTo 0
         
        If Ws Is Nothing Then
            MsgBox "Ce devis n'existe pas"
            Exit Sub
            Else
            'si l'onglet existe de rend visible et le sélectionne
            With Sheets(Target.Value)
                .Visible = True
                .Select
            End With
        End If
    End If
End With
End Sub


Il faudra par la suite intégrer un code dans les onglets fixes, comme liste devis, liste clients, etc ..., du type Worksheet_Activate() qui à la sélection de ces onglets masquera tous les onglets du classeur sauf ceux là, afin que tous les devis se masquent automatiquement.

Pour le second point, je calcule dans le code ci-dessus la dernière ligne remplie colonne A avec ceci:

Code: Tout sélectionner
Cells(Rows.Count, 1).End(xlUp).Row


pour calculer la première ligne vide on peut juste ajouter 1(mais le Offset(1,0) est très bien aussi):

Code: Tout sélectionner
Cells(Rows.Count, 1).End(xlUp).Row + 1


On peut juste à titre d'exemple simplifier ce code:
Code: Tout sélectionner
'Je reviens sur la "fiche enregistrement clients"'
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("Fiche enregistrement clients").Select
   
'Je resélectionne les données entrées manuellement, et qui vont de "A4 à J15", et je les copient'
    Range("A4:J15").Select
    Selection.Copy

'Je vais sur la feuille "Liste clients" et je me place sur la première ligne vide'
    Sheets("Liste Clients").Select
    ActiveCell.Offset(1, 0).Select
   
'Je me place sur la celulle "A2" et je colle les données de la feuille "fiche enregistrement clients"'
   
    ActiveSheet.Paste


en éliminant les Select inutiles et en utilisant une variable Lig comme ceci:
Code: Tout sélectionner
Dim Lig As Long

Lig = Sheets("Liste clients").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Fiche enregistrement clients").Range("A4:J15").Copy Sheets("Liste Clients").Range("A" & Lig)


En espérant que cela pourra vous aider,

Très bonne soirée,

D
DeVline
Futur Geek
Futur Geek
 
Messages: 112
Enregistré le: 29 Mar 2010 12:36

Re: création d'un lien vers feuille dont le nom est changea

Messagepar dubdub22 » 15 Jan 2012 22:36

Bonsoir DeVline,

Tout d'abord merci de votre aide.
Votre formule fonctionne très bien, cela fonctionne à merveille.

En revanche j'ai deux petits soucis, l'un sur un code que j'ai fait pour transformer un devis en facture, il fonctionne bien à condition que je l'exécute avec le devis qui m'a servi à faire la macro, mais si je veux exécuter la macro avec un devis nommé différemment, là il ne fonctionne pas, auriez vous une idée sur le code que je devrais mettre pour qu'il fonctionne avec n'importe quel devis ?

Voici le code:

Sub DevisFacture()
'
' DevisFacture Macro
'

'
Range("B14,B17:B37,D17:D37,E39").Select
Range("E39").Activate
ActiveWindow.SmallScroll Down:=-9
Range("B14").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Facture").Select
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("AW866PGDEV-8").Select 'là, si j'ai un devis qui se nomme "1171VH22DEV-9" , cela ne fonctionne pas
Range("B17:B37").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Facture").Select
Range("B17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("AW866PGDEV-8").Select
Range("D17:D37").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Facture").Select
Range("D17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("AW866PGDEV-8").Select
Range("E39").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Facture").Select
Range("E39").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AW866PGDEV-8").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Facture").Select
ActiveWindow.SmallScroll Down:=-15
Range("B14").Select
End Sub


Ensuite je voudrais effectuer une copie des références et des désignations qui se trouvent sur la facture, vers une fiche client, est-ce que d'après vous cela est possible ?

Merci par avance de votre aide.

Cordialement

Dubdub
" Ont hérite pas de la terre de nos ancêtres, mais ont utilise celle de nos enfants " (St. Exupérit)
Avatar de l’utilisateur
dubdub22
Carotteur
Carotteur
 
Messages: 32
Enregistré le: 17 Fév 2011 12:02
Localisation: Côtes d'Armor

Re: création d'un lien vers feuille dont le nom est changea

Messagepar DeVline » 19 Jan 2012 14:10

Bonjour Dubdub

Pour le nom de l'onglet, s'il provient de la facture il y a donc un identifiant client sur cette facture, par exemple en A1, identifiant qui correspond si j'ai bien compris au nom de l'onglet.
Il est possible de définir une variable prenant la valeur de cet identifiant, par exemple :
Code: Tout sélectionner
Dim Idcli As String
Idcli = ActiveSheet.Range("A1")


Ensuite pour sélectionner l'onglet il suffit d'écrire:
Code: Tout sélectionner
Sheets(Idcli).Select

(Comme vu dans le post précédent il peut-être intéressant ici de tester l'existance de l'onglet Idcli)

Pour la question suivante, cela semble tout à fait posible, ceci dit, cela sera d'autant plus facile que les données sont organisées proprement.
D'après ce que je comprends, vous aurez à terme dans ce fichier autant d'onglets que de clients, plus autant d'onglets que de factures, de devis .... ce qui risque de faire un peu beaucoup.
Avis qui n'engage que moi, il me semble qu'il serait plus ergonomique pour la partie clients par exemple de ne pas créer un onglet par client, mais plutôt une base de données regroupant les informations de facturation dans un seul onglet. Un simple filtre permet ensuite facilement de consulter les données d'un client choisi.

Bien à vous,

D
DeVline
Futur Geek
Futur Geek
 
Messages: 112
Enregistré le: 29 Mar 2010 12:36

Re: création d'un lien vers feuille dont le nom est changea

Messagepar dubdub22 » 23 Jan 2012 11:12

Bonjour DeVline,

Je vous remercie de votre réponse et de vos remarques.

Pour ce qui est de votre réponse, cela correspond à ce que je voulais, je dis "voulais", car vos remarques m'ont fait réfléchir et elles se sont avérées exactes.

En effet ce petit programme, est destiné à un de mes enfants qui vient de reprendre un garage automobile, et son prédécesseur avait entre 5 et 600 clients, et mon fils qui s'était installé comme auto-entrepreneur à une dizaine de kilomètres de là, s'était fait une clientèle d'environ une centaine de personnes, si j'ajoute 600 + 100 j'obtiens 700 fiches de suivi clients, x par une facture en moyenne tous les deux mois, soit 1400 factures minimum, et les devis en plus, les fiches de gestion des stocks, effectivement ça donne à réfléchir.
Après vos remarques j'ai testé le nombre de feuilles sur excel 2007, et je suis arrivé à 2300 feuilles vierges, là je me suis arrêté, mais je n'ai pas envie de lui faire ce programme et qu'il reste planté en cours de route par un manque de place.

Je vais donc opter pour votre suggestion d'une base de donnée, suivi d'une feuille d'analyse avec un tableau croisé dynamique, ce qui lui permettra d'avoir un suivi de client par la référence de la plaque minéralogique, et bien d'autres renseignements.

Je ne voudrais pas abuser, mais j'aurai juste un petit renseignement à vous demander, (après je pense que j'aurai résolu mes problèmes) comment je peux faire pour entrer le contenu de ses bons de livraisons dans son stock, sachant qu'il a 2 403 références.

Je pars sur la base d'une feuille excel, que je nommerais "Formulaire Entrée", dans laquelle je recopierai manuellement les données du bon de livraison, et je voudrais que par une macro l'ensemble des données que j'ai recopiées, s'inscrivent sur la plage nommées "entrées", de la base de données, dans la colonne correspondant à la référence de l'article, et sur la première cellule vide, (pour ce qui est de cette dernière manip vous m'avez indiqué le code qui fonctionne très bien, il n'y a pas de problème), La plage de données est elle même divisée par familles, comme, (pneumatique, huile, etc....) et ensuite je réinitialiserai la feuille "Formulaire Entrées".

Merci encore de votre aide, passée et future, et de m'avoir ouvert les yeux sur les difficultés que j'aurais peut-être rencontrées.

Cordialement

Dubdub
" Ont hérite pas de la terre de nos ancêtres, mais ont utilise celle de nos enfants " (St. Exupérit)
Avatar de l’utilisateur
dubdub22
Carotteur
Carotteur
 
Messages: 32
Enregistré le: 17 Fév 2011 12:02
Localisation: Côtes d'Armor

Re: création d'un lien vers feuille dont le nom est changea

Messagepar DeVline » 27 Jan 2012 00:59

Bonsoir Dubdub,

Ce n'est à priori pas difficile d'alimenter un onglet à partir d'un autre, mais j'ai un peu de mal à vous répondre sans rien de concret sous les yeux.
Peut-être pouvez-vous préparer trois choses, d'un coté une mini base produits en validant bien qu'il ne manque aucun champ d'information, ensuite l'onglet qui récoltera les entrées en stock (sans doute aussi le stock initial et les sorties), enfin le masque du formulaire qui servira à l'alimenter?
Ce sera plus facile de se représenter le travail à effectuer avec tout ça.

Bon courage et à bientôt,

D
DeVline
Futur Geek
Futur Geek
 
Messages: 112
Enregistré le: 29 Mar 2010 12:36

Re: création d'un lien vers feuille dont le nom est changea

Messagepar dubdub22 » 13 Fév 2012 15:41

Bonjour DeVline,

Je vous remercie pour vos conseils, je suis désolé du retard dans ma réponse, mais un petit souci de santé m'a écarté de mon ordinateur quelques temps, maintenant je suis revenu, je vais reprendre ce que j'ai commencé.

Encore merci, pour vos conseils et aide.

Cordialement

Dubdub
" Ont hérite pas de la terre de nos ancêtres, mais ont utilise celle de nos enfants " (St. Exupérit)
Avatar de l’utilisateur
dubdub22
Carotteur
Carotteur
 
Messages: 32
Enregistré le: 17 Fév 2011 12:02
Localisation: Côtes d'Armor

Re: création d'un lien vers feuille dont le nom est changea

Messagepar dubdub22 » 17 Fév 2012 13:17

Bonjour DeVline,
Je reviens vers vous, je n’ai pas réussi à faire comme vous me l’avez conseillé, car je crois que je suis fâché avec les formulaires.

J’ai réussi à faire malgré tout un programme pas trop mal, qui fonctionne dans sa partie la plus simple, c'est-à-dire , Devis, Facturation, remplissage des journaux de caisse, de ventes et, les entrées, même si j’ai l’impression parfois de passer par Marseille pour aller à Lille en partant de Bretagne.
(mais bon, mon fils sait que je ne suis pas programmeur, et comprend que cela ne soit pas tip top comme le ferai un pro)

Mon plus gros problème c’est de faire quelque chose qui ne demande pas trop longtemps à, s’ouvrir et s’enregistrer ainsi qu’à se refermer.
C’est pourquoi, j’ai fait plusieurs classeurs indépendant les uns des autres, et je suis passé de 8 minutes d’ enregistrement, à 1minute 40, mais cela à un inconvenant sur les stocks, car 1 minute 40 multiplié par 21 feuilles, cela fait un bout de temps pour les ouvrir et les refermer pour la mise à jour des stocks.

En effet pour les sorties j’ai fait un classeur (donc une feuille) par famille, sachant que mon fils m’a donné 21 familles de produits, contenant chacune 200 références possibles, ( soit 210 colonnes et 25 000 lignes taille de chaque classeur 31.1 Mo)et autant de fichiers pour les entrées, identiques en taille mais plus léger (12.5Mo) car pas de formules dedans sauf celle du total.

En mettant toutes les familles sur une seule feuille cela me représentait un total de 2500 colonnes par 25000 lignes, qui contenaient une formule dans chaque cellule.

Ce que je cherche mais ne trouve pas, c’est une formule VBA qui me permettrait d’envoyer dans la colonne « P », dans la première cellule vide, de la feuille stock, le résultat de la cellule « C4 » de la feuille sorties, si le contenu de la cellule « A 4 » de cette même feuille sorties, correspond à la cellule « P1 » de la feuille stock
Exemple :

Feuille Sortie

Cellule A4 = batterie 50Ah
Cellule C4 = 1

Feuille Stock

Cellule P1 = batterie 50 Ah

Je voudrais que le chiffre 1 se range dans la colonne « P » à la première ligne vide.

Sachant que j’utilise 200 colonnes sur la feuille « Stock » et que les sorties risquent , d’après les prévisions, d’aller de A4 à C25 000

Autre problème

Existe-t-il une formule qui me permettrait de copier coller que le contenu des cellules, et de ne pas prendre en compte les cellules contenant des formules ?

J’ai utilisé une macro en faisant copier les valeurs, mais cela prend quand même en compte les cellules contenant les formules, cela me crée un décalage , d’autant de cellules qui sont vides, ce qui est très gênant.
Et dernier problème, Est-il possible en VBA, de faire recopier autant de fois la date et le n° Immatriculation sur une feuille, qu’il n’y a de lignes remplies ?
Exemple : Colonne « A » date Colonne « B » Immatriculation et colonne « C » Désignation
1 17/02 xxxxxxxx AAAAAAAA
2 a ajouter a ajouter BBBBBBBBB
3 a ajouter a ajouter CCCCCCCCC

Merci à nouveau d’avance pour votre aide.

Cordialement

DUBDUB
" Ont hérite pas de la terre de nos ancêtres, mais ont utilise celle de nos enfants " (St. Exupérit)
Avatar de l’utilisateur
dubdub22
Carotteur
Carotteur
 
Messages: 32
Enregistré le: 17 Fév 2011 12:02
Localisation: Côtes d'Armor

Re: création d'un lien vers feuille dont le nom est changea

Messagepar DeVline » 28 Fév 2012 00:40

Bonsoir Dubdub22,

Nous nous éloignons de plus en plus du sujet initial du fil ...
En vous lisant, j'ai l'impression que vous avez fait un énorme travail mais que l'organisation de vos données a produit un monstre qui sera difficilement gérable.
Vous semblez avoir une belle volumétrie, je ne sais si Excel est le plus approprié mais pourquoi pas, Excel 2007 est puissant, ceci dit quel que soit l'outil avant de trop avancer réfléchissez bien à la structure de vos données.

Bonne continuation pour votre projet,

D
DeVline
Futur Geek
Futur Geek
 
Messages: 112
Enregistré le: 29 Mar 2010 12:36


Retourner vers Forum d'entraide : Bureautique

 


  • Articles en relation
    Réponses
    Vus
    Dernier message

Qui est en ligne

Utilisateurs parcourant ce forum : Aucun utilisateur enregistré et 4 invités

  • Publicité