Boucle For Each Next sur plusieurs feuilles

**NadStin** · 12/04/2017, 09h20

Bonjour,

Je souhaite faire une boucle pour coller un sélection sur plusieurs feuilles :

Feuilles :
TEST
SEM 1
SEM 2
SEM 3
SEM 4
SEM 5
RECAP

A savoir que les feuilles SEM 1, SEM 2 ... peuvent aussi s'appeler selon le mois :
SEM 5
SEM 6
SEM 7
SEM 8
SEM 9

Donc je copie une sélection de la SEM 1 qui est la feuille active et je souhaite la copier dans les autres semaines (soit tous les onglet sauf ONGLET TEST ET ONGLET RECAP)

Voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 ActiveCell.Copy
 
For Each ws In Worksheets
 
If ws.Name > "SEM 1" Or ws.Name < "SEM 52" Then
ws.Range("a999").End(xlUp).Offset(5, 0).PasteSpecial xlPasteAll
 
End If
 
Next ws

**riaolle** · 12/04/2017, 09h35

Bonjour,
ws.Name renvoie le nom de la feuille, donc une chaine de caractères.
Tu ne peux pas comparer des chaines de caractères avec < ou >. Que veut dire chaine1 > chaine2?

tous les onglet sauf ONGLET TEST ET ONGLET RECAP

Alors utilise cette condition :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
If ws.Name <> "TEST" And ws.Name <> "RECAP" Then
 ...
End If

**unparia** · 12/04/2017, 09h55

Bonjour riaolle

Bien sur qu oui : on peut comparer deux chaînes par < ou >.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

msgbox "chaine 2" > "chaine 1"

retournera par exemple true (ou vrai)
il serait alors par contre prudent de les nommer "chaine 01" et ""chaine 02" (pour éviter d'arriver à "chaine 12" qui serait < "chaine 2"

Ceci étant dit : la solution que tu proposes est plus adroite dans le cas présenté.

Amitiés

**halaster08** · 12/04/2017, 09h55

Bonjour,

Envoyé par riaolle

Tu ne peux pas comparer des chaines de caractères avec < ou >. Que veut dire chaine1 > chaine2?

Si on peut, il compare alors caractère par caractère la valeur du code ASCII

edit: trop lent ...

**NadStin** · 12/04/2017, 10h14

J'ai donc modifié : (l'onglet test s'appelle maintenant HORAIRE DE BASE)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 ActiveCell.Copy
 
For Each ws In Worksheets
 
If ws.Name <> "HORAIRE DE BASE" Or ws.Name <> "RECAP" Then
 
ws.Range("a999").End(xlUp).Offset(5, 0).PasteSpecial xlPasteAll
 
End If
 
Next ws

Mais la boucle ne se fait pas

**halaster08** · 12/04/2017, 10h25

Envoyé par NadStin

Mais la boucle ne se fait pas

Essaye le code ci dessous.
Puis va voir le résultat du debug.print (ctrl+G) pour voir si la boucle se fait bien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
dim ws as worksheet
 ActiveCell.Copy
 
For Each ws In thisworkbook.Worksheets
debug.print ws.name
If ws.Name <> "HORAIRE DE BASE" Or ws.Name <> "RECAP" Then
 
ws.Range("a999").End(xlUp).Offset(5, 0).PasteSpecial xlPasteAll
 
End If
 
Next ws

**riaolle** · 12/04/2017, 10h25

OK, désolée pour mon erreur... j'ai appris quelque chose

Attention, tu mets Or, il faut mettre And, car pour l'onglet "RECAP", par exemple, le nom est bien différent de HORAIRE DE BASE, donc la contidion est vérifiée.

Mais la boucle ne se fait pas

Pourrais-tu être un peu plus précis? Le code tourne, mais rien ne se passe?
Peux-tu nous donner ce qu'il y a autour du bout de code que tu nous donnes pour voir s'il y a pas une erreur en amont.

**NadStin** · 12/04/2017, 10h38

ok, j'ai modifié "or" en "and" mais toujours rien,
le reste de la macro tourne et tout se passe comme prévu mais pour ce bout de code, rien ne se passe...

voici le code en entier

et ce que cela doit faire :
lors d'un double clic, il a a un tableau qui se colle dans la feuille active et ce dernier est complété avec les éléments des input box, ensuite (normalement) ce même tableau doit se coller sur les autres onglet (d'ou la boucle...)

merci...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
 
Dim ws As Worksheet
 
