Copy and Paste row by index number in Excel Macro

Question

I'm trying to copy an entire row by index number and paste it to another row with a different index number when a certain condition is met (I know the issue is not with the conditional logic). I'm thinking of something like this:

Sub Makro1()

Dim i As Integer

With ActiveSheet
    'for looping
    totalRows = .Cells(.Rows.Count, "A").End(xlUp).Row

    'index of last row even after rows have been added
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    'data starts at row #3
    For i = 3 To totalRows
        If .Cells(i, 19).Value > 0 Then
            Number = .Cells(i, 19).Value
            Do While Number > 0
                lastRow = lasRow + 1
                'Next line doesnt do anything
                .Rows(lastRow) = .Rows(i).Value
                Number = Number - 1
            Loop
        End If
    Next i
End With
End Sub

The logic works like its supposed to but no lines are pasted. I've gone step by step and am certain the problem is not with the logic.

Do the `TotalRows` and `LastRow` variables give you the values you are expecting? — RGA, Jun 16 '16 at 08:20
Also, there appears to be a typo in the `Lastrow = lastRow + 1` line. Unclear if that is causing the error in your code (It won't be seen if you do not use `Option Explicit` at the start of your module — RGA, Jun 16 '16 at 08:21
@Nathan_Sav It should start the same but is incremented during OP's loop — RGA, Jun 16 '16 at 08:25

Ajeet Shah · Accepted Answer · 2016-06-16T09:13:01.383

I assume that you want to copy Rows(i) and paste it as value in Rows(lastRow). So, you need to replace this line

 .Rows(lastRow) = .Rows(i).Value

with these two lines:

.Rows(i).Copy
.Rows(lastRow).PasteSpecial xlPasteValues

Or

.Rows(lastRow).Copy
.Rows(i).PasteSpecial xlPasteValues

if you want to copy Rows(lastRow) and paste it as value in Rows(i).

Edit:

To paste everything (formulas + values + formats), use paste type as xlPasteAll.

Reference: msdn

score 2 · Answer 2 · 2016-06-16T08:31:33.727

2

Range Copy and Paste

Syntax

Range().Copy [Destination]

The square brackets indicate that Destination is an optional parameter. If you don't designate a Destination range it copies the selection to the clipboard. Otherwise it copies the first range directly to the new location.

Change this line:

.Rows(lastRow) = .Rows(i).Value

To:

.Rows(lastRow).copy .Rows(i)

It's worth noting that

.Rows(lastRow).copy .Cells(i, 1)

Will also work. Excel will resize the Destination range to fit the new data.

edited Jun 16 '16 at 08:31

answered Jun 16 '16 at 08:25

Is the `Destination:=` call not required here? – RGA Jun 16 '16 at 08:27
1

@RGA `Destination:=` call is optional. Both of thses works, e.g. `.Rows(1).Copy Destination:=.Rows(2)` and `.Rows(1).Copy .Rows(2)` and does the same. – Ajeet Shah Jun 16 '16 at 13:58

score 2 · Answer 3 · answered Jun 16 '16 at 09:05

your code works for me

so just add a breakpoint at .Rows(lastRow) = .Rows(i).Value statement and then query all relevant variables value in the Immediate Window, like:

?lastRow
?.Rows(lastRow).Address
?i
?.Rows(i).Address

in the meanwhile you could

add Option Explicit statement at the very top of your code module

this will force you to declare all variables and thus lead to some extra work, but you'll get repaid with much more control over your variables usage and misspelling, thus saving debugging time
dim variables to hold rows index as of Long type, to handle rows index higher then 32767
avoid inner loop using the Resize() method of range object

much like follows:

Option Explicit

Sub Makro1()

    Dim i As Long, totalRows As Long, lastRow As Long, Number As Long

    With ActiveSheet
        'for looping
        totalRows = .Cells(.Rows.Count, "A").End(xlUp).Row

        'index of row to add from
        lastRow = totalRows + 1 '<--| start pasting values one row below the last non empty one in column "A"

        'data starts at row #3
        For i = 3 To totalRows
            If .Cells(i, 19).Value > 0 Then
                Number = .Cells(i, 19).Value
                .Rows(lastRow).Resize(Number).Value = .Rows(i).Value
                lastRow = lastRow + Number
            End If
        Next i
    End With
End Sub

Copy and Paste row by index number in Excel Macro

3 Answers3

Range Copy and Paste

Linked