Pbm Boucle For Each sur plusieurs onglet

[WaxistSelecta]

Bonjour le fil,

je travaille sur une boucle 'For Each' qui ne fonctionne pas exactement comme je le souhaite... J'ai fais des recherches sur le fil et sur google pour voir si je trouvais une solution mais sans succès...

Je l'ai écrite tout seul et n'étant pas très calé en VB, il se peut qu'elle soit bancale (c'est fort probable).

Voila ce qu'elle est censé faire:
J'ai un classeur avec environ 29 + 1 onglets
Je souhaite, pour chaque onglet présent dans le classeur, réaliser un copier coller des données vers un autre doc (qui est un template) et renommer ce doc avec la valeur d'une cellule. Puis passer à l'onglet suivant.


Je joins mon code si qqun peut me dire ce qui cloche:

Sub CreaReportLoop()

'Dim Sh As Worksheet
'Dim Wkb As Workbook

'Dim chemin As String, fichier As String
'chemin = ThisWorkbook.Path
'fichier = chemin & "\" & Range("B7") & ".xls"

Application.ScreenUpdating = False


For Each Worksheet In ThisWorkbook.Worksheets
'Boucle sur chaque feuille de chaque classeur
Workbooks("Copy of Consolidation par semaine_Test_V2.xls").Worksheets
Range("A2:L36").Select
Selection.Copy
Workbooks.Open ("C:\Templates\Template Report Région.xls")
ActiveWorkbook.Sheets("Votre Région a Date").Range("A7").Activate
'Selection.Paste
Selection.PasteSpecial xlPasteValues
ActiveWorkbook.SaveAs Filename:=Range("B7")
ActiveWorkbook.Close

Next Worksheet
End Sub

Merci d'avance si qqun peut m'aider!

 

[Pierrot93]

Re : Pbm Boucle For Each sur plusieurs onglet

Bonsoir,

commence peut être par remplacer ces lignes :

For Each Worksheet In ThisWorkbook.Worksheets

et

Next Worksheet

par :

For Each Sh In ThisWorkbook.Worksheets

et

Next Sh

"Worksheet" étant un objet vba, il n'est pas conseillé de l'utiliser comme une variable...

bonne soirée
@+

 

[Pierrot93]

Re : Pbm Boucle For Each sur plusieurs onglet

Re,

tu pourrais partir comme ceci :

Dim Sh As Worksheet
For Each Worksheet In ThisWorkbook.Worksheets
    Sh.Range("A2:L36").Copy Destination:=Workbooks("nonclasseurdestination.xls").Sheets("Feuilledestination").Range("A1")
Next Sh

A noter le classeur de destination doit être ouvert...

@+

 

[WaxistSelecta]

Re : Pbm Boucle For Each sur plusieurs onglet

Salut et merci pour tes propositions rapides!

Je vais les tester.

Par contre, comme tu as du voir dans mon code initial,

j'ai besoin de:
-sélectionner les données dans l'onglet d'origine
- les copier
- ouvrir un modèle (fichier générique) => fichier de destination
- copier les données dans le fichier génériques
- sauvegarder le fichier de destination en le renommant avec la valeur d'une cellule
- fermer ce fichier et recommencer l'opération avec l'onglet suivant du fichier d'origine

 

[Pierrot93]

Re : Pbm Boucle For Each sur plusieurs onglet

Re,

regarde l'exemple ci-dessous, à adapter, à noter qu'en vba il est rarement utile de sélectionner les objets (classeur, feuillle et cellule) :

Sub test()
Dim Sh As Worksheet, wb As Workbook
Set wb = Workbooks.Open("C:\Templates\Template Report Région.xls")
For Each Worksheet In ThisWorkbook.Worksheets
    Sh.Range("A2:L36").Copy Destination:=wb.Sheets("Feuilledestination").Range("A65536").End(xlUp)(2)
Next Sh
wb.Close True
End Sub

bon j'ai sauté la phase d'entegistrement, essaye d'adapter et reviens si problème...

@+

 

[WaxistSelecta]

Re : Pbm Boucle For Each sur plusieurs onglet

Yes, merci pour les conseils...

Je teste ca mais pour la phase d'enregistrement, je pense que ca devrait aller avec ce que j'ai en ma possession dans le code d'origine!

Merci beaucoup, je reviendrai vers toi éventuellement en cas de besoin