If MsgBox("Souhaitez vous ajouter une personne ?", vbYesNo) = vbYes Then
 
  Range("a999").End(xlUp).Offset(5, 0).Select
 
  Sheets("MODELES").Range("a16:aa25").Copy
 
  ActiveCell.PasteSpecial xlPasteAll
 
  nom = InputBox("Merci d'indiquer le nom et prénom du collaborateur : ", "Non - Prénom")
  ActiveCell.Offset(0, 2).Value = nom
 
  poste = InputBox("Merci d'indiquer le poste du collaborateur : " & Chr(10) & Chr(10) & "RM" & Chr(10) & "1er ADJOINT" & Chr(10) & "2ème ADJOINT" & Chr(10) & "VENDEUR(SE)" & Chr(10) & "CHEF CAISSE" & Chr(10) & "STAGIAIRE" & Chr(10) & "APPRENTI", "poste")
  ActiveCell.Offset(4, 2).Value = poste
 
  ray = InputBox("Merci d'indiquer le rayon du collaborateur : ", "rayon")
  ActiveCell.Offset(5, 2).Value = ray
 
  hor = InputBox("Merci d'indiquer le nombre d'heure hebdomadaire du collaborateur : " & Chr(10) & Chr(10) & "Format : HH:MM", "Nb d'heure")
  ActiveCell.Offset(7, 2).Value = hor
 
  anc = InputBox("Merci d'indiquer la date d'ancienneté du collaborateur : " & Chr(10) & Chr(10) & "Format : JJ/MM/AAAA", "Ancienneté")
  Sheets("ANCIENNETE").Activate
  Range("A1").End(xlDown).Offset(1, 0).Value = nom
  Range("B1").End(xlDown).Offset(1, 0).Value = anc
 
  ActiveCell.Copy
 
For Each ws In Worksheets
 
If ws.Name <> "HORAIRE DE BASE" And ws.Name <> "RECAP" Then
 
ws.Range("a999").End(xlUp).Offset(5, 0).PasteSpecial xlPasteAll
 
End If
 
Next ws
 
 
 If MsgBox("Souhaitez vous ajouter ce collaborateur à horraire de base ?", vbYesNo) = vbYes Then
 
  Sheets("MODELES").Range("a32:i36").Copy
  Sheets("HORAIRE DE BASE").Activate
  Range("a104857").End(xlUp).Select
  ActiveCell.Offset(6, 0).PasteSpecial xlPasteAll
 
  ActiveCell.Value = nom
 
  MsgBox ("Merci de compléter l'horaire de base du collaborateur")
 
 
End If
End If
 
 
 
End Sub

**riaolle** · 12/04/2017, 10h55

Je ne vois pas vraiment d'erreur sur le bout de code, néanmoins quelques commentaires :

Y a-t-il une raison spéciale pour laquelle tu remontes les cellules à partir de A999?
Si non, utilise plutôt :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
```
ws.Cells(Rows.Count, 1).End(xlUp)
```
Comme ça tout se passera bien même si tu dépasses A999.

