recalculer feuille selon valeur cellule et creation d'une nouvelle feuille

[KIM]

Bonjour le forum,
Pour automatiser les taches ci-dessous j'ai besoin de votre aide et vous en remercie d'avance.
J'ai créé un tableau recapitulatif général des dépenses de projets par service (voir pièce jointe). les formules utilisées dépendent de la valeur de la cellule D3 nommée "serv". Dans l'exemple je n'ai pas mis de formules.
J'ai récupéré le noms des services en creant une liste sans doublons de la col C (à partir de C6:...) de la feuille DATA et j'ai créé une liste déroulante pour la cellule D3.
A chaque modification de la cellule, je recalcule la feuille (en tapant sur F9) et je la recopie dans une nouvelle feuille renommée de la valeur de la variable "serv".
Je souhaite automatiser ces taches par vba et j'ai besoin de votre aide.
Est-ce possible dans une macro de:
1- definir la liste sans doublons des services dans un tableau avec comme 1er element de ce tableau le caractère "*" et ensuite les services trouvés
2- Pour chaque élément de cette table,
a- attribuer à la case D3 la valeur de cet element,
b- recalculer les formules de cette feuille DEP_SV
c- recopier la feuille sans formules dans une nouvelle feuille et renommer cette nouvelle feuile du nom: valeur de la variable "serv" suivi de "_Projets"
NB pour le nom du service "*", la feuille sera nommée DEP_Projets
Merci d'avance,
Amicalement
KIM

 

[Hervé]

Re : recalculer feuille selon valeur cellule et creation d'une nouvelle feuille

bonjour kim

une proposition en pièce jointe.

attention j'ai ajouté des formules pour les tests.

salut

 

[KIM]

Re : recalculer feuille selon valeur cellule et creation d'une nouvelle feuille

Bonjour Hervé et le forum,
merci de ta reponse si rapide, elle repond à ma demande.
Pour le calcul automatique,
Est-ce que le fait de mettre la ligne
Application.Calculation = xlAutomatic
que le calcul se fait automatiquement sur chaque élément de la collection?

Sinon, pour mon information, quuel est le code à rajouter pour recalculer la feuille courante et non le classeur ? car j'ai un classeur avec beaucoup de feuilles et de formules et j'ai peur qu'à chaque modif il doit recalculer toutes les feuilles du classeur. peut-etre je me trompe.
Merci d'avance
Amicalement
KIM

 

[Hervé]

Re : recalculer feuille selon valeur cellule et creation d'une nouvelle feuille

re kim

j'avais pas pensé à cette eventualité, code modifié et simplifié :

Sub Bouton2_QuandClic()
Dim data As New Collection
Dim i As Integer
Dim c As Range

data.Add "*"


'collection sans doublons des services
With Sheets("data")
    On Error Resume Next
    For Each c In .Range("c6:c" & .Range("c65536").End(xlUp).Row)
        data.Add c, CStr(c)
    Next c
    On Error GoTo 0
End With

'boucle sur chaque élément de la collection
For i = 1 To data.Count
    Sheets("DEP_SV").Range("d3") = data(i) 'place le nom du service
    Sheets("DEP_SV").Copy After:=Sheets(Sheets.Count) 'copie la feuille
    With ActiveSheet
        .Calculate
        .Name = IIf(i = 1, "DEP_Projets", data(i) & "_Projets") 'renomme la feuille
        .Range("f8:k24") = .Range("f8:k24").Value 'colle en valeur
    End With
Next i

End Sub

place le bouton de lancement sur la feuille data, ceci t'evitera de le voir se copier sur chaque feuille.

supprime les macros2,3 et 4 de mon fichier, ce ne sont que des résidus de macros enregistrées.

salut

 

[KIM]

Re : recalculer feuille selon valeur cellule et creation d'une nouvelle feuille

Re,
Merci Hervé et le forum
Bonne journée
Amicalement
KIM