copier sur la prochaine ligne vide

[mboileau]

Bonjour,

Voici mon code présentement:


Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("A33:Q49")) Is Nothing Then Exit Sub
If Range("B" & Target.Row) = "" Then Exit Sub
Target.Copy
Application.EnableEvents = False


taddress = Target.Offset(-29, 0).Address


With Sheets("Rapport des transactions")
.Range(taddress).PasteSpecial xlPasteAll
End With


Application.EnableEvents = True


End Sub


Au lieu de coller mes données au tadress offset(-29,0), j'aimerais que

les données se copie sur la prochaine ligne vide. Donc la première
ligne va se offset de -29 mais après les autres vont se mettre
directemetn sur la prochaine ligne vide.


Que dois-je faire?


Merci
MP


Reply »

 

[Peter T]

If I understand correctly you want to refer to the first empty cell below
Target.Offset(-29, 0)

taddress = Target.Offset(-29, 0).End(xlDown).Offset(1, 0).Address

In passing I assume Sheets("Rapport des transactions") refers to the
worksheet module with your code. If so you do not need to qualify the Range
with the sheet, or for readability use "Me", eg

Me.Range(taddress).PasteSpecial xlPasteAll

This avoids hard-coding the sheet name and the risk if it is re-named.

Ignore the above if you are pasting to a relative position on another sheet.

Regards,
Peter T


Bonjour,

Voici mon code présentement:


Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("A33:Q49")) Is Nothing Then Exit Sub
If Range("B" & Target.Row) = "" Then Exit Sub
Target.Copy
Application.EnableEvents = False


taddress = Target.Offset(-29, 0).Address


With Sheets("Rapport des transactions")
.Range(taddress).PasteSpecial xlPasteAll
End With


Application.EnableEvents = True


End Sub


Au lieu de coller mes données au tadress offset(-29,0), j'aimerais que

les données se copie sur la prochaine ligne vide. Donc la première
ligne va se offset de -29 mais après les autres vont se mettre
directemetn sur la prochaine ligne vide.


Que dois-je faire?


Merci
MP


Reply »

 

[mboileau]

ok i change a few thing

Here is my macro now:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A33:O49")) Is Nothing Then Exit Sub
If Range("O" & Target.Row) = "" Then Exit Sub
Target.EntireRow.Copy
Application.EnableEvents = False

taddress = (1, Target.Column).End(xlDown).Address

With Sheets("Rapport des transactions")
.Range(taddress).PasteSpecial xlPasteAll
End With

Application.EnableEvents = True

End Sub

I'm trying to copy my info in the first empty row of sheet "Rapport des
Transactions" in the same column as the target. What should i change
because it doesn't work?

Thank you
MP

 

[Peter T]

I don't follow, try and explain with examples including addresses for
selection, range to be copied and the basis for determining the destination.
Is "Rapport des transactions" same sheet as the Target or another sheet.

What happens if Target is more than one cell.

this will never work -
taddress = (1, Target.Column).End(xlDown).Address

Did you try the example I posted, maybe you can adapt.

Regards,
Peter T