Macro, erreur d'éxecution 9

[Nata]

Bonjour à tous,

J'ai besoin de réaliser une macro, mais le vba est complètement nouveau pour moi et je suis un peu dépassée! J'ai passé de nombreuses heures sur Internet à découvrir le monde des macros, mais je suis encore loin d'être capable de me débrouiller seule.

Je dois donc trouver une solution pour que des données de plusieurs fichiers sources (réunis en un répertoire) soient copiées dans un fichier destination, sans doublons bien sûr. L'objectif est que je puisse "appuyer sur un bouton" tous les mois pour que le fichier destination s'actualise seul, à partir des fichiers sources que j'aurai collecté.

J'ai donc trouvé un code qui semblait correspondre (que je souhaitais faire suivre d'une autre macro qui recherche et élimine les éventuels doublons - mais je n'en suis pas encore là!). Mon problème est "erreur d'éxecution 9 : l'indice n'appartient pas à la sélection"

Voici mon code (je l'ai trouvé sur Internet puis adapté... J'en comprends les grandes lignes, mais je dois avouer que je ne comprends pas tout!) :

Sub macro ()
Dim FichD As String
Dim FichS As Long

FichD = "synthèse.xls"

Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.RefreshScopes
.LookIn = "D:\documents and settings\Mes documents\Test"
.Filename = ".xls"
.SearchSubFolders = False
.Execute
For Ctr = 1 To .FoundFiles.Count
FichS = Ctr
Workbooks.Open (.FoundFiles(FichS))
Sheets("Janvier").Range("F7:"&Range("CG37").End(xlUp).Adress).Copy
ActiveWorkbook.Close
Workbooks(FichD).Sheets("Feuil1).Range("C6").End(xlUp).Offset(1,0).Activate
ActiveSheet.Paste
Next
End with
Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

J'espère que vous pourrez m'aider à y voir plus clair!
Merci d'avance!
Natacha

 

[JNP]

Re : Macro, erreur d'éxecution 9

Bonjour Nata et bienvenue ,
Attention déjà à une chose, FileSearch a été supprimé à partir d'Excel 2007 ...
Ensuite, tu ne peux pas fermer le classeur avant d'avoir collé ...
Tous les classeurs ont-il une feuille janvier ?
Difficile de t'en dire plus, mais qu'elle est la ligne surlignée en jaune quand tu demandes le débogage ?
Bon courage

 

[Nata]

Re : Macro, erreur d'éxecution 9

Bonjour JNP,
Tout d'abord, merci pour ta réponse si rapide!
Pour te répondre :
- J'utilise excel 2003, est-ce que du coup je peux encore utiliser FileSearch? Ou bien il vaut mieux que j'utilise autre chose?
- En fait je colle sur un autre classeur... Je dois quand même laisser le classeur source ouvert?
- Oui, tous les classeurs sources auront une feuille "janvier" (mais pour l'instant je n'ai qu'un seul classeur source - je comptais créer les autres après avoir trouvé la macro)
- Je ne sais pas trop comment faire pour "demander un débogage", mais en tout cas à chaque fois que je rejoue la macro (avec la feuille de code), aucune ligne ne devient jaune...

Merci encore pour ton aide, j'espère que mes réponses sont assez précises!

 

[JNP]

Re : Macro, erreur d'éxecution 9

Re ,

- J'utilise excel 2003, est-ce que du coup je peux encore utiliser FileSearch? Ou bien il vaut mieux que j'utilise autre chose?
Si tu es sûre de ne pas passer en 2007 ou 2010 avant de ne plus avoir besoin de ta macro, pas de problème, mais je préfèrais te prévenir ... Personnellement, je préfère passer par les ScriptingObject ...
- En fait je colle sur un autre classeur... Je dois quand même laisser le classeur source ouvert?
Excel ne sait coller une plage de cellule qu'en ayant la plage sélectionnée au même moment. Donc si tu ferme le classeur, il n'y a rien à coller... Et chez moi, juste un coller sans sélection en cours, ça déclenche une erreur 1004.
- Oui, tous les classeurs sources auront une feuille "janvier" (mais pour l'instant je n'ai qu'un seul classeur source - je comptais créer les autres après avoir trouvé la macro)
Une feuille absente déclenche une erreur 9, d'où ma question ...
- Je ne sais pas trop comment faire pour "demander un débogage", mais en tout cas à chaque fois que je rejoue la macro (avec la feuille de code), aucune ligne ne devient jaune...
Dans ce cas là, essaie d'exécuter ta macro pas à pas avec F8, comme ça, tu verras tes lignes exécutées en jaune, et quand ça créera l'erreur, tu sauras sur quelle ligne tu étais .

A noter aussi que dans la ligne

Workbooks(FichD).Sheets("Feuil1).Range("C6").End(x lUp).Offset(1,0).Activate

il manque la fermeture des guillemets après Feuil1. De plus, j'écrirais plutôt

Workbooks(FichD).Activate
Sheets("Feuil1).Range("C6").End(x lUp).Offset(1,0).Select

Bises et bon courage

 

[Nata]

Re : Macro, erreur d'éxecution 9

Rebonjour JNP,
J'ai fait les changements dont tu m'as parlé, et j'ai aussi fait le débogage pas à pas. Voici le code auquel j'ai abouti en suivant tes conseils (et en espérant ne pas les avoir mal interprétés!). En jaune, c'est à cet endroit, entre ces 2 lignes, que le problème se situe.
Merci encore pour ton aide!
PS : je n'ai pas changé le Filesearch car à vrai dire je ne sais pas me servir de l'autre et je préfère d'abord réussir cette macro, je verrai par la suite si je dois faire cette modif


Sub macro ()
Dim FichD As String
Dim FichS As Long

FichD = "synthèse.xls"

Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.RefreshScopes
.LookIn = "D:\documents and settings\Mes documents\Test"
.Filename = ".xls"
.SearchSubFolders = False
.Execute
For Ctr = 1 To .FoundFiles.Count
FichS = Ctr
Workbooks.Open (.FoundFiles(FichS))
Sheets("Janvier").Range("F7:" & Range("CG37").End(xlUp).Address).Copy
Workbooks(FichD).Activate
Sheets("Feuil1").Range("C6").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Next
End with
Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[JNP]

Re : Macro, erreur d'éxecution 9

Re ,
Essaie de remplacer

Sheets("Janvier").Range("F7:" & Range("CG37").End(xlUp).Address).Copy
Workbooks(FichD).Activate
Sheets("Feuil1").Range("C6").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

par

Sheets("Janvier").Range("F7:" & Range("CG37").End(xlUp).Address).Copy Workbooks(FichD).Sheets("Feuil1").Range("C6").End(xlUp).Offset(1, 0)

A +

 

[Nata]

Re : Macro, erreur d'éxecution 9

Bonjour JNP,

Merci de nouveau pour ton aide, malheureusement cela ne marche toujours pas - c'est vraiment dur VBA quand on ne s'y connaît pas

Si je peux encore un peu abuser de ton aide, est-ce que je peux te demander si tu connaîtrais une macro autre que celle que j'essaie desesperemment de faire marcher, qui me permettrait de résoudre mon problème?

Voici ce dont j'aurais idéalement besoin, sachant que :
1) Je récupère manuellement les fichiers sources que je mets dans un répertoire (ces fichiers contiennent une cellule indiquant, pour chaque ligne, la région concernée)
2) Le fichier destination contient une feuille par région, dont les cellules reprennent les intitulés des fichiers sources.

Ce que doit faire la macro :
1) Copier les lignes du fichier source
2) Les coller (en ordre chronologique) dans le fichier destination sur la feuille correspondant à la région concernée OU tout coller sur une feuille puis dispacher en fonction des régions
3) Supprimer les éventuels doublons

