Discussion :

[NadStin]

Bonjour,
Je souhaite faire une boucle afin de coller une plage de cellules d'une feuille sur une autre feuille, en boucle jusqu'au ce que le nombre de cellules calculés précédemment est inférieur au nombre de lignes.

Voici le début de ma macro : il calcule le nombre de ligne :

Code :

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

1
2
 Dim nbLignes As Integer ' nbLignes est une variable stockées sous forme de nombre
  nbLignes = (Cells(Rows.Count, 3).End(xlUp).Row + 1) ' calcul du nombre de lignes (nb de salaries) afin de répéter la boucle

Donc sur ma feuille active, je clique sur un bouton, ce dernier doit copier une plage de cellules de la feuille HORAIRE DE BASE SEM A, et la coller sur une plage de ma feuille active.
Lors de la boucle il doit aller chercher la plage de cellules 9 lignes en dessous et il doit coller sur la feuille active 11 lignes plus bas.

En français cela donne :
Je suis sur la feuille SEM1
Je clique sur le bouton,
Cela calcule le nombre de lignes maxi (ici 142),
copie de la plage B12:H18 de l' HORAIRE DE BASE SEM A et je le colle sur E12:K18 sur SEM1
Boucle : B21:H27 de HORAIRE DE BASE SEM A et je le colle sur E23:K29 sur SEM1
...boucle tant que la plage se situe en dessous de la ligne 142.

alors j'ai essayer de faire quelque chose... mais cela ne fonctionne pas sans grand étonnement

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
 'définition des variables
Dim TOUS As Range
Set TOUS = Cells(12, 5)
 Dim nbLignes As Integer ' nbLignes est une variable stockées sous forme de nombre
 Dim numeroL As Integer  ' numeroL est une variable stockées sous forme de nombre
 Dim numeroC As Integer  ' numeroC est une variable stockées sous forme de nombre
 nbLignes = (Cells(Rows.Count, 3).End(xlUp).Row + 1) ' calcul du nombre de lignes (nb de salaries) afin de répéter la boucle
 numeroL = 12 'numero de ligne = 12
 numeroC = 2 ' numero de colonne = 5
 
 
 Do While numeroL <= nbLignes ' boucle tant que le numéro de ligne est inférieur au nombre de lignes calculées 
Sheets("HORAIRE DE BASE SEM A").Range(Cells(numeroL, numeroC), Cells(numeroL + 5, numeroC + 6)).copie ' supprime le contenu des celules B12:H18
TOUS.PasteSpecial xlPasteAll
numeroL = numeroL + 11 ' boucle à la fin de la ligne +20
TOUS=TOUS+9
Loop ' fin de la boucle

Merci beaucoup à celles et ceux qui prendront le temps de m'aider.
Cordialement,
Nadège

[Philippe Tulliez]

Bonjour,

alors j'ai essayer de faire quelque chose... mais cela ne fonctionne pas sans grand étonnement

Qu'est-ce qui ne fonctionne pas ?
Un message d'erreur, dans l'affirmative lequel et à quelle ligne, ou bien le résultat attendu n'est pas le bon ?

Je n'ai effectué aucun test mais je vois déjà un problème dans la ligne de code

Code :

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

Sheets("HORAIRE DE BASE SEM A").Range(Cells(numeroL, numeroC), Cells(numeroL + 5, numeroC + 6)).copie ' supprime le contenu des cellules B12:H18

, il manque le point devant les objets Cells qui les relient à leur parent la feuille nommée HORAIRE DE BASE SEM A