Bonne soirée!

Cdt

 

[Pierrot93]

Re : Pbm Boucle For Each sur plusieurs onglet

Bonjour,

j'ai modifiée un peu la procédure, pour effectuer un "enregistrer sous" :

Option Explicit
Sub test()
Dim Sh As Worksheet, wb As Workbook
For Each Worksheet In ThisWorkbook.Worksheets
    Set wb = Workbooks.Open("C:\Templates\Template Report Région.xls")
    Sh.Range("A2:L36").Copy Destination:=wb.Sheets("Feuilledestination").Range("A65536").End(xlUp)(2)
    With wb
        'le nom du fichier est pris sur la feuille active
        .SaveAs "C:\Templates\" & Range("A1").Value & ".xls"
        .Close
    End With
Next Sh
End Sub

bonne journée
@+

 

[WaxistSelecta]

Re : Pbm Boucle For Each sur plusieurs onglet

Salut Pierrot,

super ton code fonctionne à merveille! Merci encore pour ton assistance sur ce point...

J'aurais néanmoins besoin d'aide sur un point supplémentaire:

Dans l'hypothèse où j'aurais 2 types d'onglet:
- Des onglets nommés par un chiffre Ex : 1, 2, 3, ..., 29
- Des onglets nommés par un chiffre + lettre H Ex : 1H, 2H, 3H...,29H
dans un même workbook

Comment faire pour faire la même manip réalisée par le code mais avec une "subtilité" :
- Coller les onglets 1, 2, 3, ..., 29 dans un 1er onglet ("Conso à Date")
=> C'est ce que fait le code que tu m'as proposé

PUIS
- Coller les onglets 1H,2H,3H ... 29H dans un 2nd onglet ("Conso Historique")

Tout ca dans le même document...

Je ne sais pas si ca te parle, je suis entrain de réflechir à la question mais sans grand succès pour l'instant.

Merci d'avance si tu peux m'aider là dessus.

J.

 

[Pierrot93]

Re : Pbm Boucle For Each sur plusieurs onglet

Bonjour,

regarde peut être du coté de la fonction "isnumeric", l'exemple ci-dessous teste si le nom de la feuille active ast numérique...

If IsNumeric(ActiveSheet.Name) Then MsgBox "ok numérique.."

bon après midi
@+

 

[WaxistSelecta]

Re : Pbm Boucle For Each sur plusieurs onglet

Bonjour Pierrot,

la fonction IsNumeric (ActiveSheet.Name) a l'air de fonctionner à priori mais pour être tout à fait efficace, j'aurai besoin d'exclure 2 onglets de la boucle (les 2 premiers onglets) qui ont chacun un nom:
1. "Conso à date par région"
2. "Conso histo par région"

Je ne vois pas vraiment comment le faire ...

Si tu as un truc qui te viens, je cherche de mon côté mais n'étant pas très fortiche sur Visual Basic, ....

Merci d'avance

 

[Pierrot93]

Re : Pbm Boucle For Each sur plusieurs onglet

Bonjour,

pour exclure 2 noms de feuille, regarde le code ci-dessous, nom des feuilles à adapter :

Sub test()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> "Feuil1" And Sh.Name <> "Feuil2" Then
        'ton code
    End If
Next Sh
End Sub

bonne fin d'après midi
@+

 

[WaxistSelecta]

Re : Pbm Boucle For Each sur plusieurs onglet

Bonjour Pierrot et merci pour ta réactivité,

effectivement ca a l'air d'être évident quand tu me le dis mais je n'ai peut être pas encore les bons réflexe

Donc OK pour le IF sur les noms de sheet.. Cela fonctionne.

Cependant, je suis confronté à un autre problème

