Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copier sur la prochaine ligne vide
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 » |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copier sur la prochaine ligne vide
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 wrote in message oups.com... 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 » |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copier sur la prochaine ligne vide
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copier sur la prochaine ligne vide
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 wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double A copier paper.....0.80USD | Excel Discussion (Misc queries) |