Donc il y lieu de la remplacer par un bloc With...End...With (Attention c'est Copy et pas Copie et attention, il s'agit d'une copie et pas d'une suppression, comme le laisse sous-entendre le commentaire, sinon il faut choisir la méthode Cut en ajoutant l'argument Destination

Code :

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

1
2
3
    With Sheets("HORAIRE DE BASE SEM A")
    .Range(.Cells(numeroL, numeroC), .Cells(numeroL + 5, numeroC + 6)).Copy ' supprime le contenu des celules B12:H18
    End With

Et idéalement, dans les premières lignes (la 3 et la 7 de votre code), il faudrait préciser la parentalité des objets Range soit pour la ligne 3

Code :

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

Set TOUS = Worksheets("SEM1").Cells(12, 5)

J'ajouterais également l'objet classeur (ThisWorkbook). Plus on précise la parentalité d'un objet plus on évite les problèmes

Voir éventuellement mon billet sur le sujet Excel VBA - La parentalité d’un objet

[Philippe Tulliez]

Bonjour,
Ce que j'ai compris de votre projet
Vous devez copier depuis une feuille "Source", un certain nombres de blocs de cellules constitués de 7 lignes et 7 colonnes à partir de la ligne 12 et ce toutes les 9 lignes et la copie doit s'arrêter à partir du moment où l'on atteint la dernière ligne
Ces blocs doivent être collés dans une feuille "cible" à partir du même numéro de ligne et avec un écart de 3 colonne(B -> E)

Voici un code qui calcule les adresses des blocs de cellules à copier de la source et l'adresse de la cellule "cible" (n'ayant pas compris l'écart des lignes de la cible, j'ai considéré que les blocs copiés sont collés à la suite l'un de l'autre)

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
Sub t()
  ' Déclaration
  Const SourceName As String = "Source" ' Nom de la feuille source
  Const TargetName As String = "Cible"  ' Nom de la feuille cible
  Const StartRow As Long = 12           ' Ligne de début
  Const StartCol As Integer = 2         ' Colonne de début
  Const NumberRows As Integer = 7       ' Nombre de lignes à copier
  Const NumberColumns As Integer = 7    ' Nombre de colonnes à copier
  Const GapColumn = 3                   ' Ecart de colonne de la cible par rapport à la source
  Const StepRow = 9                     ' Nombre de ligne d'écart entre les plages
  Dim shtSource As Worksheet            ' Feuille source
  Dim shtTarget As Worksheet            ' Feuille cible
  Dim rngSource As Range                ' Plage source
  Dim rngTarget As Range                ' Plage cible
  Dim LastRow As Long                   ' Dernière ligne remplie dans la feuille source
  Dim LineNumber As Long                ' Numéro de ligne
  Dim adSrce As String                  ' Adresse source à copier
  Dim adTrgt As String                  ' Adresse cible où coller
  Dim n As Long                         ' Nombre de blocs copié
  Dim t As String                       ' Texte renvoyé à la MsgBox
  ' Définir les feuilles source et cible
  With ThisWorkbook
    Set shtSource = .Worksheets(SourceName)
    Set shtTarget = .Worksheets(TargetName)
  End With
  '
  With shtSource
    LastRow = .Cells(.Rows.Count, StartCol).End(xlUp).Row
  End With
  LineNumber = StartRow ' Ligne où débute la copie
  ' Copie
  t = "Plage à copier "
  Do While LineNumber <= LastRow
    With shtSource
      adSrce = .Range(.Cells(LineNumber, StartCol), .Cells(LineNumber + NumberRows - 1, StartCol + NumberColumns - 1)).Address
      adTrgt = shtTarget.Cells(StartRow + (n * NumberRows), StartCol + GapColumn).Address
      n = n + 1
      t = t & vbCrLf & adSrce
      t = t & " => " & adTrgt
      ' Ici écrire la ligne à copier
      '
   End With
    LineNumber = LineNumber + StepRow
  Loop
  MsgBox t
  ' Fin
  Set shtSource = Nothing: Set shtTarget = Nothing
  Set rngSource = Nothing: Set rngTarget = Nothing
End Sub

Il reste à placer la ligne d'instruction de copie et à supprimer les lignes 38, 39 et 45 ainsi que les lignes où ces variables utilisées sont déclarées

Pour info, il est possible de faire ce travail à l'aide de Power Query en cinq fois moins de temps et avec moins de lignes de requête. Il peut être intéressant de s'y intéresser

Illustration de l'affichage du MsgBox (ligne 45 du code publié)



Illustration de la plage de données de la feuille "Source" utilisées pour mon test

[NadStin]

Bonjour,

Je réponds dans un premier temps au premier message, j'ai fais les modifications que vous m'avez indiqué et cela fonctionne, le copier fonctionne bien et la cellule a copier change bien a chaque boucle, donc pas de pb. Cependant, la zone pour le collage reste identique à chaque boucle. Je vous joins le code :

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
 Dim nbLignes As Integer ' nbLignes est une variable stockées sous forme de nombre
 Dim numeroL As Integer  ' numeroL est une variable stockées sous forme de nombre
 Dim numeroC As Integer  ' numeroC est une variable stockées sous forme de nombre
 nbLignes = (Cells(Rows.Count, 3).End(xlUp).Row + 1) ' calcul du nombre de lignes (nb de salaries) afin de répéter la boucle
 numeroL = 12 'numero de ligne = 12
 numeroC = 2 ' numero de colonne = 5
 
 
'boucle permettant de supprimer le contenu des cellules TR +/- et RC
 Do While numeroL <= nbLignes ' boucle tant que le numéro de ligne est inférieur au nombre de lignes calculées et que le numéro de cellule est inférieur à 11 (jusque'au dimanche)
With Sheets("HORAIRE DE BASE SEM A")
.Range(.Cells(numeroL, numeroC), .Cells(numeroL + 6, numeroC + 6)).Copy
End With
TOUS.PasteSpecial xlPasteAll
numeroL = numeroL + 9 ' boucle à la fin de la ligne +20
Loop ' fin de la boucle

[Philippe Tulliez]

Bonjour,
En ligne 14 de votre dernier code publié, vous utilisez la variable TOUS mais je ne vois pas où l'on affecte l'objet
D'après moi, sans avoir testé, c'est à cette ligne là que se situe le problème. Comme vous coller tout, préférez la méthode Copy avec l'argument Destination, c'est plus simple à gérer.

[EDIT]
Voici deux exemples simples avec les deux syntaxes avec le même résultat (l'un avec Copy et Paste l'autre en utilisant Copy et son argument Destination

Code :

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

1
2
3
4
5
6
7
Sub Exemple_1()
  With ThisWorkbook
  sht_Target.Cells.Clear
  .Worksheets("Base 0").Range("B5:D15").Copy
  .Worksheets("Cible").Range("D4").PasteSpecial xlPasteAll
  End With
End Sub

Code :

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

1
2
3
4
5
6
Sub Exemple_2()
  sht_Target.Cells.Clear
  With ThisWorkbook
  .Worksheets("Base 0").Range("B5:D15").Copy Destination:=.Worksheets("Cible").Range("D4")
  End With
End Sub

[NadStin]

Pour ce qui est du 2ème message, je ne doute pas de son efficacité mais il est très complexe et j'aimerai, si cela est possible, rester sur ma base de code que je comprend.

Merci beaucoup.

[NadStin]

Le copier sera toujours fait depuis la feuille "HORAIRE DE BASE SEM A", cependant le coller se fera sur la feuille active au moment du déclenchement de la macro.