Evite d'utiliser Select, Activate, ActiveCell etc ... Souvent cela ne fait qu'entraîner des problèmes difficiles à gérer. Précise sur quelles cellules ou feuilles tu travailles. Soit à travers une variable (c'est ce qui me paraît le plus judicieux, ici) ou à l'aide d'un bloc With.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Dim myCel As Range
myCel = Range("a999").End(xlUp).Offset(5, 0)
   'puis dans la suite du code :
myCel.PasteSpecial xlPasteAll
   '...
myCel.Offset(0, 2).Value = nom
   '...

Essaie déjà avec ça. Je ne garantie pas que le code marchera, mais on avance un peu

**halaster08** · 12/04/2017, 11h22

Envoyé par NadStin

ensuite (normalement) ce même tableau doit se coller sur les autres onglet (d'ou la boucle...)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13

ActiveCell.Copy
  
For Each ws In Worksheets

If ws.Name <> "HORAIRE DE BASE" And ws.Name <> "RECAP" Then

ws.Range("a999").End(xlUp).Offset(5, 0).PasteSpecial xlPasteAll

End If

Next ws

Tu veux copier un tableau ou une cellule?

**NadStin** · 12/04/2017, 11h31

merci pour ses infos, mais cela ne fonctionne pas...

voici le msg d'erreur :

Nom : Capture.PNG
Affichages : 1488
Taille : 22,0 Ko

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
 
Dim myCel As Range
 
Dim ws As Worksheet
 
If MsgBox("Souhaitez vous ajouter une personne ?", vbYesNo) = vbYes Then
 
  Cells(Rows.Count, 1).End(xlUp).Offset(5, 0).Select
 
  Sheets("MODELES").Range("a16:aa25").Copy
 
  myCel.PasteSpecial xlPasteAll
 
  nom = InputBox("Merci d'indiquer le nom et prénom du collaborateur : ", "Non - Prénom")
  myCel.Offset(0, 2).Value = nom
 
  poste = InputBox("Merci d'indiquer le poste du collaborateur : " & Chr(10) & Chr(10) & "RM" & Chr(10) & "1er ADJOINT" & Chr(10) & "2ème ADJOINT" & Chr(10) & "VENDEUR(SE)" & Chr(10) & "CHEF CAISSE" & Chr(10) & "STAGIAIRE" & Chr(10) & "APPRENTI", "poste")
  myCel.Offset(4, 2).Value = poste
 
  ray = InputBox("Merci d'indiquer le rayon du collaborateur : ", "rayon")
  myCel.Offset(5, 2).Value = ray
 
  hor = InputBox("Merci d'indiquer le nombre d'heure hebdomadaire du collaborateur : " & Chr(10) & Chr(10) & "Format : HH:MM", "Nb d'heure")
  myCel.Offset(7, 2).Value = hor
 
  anc = InputBox("Merci d'indiquer la date d'ancienneté du collaborateur : " & Chr(10) & Chr(10) & "Format : JJ/MM/AAAA", "Ancienneté")
  Sheets("ANCIENNETE").Activate
  Range("A1").End(xlDown).Offset(1, 0).Value = nom
  Range("B1").End(xlDown).Offset(1, 0).Value = anc
 
  myCel.Copy
 
For Each ws In Worksheets
 
If ws.Name <> "HORAIRE DE BASE" And ws.Name <> "RECAP" Then
 
ws.Cells(Rows.Count, 1).End(xlUp).Offset(5, 0).PasteSpecial xlPasteAll
 
End If
 
Next ws
 
 
 If MsgBox("Souhaitez vous ajouter ce collaborateur à horraire de base ?", vbYesNo) = vbYes Then
 
  Sheets("MODELES").Range("a32:i36").Copy
  Sheets("HORAIRE DE BASE").Activate
  Range("a104857").End(xlUp).Select
  myCel.Offset(6, 0).PasteSpecial xlPasteAll
 
  myCel.Value = nom
 
  MsgBox ("Merci de compléter l'horaire de base du collaborateur")
 
 
End If
End If
 
 
 
End Sub

Envoyé par halaster08

Tu veux copier un tableau ou une cellule?

Un tableau, donc effectivement ca va pas ...

**riaolle** · 12/04/2017, 11h53

Attention, tu ne définis pas ce que vaut la variable myCel avant de l'utiliser:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Dim myCel As Range 
 myCel = Cells(Rows.Count, 1).End(xlUp).Offset(5, 0)

Puis tu peux l'utiliser. Pas besoin du Select.

Si tu veux copier un tableau, c'est une plage de cellules qu'il faudra copier. Comment est définie ton tableau? Est-ce qu'il a un certain nombre de lignes, de colonnes et un repère d'une cellule? Est-ce qu'il a une position précise dans la feuille?

**RyuAutodidacte** · 12/04/2017, 12h11

Bonjour,

Ton tableau est un vrai tableau Excel fait à partir de l'onglet "Tableaux" ??

Si c'est le cas :

- y a-t-il qu'un seul tableau sur la feuille ?

- que veux tu copier ?
• tout le tableau avec les en-têtes ??
• ou seulement les données du tableaux ??

**Marc-L** · 12/04/2017, 19h07

Bonjour !

Envoyé par NadStin

Je souhaite faire une boucle pour coller un sélection sur plusieurs feuilles

Alors voir déjà du côté de la méthode FillAcrossSheets et ce, sans boucle !

___________________________________________________________________________________________________________
Je suis Paris, Egypte, Stockholm, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

**halaster08** · 13/04/2017, 08h35

Envoyé par Marc-L

Alors voir déjà du côté de la méthode FillAcrossSheets et ce, sans boucle !

+1, je ne connaissait pas, Merci beaucoup.

**RyuAutodidacte** · 13/04/2017, 12h22

Slt Marc

,

, je ne me rappelle avoir été dans ce besoin "FillAcrossSheets", mais il est bon de le remonter à la surface, car je ne connaissais pas non plus.
Ça m'évitera de passer à coté la prochaine fois dans le cas où j'en aurai besoin …

**NadStin** · 14/04/2017, 10h07

Bonjour,

Ok, donc je définie ma variable et j'essaie avec FillAcrossSheets

Et pour te répondre Ryu, c'est le tableau avec les entêtes que je veux veux copier pas juste les données.

Bon...

Il y a quoi qui cloche dans :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Dim myCel As Range
myCel = Cells(Row.Count, 1).End(xlUp).Offset(5, 0).Resize(10, 28)

**halaster08** · 14/04/2017, 10h23

Il manque un S, a rowS.count
et un Set en début de ligne.

**Philippe Tulliez** · 14/04/2017, 10h38

Bonjour,
Une autre solution si tu souhaites utiliser à la fois les étiquettes de colonnes (titres) et les données, c'est l'utilisation de la propriété CurrentRegion
Pour l'exemple, la table des données commence en cellule A1 de la feuille nommée Feuil1 du classeur où se trouve le code VBA.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Feuil1").Range("A1").CurrentRegion

**NadStin** · 14/04/2017, 11h11

Envoyé par Philippe Tulliez

Bonjour,
Une autre solution si tu souhaites utiliser à la fois les étiquettes de colonnes (titres) et les données, c'est l'utilisation de la propriété CurrentRegion

cela ne peut pas fonctionner car il y a des colonnes vides ...

merci HALASTER !

Bon ça c'est bon, encore un pb sur la suivante ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Dim ws As Worksheet
 Set ws = Worksheet.Name <> "HORAIRE DE BASE" And Worksheet.Name <> "RECAP"

Boucle For Each Next sur plusieurs feuilles [XL-2016]

Macros et VBA Excel

Discussions similaires

Partager

Partager