How to copy from Next Row when I Run Code Again

[ali445455]

Hi,
I need such a code which should copy this range A2:D2 and then paste as simple numbers into this range A1:D1.
When i run this code again then it should copy this range A3:D3 and then paste as simple numbers into this range A1:D1.
When i run this code again then it should copy this range A4:D4 and then paste as simple numbers into this range A1:D1.
and so on

Let's say if range A5:D5 has blank cells, and if i run code. Then instead of copying and pasting this range, it should show a message saying "Task Completed".

Thanks in Advance

 

[RobP]

Hi,

Do you only want to run the code again because you have a new row of data in Ax:Dx , or does your file always have all the rows (1-4) populated with data ?

(wondering if you could detect the last row, then act on that row to paste it back up top ...)

 

[ali445455]

Hi,

Do you only want to run the code again because you have a new row of data in Ax:Dx , or does your file always have all the rows (1-4) populated with data ?

(wondering if you could detect the last row, then act on that row to paste it back up top ...)

does your file always have all the rows (1-4) populated with data ?
No Sir. There is no any specific range of data.

Code should keep on copying pasting data from the below row every time i run it. It should only stop when Blank cells comes. At this time it should show this message that "Task Completed".

 

[ali445455]

Hi, Can anybody please solve my issue. I will be very thankful to you

 

[RobP]

Hi,

Sorry, but I can't think of a way therefore that this could be done based on what you have given us.
Maybe if you shared a little more about your spreadsheet, why its in this format, and what your overall challenge is, maybe some people could suggest other ways to do things.

Rgds
Rob

 

[RobP]

Hi, so I spent a little more time thinking about it.. and this code might get you what you want ?
Its not particularly pretty, and it assumes that each time you run the macro, you still have the previous rows data in cells A1-D1.
If you delete that row, then it will assume you are starting again from Row 1.

Hope it helps.

Sub Test()

Dim Rng1, Rng2 As Range
Dim Rn1, Rn2 As Variant

Set Rng1 = Range("A1:D1")
Rn1 = Range("A1:D1").Value


For x = 2 To 5
    Result = True
    If Application.WorksheetFunction.CountA(Range(Cells(x, 1), Cells(x, 4))) > 0 Then
        If Application.WorksheetFunction.CountA(Rng1) = 0 Then
            Set Rng1 = Range(Cells(x, 1), Cells(x, 4)) 'default row 2 if Row 1 is empty
            Exit For
        Else
            Rn2 = Range(Cells(x, 1), Cells(x, 4)).Value
            For y = 1 To 4
                If Rn1(1, y) <> Rn2(1, y) Then Result = False
            Next y
            If Result = True And x < 4 Then
                rownum = x + 1
                Set Rng1 = ActiveSheet.Range(ActiveSheet.Cells(rownum, 1), ActiveSheet.Cells(rownum, 4))
                Exit For
            End If
            
        End If

    End If

Next x

Sheets("Sheet1").Range("A1:D1") = Rng1.Value
If x = 6 Then MsgBox ("Task Completed")

End Sub