En gros, j'ai une macro qui génère deux types d'onglets:
- les onglets appelés 1, 2, 3 ... 29 qui sont tirés de mon onglet "Conso à Date"
- les onglets 1H, 2H, 3H, ... 29H qui sont tirés de mon deuxième onglet "Conso Historique" (d'où le H).

La boucle doit servir a coller chacun de ses onglets dans un fichier template + le sauvegarder en utilisant la valeur d'une cellule.

La subtilité est que je dois coller :
- l'onglet 1 dans le fichier Template, dans un 1er onglet
- l'onglet 1H dans le même fichier Template, dans un 2nd onglet
- sauvegarder en utilisant la valeur de "1er onglet".Range("A1")

==> Helas, ca ne fonctionne pas!


Voici mon code au cas où tu verrais iù se trouve l'erreur:

Sub BoucleTotale()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Génération des onglets "A Date" et "Historique" en utilisant le fichier template '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Application.ScreenUpdating = False

Dim Sh As Worksheet, wb As Workbook

'Boucle passant sur chacun des onglets régions pour créer un report indépendants

For Each Sh In ThisWorkbook.Worksheets

If Sh.Name <> "Conso à date par région" And Sh.Name <> "Conso histo par région" Then
Set wb = Workbooks.Open("C:\Templates\Template Report Région.xls")
If IsNumeric(ActiveSheet.Name) Then
Sh.Range("A2:G36").Copy Destination:=wb.Sheets("Votre Région a date").Range("A65536").End(xlUp)(2)
Sh.Range("H2:H36").Copy Destination:=wb.Sheets("Votre Région a date").Range("J65536").End(xlUp)(2)
Sh.Range("I2:I36").Copy Destination:=wb.Sheets("Votre Région a date").Range("M65536").End(xlUp)(2)
Sh.Range("J2:J36").Copy Destination:=wb.Sheets("Votre Région a date").Range("P65536").End(xlUp)(2)
Sh.Range("K2:K36").Copy Destination:=wb.Sheets("Votre Région a date").Range("S65536").End(xlUp)(2)
Sh.Range("L2:L36").Copy Destination:=wb.Sheets("Votre Région a date").Range("V65536").End(xlUp)(2)
Sh.Range("M2:M36").Copy Destination:=wb.Sheets("Votre Région a date").Range("Y65536").End(xlUp)(2)
Sh.Range("N2:N36").Copy Destination:=wb.Sheets("Votre Région a date").Range("AB65536").End(xlUp)(2)

Else
Sh.Range("A2:G36").Copy Destination:=wb.Sheets("Votre Région Histo").Range("A65536").End(xlUp)(2)
Sh.Range("H2:H36").Copy Destination:=wb.Sheets("Votre Région Histo").Range("J65536").End(xlUp)(2)
Sh.Range("I2:I36").Copy Destination:=wb.Sheets("Votre Région Histo").Range("M65536").End(xlUp)(2)
Sh.Range("J2:J36").Copy Destination:=wb.Sheets("Votre Région Histo").Range("P65536").End(xlUp)(2)
Sh.Range("K2:K36").Copy Destination:=wb.Sheets("Votre Région Histo").Range("S65536").End(xlUp)(2)
Sh.Range("L2:L36").Copy Destination:=wb.Sheets("Votre Région Histo").Range("V65536").End(xlUp)(2)
Sh.Range("M2:M36").Copy Destination:=wb.Sheets("Votre Région Histo").Range("Y65536").End(xlUp)(2)
Sh.Range("N2:N36").Copy Destination:=wb.Sheets("Votre Région Histo").Range("AB65536").End(xlUp)(2)

End If
End If


Next Sh

MsgBox "Les Fichiers de Reporting Hebdomadaires ont été générés."

End Sub

 

[Pierrot93]

Re : Pbm Boucle For Each sur plusieurs onglet

Re,

cela veut dire quoi, ca ne fonctionne pas ??? message d'erreur ou ce n'est pas le résultat attendu.... A noter qu'il manque les lignes conceernant la sauvegarde du fichier :

With wb
        'le nom du fichier est pris sur la feuille active
        .SaveAs "C:\Templates\" & Range("A1").Value & ".xls"
        .Close
    End With

A placer avent le dernier "end if"....

 

[WaxistSelecta]

Re : Pbm Boucle For Each sur plusieurs onglet

Pierrot,

effectivement il manque les lignes, elles sont bien dans mon code, c'est une erreur de sélection au copier/coller de ma part...

En fait, j'ai un message d'erreur au niveau de la ligne de sauvegarde...

Visiblement, ca vient du fait que le premier onglet rencontré par la boucle n'est pas numérique ("1H"), donc le code va coller les données dans le template, wb.Sheets("Votre Région Histo").
Alors que la ligne de sauvegarde doit pointer sur la première feuille "Votre région à Date"...

 

[Pierrot93]

Re : Pbm Boucle For Each sur plusieurs onglet

Re,

et que dit ce message d'erreur ?