VBA copy & paste with dynamic range

Question

I'm new with VBA and I'm stuck somewhere. I have to copy last row of column A till column H and paste it untill the last row of column I. Last rows of columnns will be always change.

e.g; my data is in A2:H2 and I5 is the last cell with data.
My code should be copy A2:H2 and paste it A3:H5. And second time I run the macro (after I add new data to respective columns) it should be copy A6:H6 and paste it untill the last row of column I.

I wrote two codes which were not fulfill my needs.

first code is;

  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 pastes to A5:H5

second code is;

 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 paste it A5:H5 but when I add new data it doesn't start to paste from A5:H5. It start from A2:H2 and overwrite to old data. I can see what I have to change,range should be dynamic range like in the first code,but I can't manage to write the code.

I'll really appreciate little help.

JohnB · Accepted Answer · 2012-08-27 09:33:23Z

2

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's 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

edited Aug 27, 2012 at 9:33

answered Aug 27, 2012 at 9:10

JohnB

13.8k6 gold badges42 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JMax Over a year ago

Your answer would be surely better if you added some comments in your code (event if it seems obvious to you, this will probably help other users)

JMax Over a year ago

Yes, it is. I wouldn't have followed the same model but it probably works.

cem karadas Over a year ago

Hi JohnB, first of all thanks for your effort. when I run this code it works fine but when I put it in my code it gives a wrong result. Instead of copying & pasting last row untill the column I. it copies last row and paste it to A2:H2 and copy old data of A2:H2 to last row untill column I.

JohnB Over a year ago

Probably it has to do with what the code considers to be "empty". First of all, you could replace c.Value2 and columnI(i)<>"" by c.Text and columnI(i).Text <> "". In addition, you may have to look into columns B ... H for non-emptiness as well instead of considering only column A.

cem karadas Over a year ago

Hi again, I found the the reason. It was my mistake I made some error in my code. I fixed it and now your code works fine. thanks a lot again for your help.

Matt Ridge · Accepted Answer · 2012-08-27 14:37:13Z

1

Try this one for something that allows future functionality, or at least it did for me...Ask if you need help understanding it :)

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

edited Aug 27, 2012 at 14:37

answered Aug 27, 2012 at 14:26

Matt Ridge

3,62916 gold badges48 silver badges64 bronze badges

Collectives™ on Stack Overflow

VBA copy & paste with dynamic range

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related