Par la suite, des fonctions Excel "simples" prennent le relai pour remplir des tableaux de synthèse et actualiser des graphiques - mais ça je sais faire

Merci encore pour ton aide!

 

[JNP]

Re : Macro, erreur d'éxecution 9

Re ,
A tester

Sub test()
Dim MonSystème, MonDossier, MonFichier
Dim MonClasseur As Workbook, MaFeuille As Worksheet, Plage As Range
Set MonSystème = CreateObject("Scripting.FileSystemObject")
Set MonDossier = MonSystème.Getfolder("C:\Temp")
For Each MonFichier In MonDossier.Files
    If Right(MonFichier.Name, 4) = ".xls" Then
        Workbooks.Open MonFichier.Path
        Set MonClasseur = ActiveWorkbook
        For Each MaFeuille In MonClasseur.Worksheets
            If MaFeuille.Name = "Janvier" Then
                With MaFeuille
                    Set Plage = .Cells.SpecialCells(xlCellTypeConstants)
                    Set Plage = Intersect(.Range("F7:CG37"), .Range(.Range("F7"), Plage.SpecialCells(xlCellTypeLastCell)))
                    Plage.Copy ThisWorkbook.Sheets("Feuil1").Range("C35000").End(xlUp)
                End With
            End If
        Next MaFeuille
        MonClasseur.Close savechanges:=False
    End If
Next MonFichier
End Sub

J'ai volontairement compliqué ... pour que ce soit plus simple ...
En principe, dans ce code, on sait toujours où on en est ...
N'oublie pas de changer le chemin de ton dossier .
Tiens moi au courant

 

[Nata]

Re : Macro, erreur d'éxecution 9

RE ,
Comme tu me l'avais précisé, j'ai changé le chemin du dossier dans la ligne suivante :
Set MonDossier = MonSystème.Getfolder("C:\Temp") en copiant-collant l'adresse de mon répertoire de fichiers sources à la place de "C:\Temp".

