PDA

View Full Version : [SOLVED] Paste and End(xlRight)



Edmond
12-03-2018, 03:21 PM
Hello Everyone,

I am trying to paste some data, in another sheet, in the last column.

Basically I would like this code:


wsWar.Range(Cells(2, 6), Cells(50, y - 5)).Copy wsWar2.Cells(1, 82).End(xlRight).Offset(0,1)


But of course, this one is not correct...
Some of you would have ideas? :)

Thanks a lot in advance for your time

Paul_Hossler
12-03-2018, 05:06 PM
The destination part says





wsWar2.Cells(1, 82).End(xlRight).Offset(0,1)




From CD1 (1,82)

go to the first non-blank cell or the last cell (XFD1) and then

go one more columns

SO it depends of whether there are any non-blank cells after CD1

Now if you to append another column to a block of data, I usually use something like



Option Explicit
Sub Macro1()
With ActiveSheet
.Range("A1:E10").Value = 123
MsgBox .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Address
.Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1) = 987
End With
End Sub



i.e. from far right, go left to first non-blank cell, and then right one column

Edmond
12-04-2018, 12:49 PM
Excellent! Great idea!

If someone is interested, I paste below the code I used to copy/paste in another worksheet, to the last used cell in the first row:


With wsWar2

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

End With

wsWar.Range(Cells(2, 6), Cells(50, y - 5)).Copy wsWar2.Cells(1, LastCol).Offset(0, 1)


Thanks a lot Paul!