Copy and Paste row by index number in Excel Macro
Solution 1
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
Solution 2
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 modulethis 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 32767avoid 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
Paul Etscheit
Updated on July 18, 2022Comments
-
Paul Etscheit almost 2 years
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.