VBA: coller sur cellule non vide

Le 02/06/2011 à 14:52

Bonjour,

J'ai une macro qui copie les valeurs d'une colonne, vers la même colonne d'une autre feuille. La macro cherche la première cellule non vide de la colonne de destination pour coller les valeures.

Voici le code:

Sub test()
Sheets("try1").Select
Range("e2:e" & [e65000].End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy Sheets("destination").Range("e2:e999").End(xlDown)

End Sub

Cette macro fonctionne, à l'exception que:

  • Ca me renvoie un message d'erreur (copy and paste area are not the same size) quand la colonne de destination est vide. Une fois qu'il y a des valeurs, cela fonctionne.. ?
  • En fait, il faut qu'il y a une valeur dans la case e3 ou plus bas. Auquel cas ça fonctionne, sauf que ça remplace cette cellule

Ce que je voudrais, c'est que cela colle les valeurs sous la dernière valeur (sans la remplacer!), et que si il n'y a aucune valeure, coller quand même à partir de e2.

J'espère que mon explication est assez claire...

Merci d'avance quand même

Le 02/06/2011 à 15:33

Bonjour

Je reviens à la charge

ce code à l'air de fonctionner

Sub test()
  Sheets("try1").Select
  Range("e2:e" & [e65000].End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
                    Sheets("destination").Range("E999").End(xlUp).Offset(1, 0)
End Sub
Le 02/06/2011 à 17:25

Bonjour,

Petites améliorations :

1. sans le Select

2. range "e65000" au lieu de "e999" au cas où il y ait des valeurs sous e999

Sub test()

Sheets("try1").Range("e2:e" & [e65000].End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
    Sheets("destination").Range("e65000").End(xlUp).Offset(1, 0)

End Sub

Note : Quand on veut trouver la dernière cellule d'une colonne, il est toujours préférable d'utiliser .end(xlUp) car si il n'y a pas de cellule, .End(xlDown) nous donne la dernière ligne de la colonne, soit 65536 sous Excel 2003. Cela cause des problèmes si on veut copier des cellules. Tandis que si la colonne est vide, .End(xlUp) nous renvoie la ligne 1. Aucun problème à copier des cellules.

A+

Le 02/06/2011 à 17:33

Bonjour

Pour rebondir

Grand Chaman Excel as tu essayé ?

Je fais le test mais je n'ai que la première valeur de mon tableau qui est copiée

Sûrement j'ai fait une erreur mais où ?

Ou de ma version

Je joins le fichier du test

133test-copie.zip (9.56 Ko)
Le 02/06/2011 à 18:34

Bonjour Banzai,

Bien vu! Tu as raison, il faut garder le .Select.

Probablement que lorsque j'ai testé, j'étais déjà sur le feuille "Try1"...

En effet avec ton fichier exemple, la première fois que l'on exécute la macro, une seule ligne est copiée. Si on refait le macro une 2e fois, alors tout est copié...

En fait le problème vient du fait qu'on utilise [e65000].End(xlIp).Row pour définir la dernière cellule à copier alors qu'on devrait plutôt faire ceci Sheets("try1").Range("e65000").End(xlUp).Row. Si on prend [e65000], sans spécifier quelle feuille, alors ça prend la feuille active et c'est pour ça qu'il faut garder le .Select avant.

Alors, je propose ceci :

Sub test()

Sheets("try1").Range("e2:e" & Sheets("try1").Range("e65000").End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
    Sheets("destination").Range("e65000").End(xlUp).Offset(1, 0)

End Sub

A+

Le 02/06/2011 à 18:45

Bonjour

Ok cela fonctionne bien

Ce qui m'a perturbé (car je l'utilise rarement) cest la notation [......] pour une zone

Juste pour le plaisir

testé et fonctionnel

Sub test()
  With Sheets("try1")
    .Range("e2:e" & .Range("E65000").End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
      Range("E65000").End(xlUp).Offset(1, 0)
  End With
End Sub
Le 03/06/2011 à 08:09

Hello,

Effectivement, j'ai testé et ça marche imec!!

Merci bien pour votre aide

Le 04/06/2011 à 08:39

Petite précision,

J'ai essayé de modifier le code pour qu'il colle seulement les valeures et non pas la mise en forme. Avec la méthode PasteSpecial j'arrive pas à avoir un code qui fonctionne.

Y'aurais moyen de modifier ceci:

Sub test()

Sheets("try1").Range("e2:e" & Sheets("try1").Range("e65000").End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
    Sheets("destination").Range("e65000").End(xlUp).Offset(1, 0)

End Sub

Pour qu'il n'applique que les valeures en laissant de côté la mise en forme?

Merci

Le 04/06/2011 à 08:47

Bonjour

Essaye :

Sub test()
Sheets("try1").Range("e2:e" & Sheets("try1").Range("e65000").End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("destination").Range("e65000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub

Amicalement

Nad

Le 04/06/2011 à 09:17

Ben mince, j'etais pas loin avec ma tentative, mais ça marchait pas. J'ai du foirer quelque part au niveau de la syntaxe.

Bon j'ai découvert que la macro avait une faille: quand la colonne (ou les cellules) de départ sont vides, cela me renvoi un message d'erreur.

Quelqu'un sait comment résoudre ça?

Merci!

Le 04/06/2011 à 09:39

Re

Sub test()
Dim derlg As Integer
derlg = Sheets("try1").Range("e65000").End(xlUp).Row
If derlg < 2 Then
MsgBox "Rien à copier"
End
End If
Sheets("try1").Range("e2:e" & derlg).SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("destination").Range("e65000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub

Nad

Le 05/06/2011 à 06:49

Merci,

J'ai repris ton idée en l'adaptant un peu à ce que je voulais, et ça donne ça:

Sub Copier()
Dim lastVal As Integer
Dim caisseVal As Integer
caisseVal = 1
Do While caisseVal < 10

lastVal = Sheets("Caisse" & caisseVal).Range("h65000").End(xlUp).Row
If lastVal > 0 Then
Sheets("Caisse" & caisseVal).Range("h2:h" & Sheets("Caisse" & caisseVal).Range("h65000").End(xlUp).Row).Copy
Sheets("DepotCheques").Range("f65000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If

lastVal = Sheets("Caisse" & caisseVal).Range("j65000").End(xlUp).Row
If lastVal > 0 Then
Sheets("Caisse" & caisseVal).Range("=j2:j" & Sheets("Caisse" & caisseVal).Range("j65000").End(xlUp).Row).Copy
Sheets("DepotCheques").Range("h65000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If
...
caisseVal = caisseVal + 1
Loop

End Sub

En fait il y a 9 feuilles dont la macro doit aller chercher les valeures (caisse1 --> caisse9), et sur chaque feuille copier les mêmes colonnes. Du coup ça saute l'opération si il n'y a pas de valeurs (lastVal=0).

Et puis j'ai transformé le

.SpecialCells(xlCellTypeConstants, 23).Copy

en simplement

.Copy

, car pour une raison que je comprends pas ça ne copiait pas les valeurs si il n'y avait que une ou deux valeures dans la colonne.

Bref, tout ça pour dire que je pense que ma macro est finalisé comme ça

Merci bien!

Rechercher des sujets similaires à "vba coller vide"