Et tout marche bien jusqu'à la ligne en couleur ci-dessous :

Sub test()
Dim MonSystème, MonDossier, MonFichier
Dim MonClasseur As Workbook, MaFeuille As Worksheet, Plage As Range
Set MonSystème = CreateObject("Scripting.FileSystemObject")
Set MonDossier = MonSystème.Getfolder("C:\Temp")
For Each MonFichier In MonDossier.Files
If Right(MonFichier.Name, 4) = ".xls" Then
Workbooks.Open MonFichier.Path
Set MonClasseur = ActiveWorkbook
For Each MaFeuille In MonClasseur.Worksheets
If MaFeuille.Name = "Janvier" Then
With MaFeuille
Set Plage = .Cells.SpecialCells(xlCellTypeConstants)
Set Plage = Intersect(.Range("F7:CG37"), .Range(.Range("F7"), Plage.SpecialCells(xlCellTypeLastCell)))
Plage.Copy ThisWorkbook.Sheets("Feuil1").Range("C35000").End(xlUp)
End With
End If
Next MaFeuille
MonClasseur.Close savechanges:=False
End If
Next MonFichier
End Sub

La ligne en rouge ici est surlignée en jaune quand j'utilise le débogage, puis un message d'erreur apparaît : "Erreur d'éxecution 1004 : erreur définie par l'application ou l'objet"

Peut-être qu'il y avait d'autres modifications à faire par rapport à ce que tu m'as donné?

Merci encore pour tout!

 

[JNP]

Re : Macro, erreur d'éxecution 9

Re ,
Tu as ouvert ta discussion en "Toutes versions", mais en quelle version es-tu (SpecialCells n'existe peut-être pas dans ta version...) ?
A moins que ce ne soit les constantes ...
Tapes "SpecialCells" dans l'aide VBA pour savoir si ça existe bien et si tu as bien les 2 constantes proposées ...
Sinon, corrige ceci

                With MaFeuille
                    Set Plage = Intersect(.Range("F7:CG37"), .UsedRange)
                    Plage.Copy ThisWorkbook.Sheets("Feuil1").Range("C35000").End(xlUp)
                End With

A +

 

[Nata]

Re : Macro, erreur d'éxecution 9

Re-bonjour JNP,

Ca ne marche toujours pas :/
J'ai regardé ce que tu me demandais, je travaille avec Excel 2003, et il reconnaît le SpecialCells

J'ai fait le changement, mais ça bloque sur la ligne ci-dessous :

Plage.Copy ThisWorkbook.Sheets("Feuil1").Range("C35000").End(xlUp)

Et ça me met "Erreur d'éxecution 9 : l'indice n'appartient pas à la sélection"...

Aurais-tu encore des idées pour que ça marche?

Merci encore!

PS : je n'arrive pas à comprendre à quel moment tu colles ce que tu as copié - tu n'as pas besoin de mettre "Paste" pour y arriver?

 

[Nata]

Re : Macro, erreur d'éxecution 9

Au temps pour moi, j'avais changé entre temps le nom d'une feuille et oublié d'apporter la correction sur la macro, et maintenant ça semble fonctionner... Je regarde tout ça de plus près et te tiens au courant

 

[Nata]

Re : Macro, erreur d'éxecution 9

Ca marche!!!
Mille mercis, vraiment!
J'ai même rajouté (toute seule!) une autre macro pour supprimer les lignes vides
Juste une petite question malgré tout : sais-tu comment faire pour bloquer le contenu de quelques lignes (et non de la feuille entière)? Parce que la macro, malgré les plages que je lui indique, colle les lignes un peu trop haut dans ma feuille, ce qui "recouvre" les libellés que j'ai donné aux colonnes. J'ai essayé de verrouiller les lignes, mais ça m'impose de mettre un mot de passe qui bloque ensuite toute la feuille et empêche la macro de faire son travail.

En tout cas, vraiment, merci encore pour ton aide!

 

[JNP]

Re : Macro, erreur d'éxecution 9

Re ,
Euh, ce qui décide de la hauteur de la copie, c'est

ThisWorkbook.Sheets("Feuil1").Range("C35000").End

que d'ailleurs, j'ai fait une erreur, vu que le End détecte la dernière ligne et qu'il faut décendre d'une, donc corrige

ThisWorkbook.Sheets("Feuil1").Range("C35000").End.Offset(1,0)

et il te suffit de mettre des valeurs dans la colonne C jusqu'à la ligne à partir de laquelle tu veux commencer .
Bon courage

 

[Nata]

Re : Macro, erreur d'éxecution 9

Re-bonjour JNP,

J'ai continué à travailler sur la macro avec tes conseils, et... tout fonctionne!! J'ai même rajouté plusieurs autres macros (courtes et simples), et après quelques soucis, tout semble fonctionner!

Tous mes remerciements donc!

Bonne continuation et merci encore!