ERROR 1004 : Variable dans une fonction dans une boucle

Bonjour,

Je rédige un code pour me faire gagner beaucoup de temps.

J'ai un groupe d'une centaine de classeurs, je dois sélectionner une feuille spécifique dans chacun d'entre eux et la coller dans un n-ieme classeur de calculs. J'arrive a construire mon classeur de calcul mais pas à effectuer les operations entre les feuilles.

Principe logique de mon code :

1) ouvrir un fichier central dit modele

2) Selectionner les fichiers xls a calculer dans le fichier central

3) Pour chacun des fichiers :

i) Copier une feuille spécifique vers le fichier central

ii) fermer le fichier source

iii) selectionner des cellule dans la feuille nouvellement copiée

iv) intégrer ces cellules dans une formule dans une autre feuille du fichier de calcul

v) passer au fichier suivant

Voici le bout où le code bugge

       Worksheets("Extraits de données").Range("F" & b).FormulaLocal = "=SOMME('" & NomEtablissement & "'!B31;'" & NomEtablissement & "'!B45;'" & NomEtablissement & "'!B46;'" & NomEtablissement & "'!B51;'" & NomEtablissement & "'!B55)"

Il parvient a effectuer l'opération pour i=1 mais pas pour i=2. Je ne comprends pas pourquoi

Comme on peut lire, il s'agit d'une fonction se réfère à une feuille variable sont l'avancement de la boucle.

Code complet :

Option Explicit
Option Base 0
Sub Projet_Mairie_Paris()
Dim Modele As String
Dim ModeleS As String
Dim Chemin As String
Dim i As Integer
Dim b As Integer
Dim NomEtablissement As String
Chemin = "CONFIDENTIEL" 

Modele = "C:\Users\gleize\Desktop\01022019 Modèle ABC.xlsx"
ModeleS = "01022019 Modèle ABC.xlsx"
Application.Workbooks.Open Modele

With Application.FileDialog(msoFileDialogFilePicker)
    'Définit un titre pour la boîte de dialogue
    .Title = "Choisir les fichiers BASE DE DONNÉES EXCEL"
    'Empêcher la multi-sélection
    .AllowMultiSelect = True
    'Répertoire par défaut suivi du type de fichier par défaut
    .InitialFileName = Chemin
    .Show

    'Affiche la boîte de dialogue
    Dim CheminFichierSource As String

    For i = 1 To .SelectedItems.Count

        CheminFichierSource = .SelectedItems(i)
        Application.Workbooks.Open CheminFichierSource
        'Récuperer les infos importantes du fichiers source
        NomEtablissement = Sheets("Réception BP Asso").Range("B2").Value
        Dim NomGestionnaire As String
        NomGestionnaire = Sheets("Réception BP Asso").Range("B1").Value
        Dim NomFichierSource As String
        NomFichierSource = ActiveWorkbook.Name
        NomEtablissement = Sheets("Réception BP Asso").Range("B2").Value
        'Copier la feuille info vers le fichier cible
        Workbooks(NomFichierSource).Sheets("BP Retenu").Copy After:=Workbooks(ModeleS).Worksheets(2)
        Workbooks(ModeleS).Sheets("BP Retenu").Name = "BP " & NomEtablissement
        'Fermer le fichier source et activer le fichier cible
        Workbooks(NomFichierSource).Close savechanges:=False
        Workbooks(ModeleS).Activate

        ' Début Calcul dans la feuille Extraits de données
        b = i + 4

        Worksheets("Extraits de données").Range("B" & b).Value = NomEtablissement
        Worksheets("Extraits de données").Range("C" & b).Value = NomGestionnaire
        NomEtablissement = Worksheets(3).Name

       Worksheets("Extraits de données").Range("F" & b).FormulaLocal = "=SOMME('" & NomEtablissement & "'!B31;'" & NomEtablissement & "'!B45;'" & NomEtablissement & "'!B46;'" & NomEtablissement & "'!B51;'" & NomEtablissement & "'!B55)"

    Next i
End With

End Sub

'TOUS les docs ouverts

Bonjour,

Le VBA ne comprend que l'anglosaxon. "SOMME" doit être remplacé par SUM.

Par ailleurs, vous devriez tester le cas où aucun fichier n'est sélectionné

   
   .InitialFileName = Chemin
    if .Show = 0 then
           Msgbox "pas de fichier sélectionné : sortie procédure" : Exit Sub
    Else
          .............................................
    End if

Merci pour votre réponse.

Il me semble que la méthode FormulaLocal autorise l'emploi du français "Somme" pour "sum".

D'ailleurs comme précisé, la boucle est fonctionnelle pour i=1, c'est à partir de 2 que cela ne fonctionne plus.

Enfin la selection du fichier ne pose pas de probleme.

Eurogroup a écrit :

Il me semble que la méthode FormulaLocal autorise l'emploi du français "Somme" pour "sum".

C'est vrai, j'ai lu rapidement.

Pour votre bug, ce qui semble le plus probable, c'est que NomEtablissement ne soit pas une feuille dans votre classeur.

Vous pouvez le vérifier ainsi :

dim WS as worksheet
set WS = Sheets(NomEtablissement ) 

cette instruction ne doit pas générer d'erreur

Bonjour,

Oui il y avait un problème dans le nom de la feuille !!

Merci !