在Excel宏中按索引号复制并粘贴行 [英] Copy and Paste row by index number in Excel Macro

问题描述

我试图通过索引号复制整行，并在满足某个条件时将其粘贴到具有不同索引号的另一行（我知道问题不在条件逻辑中）。我在想这样的事情：

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.

推荐答案

我假设你想复制 Rows（i）并将其作为值粘贴到 Rows（lastRow）中。因此，您需要替换此行

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

这两行：

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

或

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

如果要复制 Rows（lastRow）并将其粘贴为中的值 。

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

编辑：

要粘贴所有内容（公式+值+格式），请使用粘贴类型为 xlPasteAll 。

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

参考： msdn

这篇关于在Excel宏中按索引号复制并粘贴行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！