PDA

View Full Version : Paste with offset from a different sheet VBA



Jezreel
02-19-2016, 08:18 AM
Hi everyone, what offset modification do I need to do to the code so it pastes the data starting from column D?

Sub copypaste()
Dim Source As Worksheet, Destination As Worksheet, lr As Long, rng As Range
Set Source = Sheets("Query")
Set Destination = Sheets("1. Detail")
lr = Source.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Source.Range("A4:A" & lr).EntireRow
rng.EntireRow.Copy
Destination.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
End Sub

Thanks.

p45cal
02-20-2016, 03:46 AM
one of:
Destination.Cells(Rows.Count, 1).End(xlUp)(2,4).paste…
Destination.Cells(Rows.Count, 4).End(xlUp)(2).paste…
depending on whether you want the destination row to be determined from column A or D respectively.

snb
02-20-2016, 05:45 AM
Sub M_snb()
with Sheets("Query").cells(1).currentregion.columns(1).offset(3)
Sheets("1. Detail").Cells(Rows.Count, 1).End(xlUp).offset(2).resize(.rows.count)=.value
end with
End Sub

SamT
02-20-2016, 06:19 AM
@ p45cal

Is this a new feature, or an undocumented trick

Dim d3 As Range
Set d3 = Range("A1")(2, 4)
It is not OOPs style notation

p45cal
02-20-2016, 06:42 AM
Is this a new feature, or an undocumented trick[CODE]Neither. The default property of the range object is .Item and it "Returns a Range object that represents a range at an offset to the specified range." So you don't need to explicitly use .Item.
See https://msdn.microsoft.com/en-us/library/office/ff196273.aspx which tells you .item is the default property
and https://msdn.microsoft.com/en-us/library/office/ff197454.aspx showing how you can use it.

SamT
02-20-2016, 06:49 AM
Thanks.