Copy and paste dynamic range VBA

Question

I am new to VBA and am stuck somewhere. I need to copy the last row of column A to column H and paste it to the last row of column I. The last rows of the column will always change.

eg; my data is in A2: H2 and I5 - the last cell with the data.
My code should be copied A2: H2 and paste it A3: H5. And the second time I run the macro (after adding new data to the corresponding columns) it should be copied A6: H6 and paste it to the last row of column I.

I wrote two codes that did not fit my needs.

first code;

Sub OrderList1() Range("a65536").End(xlUp).Resize(1, 8).Copy _ (Cells(Cells(Rows.Count, 9).End(xlUp).Row, 1)) End Sub 

this code skips A3: H4 and only inserts into A5: H5

second code:

  Sub OrderList2() Range("A2:H2").Copy Range(Cells(2, 8), _ Cells(Cells(Rows.Count, 9).End(xlUp).Row, 1)) End Sub 

it copies A2: H3 and pastes it A5: H5, but when I add new data, it doesn't start pasting from A5: H5. It starts with A2: H2 and overwrites the old data. I see what I need to change, the range should be a dynamic range, as in the first code, but I can not write the code.

I am very grateful for the help.

Answer 1

You might want to use this as a starting point:

 Dim columnI As Range Set columnI = Range("I:I") Dim columnA As Range Set columnA = Range("A:A") ' find first row for which cell in column A is empty Dim c As Range Dim i As Long i = 1 For Each c In columnA.Cells If c.Value2 = "" Then Exit For i = i + 1 Next c ' ok, we've found it, now we can refer to range from columns A to H of the previous row ' to a variable (in the previous row, column A has not been empty, so it the row we want ' to copy) Dim lastNonEmptyRow As Range Set lastNonEmptyRow = Range(Cells(i - 1, 1), Cells(i - 1, 8)) ' and now copy this range to all further lines, as long as columnI is not empty Do While columnI(i) <> "" lastNonEmptyRow.Copy Range(Cells(i, 1), Cells(i, 8)) i = i + 1 Loop 

Answer 2

Try this for something that allows you to use future features, or at least for me ... Ask if you need help with this :)

 Option Explicit Sub lastrow() Dim wsS1 As Worksheet 'Sheet1 Dim lastrow As Long Dim lastrow2 As Long Set wsS1 = Sheets("Sheet1") With wsS1 'Last row in A lastrow = Range("A" & Rows.Count).End(xlUp).Row 'Last Row in I lastrow2 = Range("I" & Rows.Count).End(xlUp).Row 'Cut in A:H and paste into last row on I wsS1.Range("A2:H" & lastrow).Cut wsS1.Range("I" & lastrow2) End With End Sub