提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何复制和插入行多次或在Excel中复制行x次?

在日常工作中,您是否曾尝试复制行或每行,然后在工作表中的当前数据行下方插入多次? 例如,我有一系列单元格,现在,我想复制每一行并将它们粘贴到下一行3次,如下面的截图所示。 你怎么能在Excel中处理这个工作?


根据特定数字轻松复制并插入多行

通常,除了处理手动复制和插入之外,没有一种好的方法可以多次复制和插入行。 但是,随着 Kutools for Excel's 基于单元格值重复行/列 功能,你可以轻松解决这个问题。 点击下载Kutools for Excel!

Kutools for Excel:比300方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


使用VBA代码多次复制并插入特定的行

如果您只想复制一个特定的行x次,以下VBA代码可能会对您有所帮助,请按照以下步骤操作:

1。 指定一个要复制并插入多次的行,然后按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:多次复制一个特定的行:

Sub test()
'Updateby Extendoffice
    Dim xCount As Integer
LableNumber:
    xCount = Application.InputBox("Number of Rows", "Kutools for Excel", , , , , , 1)
    If xCount < 1 Then
        MsgBox "the entered number of rows is error, please enter again", vbInformation, "Kutools for Excel"
        GoTo LableNumber
    End If
    ActiveCell.EntireRow.Copy
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(xCount, 0)).EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

3。 粘贴代码后,请按 F5 运行此代码的键,弹出一个提示框提醒您输入要复制的次数,请参见屏幕截图:

4。 然后点击 OK 按钮,并在所选行下方插入了三个新复制的行,请参见屏幕截图:


用VBA代码多次复制并插入每行

要在一个范围内多次复制每行,可以应用以下VBA代码,请按照以下方式进行操作:

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:多次复制每一行:

Sub insertrows()
'Updateby Extendoffice
Dim I As Long
Dim xCount As Integer
LableNumber:
xCount = Application.InputBox("Number of Rows", "Kutools for Excel", , , , , , 1)
If xCount < 1 Then
MsgBox "the entered number of rows is error ,please enter again", vbInformation, "Kutools for Excel"
GoTo LableNumber
End If
For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 2 Step -1
Rows(I).Copy
Rows(I).Resize(xCount).Insert
Next
Application.CutCopyMode = False
End Sub

3。 然后按 F5 运行此代码的键,弹出一个提示框,提醒您输入每个记录要复制的次数,请参见屏幕截图:

4。 然后点击 OK,并且每行都被复制并在活动的下面插入3次,参见截图:

注意:在上面的代码中, A 如果数据从列开始,则表示数据范围在A列开始 K, 请更换 AK 作为你的需要。


根据特定数字复制并插入每一行,并具有很棒的功能

也许,您不熟悉VBA代码,或者担心代码会导致数据崩溃。 在这里,我将介绍一个有用的功能, Kutools for Excel's 基于单元格值重复行/列使用此实用程序,您可以根据指定的数字快速复制和插入行。

提示:申请这个 基于单元格值重复行/列 功能,首先,你应该下载 Kutools for Excel,然后快速轻松地应用该功能。

安装后 Kutools for Excel,请这样做:

1。 在数据旁边的单元格列表中输入要复制行的重复数字,请参见屏幕截图:

2。 点击 Kutools > 插页 > 基于单元格值重复行/列,看截图:

3。 在 复制并插入行和列 对话框中选择 复制并插入行 在选项 类型 部分,然后选择要复制的数据范围,然后指定复制行的重复时间,请参见屏幕截图:

4。 然后,点击 Ok or 申请 按钮,您将根据需要获得以下结果:

点击下载Kutools for Excel和免费试用版吧!


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Malik · 1 months ago
    Copy And Insert Each Row Multiple Times With VBA Code

    In VBA code how can I select the starting row
  • To post as a guest, your comment is unpublished.
    Sal · 2 months ago
    hi this worked great, only i need two rows copied N number of times. Can you please adjust the code so when i select two rows, it will then copy those two rows down N times ? thanks for your help
    • To post as a guest, your comment is unpublished.
      skyyang · 2 months ago
      Hello, Sal,
      If you need to only copy two rows down by multiple times, you just need to use the VBA code 1 in this article, apply the code two times will solve your problem. Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Deep Saha · 3 months ago
    how to perform the same action with selected cells using VB,


    I need to repeat a few selected cells (rows and columns) x no of times in the same sheet and in the same order as:

    line1
    line2
    line3

    line1
    line2
    line3
  • To post as a guest, your comment is unpublished.
    Jamar · 5 months ago
    How can I do this for columns?
  • To post as a guest, your comment is unpublished.
    Jamar · 5 months ago
    how do I do this for columns?
  • To post as a guest, your comment is unpublished.
    Andreea · 8 months ago
    Love it! It works every time! Thank you so much for this :)
  • To post as a guest, your comment is unpublished.
    Sashitharan Govindasamy · 11 months ago
    Hello, I need help, this is how my table looks, for example, when I add 2 row at the end of my 1st table, I want the vba help me to do the same thing for the table below (add 2 rows at the end of the table). So my job will be, only need to add rows at the 1st table, the table bellows all will generate rows automatically based on numbers of row in the 1st table. the are the coding I used now to add row for the 1st table. I hope someone can solve this issue for me, thx.Sub InsertNumRows()

    ActiveCell.EntireRow.Copy

    Dim Rng As Long

    Application.DisplayAlerts = False
    On Error Resume Next
    Rng = InputBox("Enter number of rows required.")
    On Error GoTo 0
    Application.DisplayAlerts = True

    If Rng = 0 Then
    MsgBox "You didn't specify a range!"
    Exit Sub

    Else
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(Rng, 0)).Select
    Selection.EntireRow.Insert Shift:=xlDown
    End If

    End Sub
  • To post as a guest, your comment is unpublished.
    devin · 11 months ago
    Love the code! how do i set the start range to skip the first row (Column Headers) and start on row 2?
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hi, Devin,
      To skip the first header row, please apply the following VBA code:

      Sub insertrows()
      Dim I As Long
      Dim xCount As Integer
      LableNumber:
      xCount = Application.InputBox("Number of Rows", "Kutools for Excel", , , , , , 1)
      If xCount < 1 Then
      MsgBox "the entered number of rows is error ,please enter again", vbInformation, "Kutools for Excel"
      GoTo LableNumber
      End If
      For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 2 Step -1
      Rows(I).Copy
      Rows(I).Resize(xCount).Insert
      Next
      Application.CutCopyMode = False
      End Sub

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    naz · 1 years ago
    Really thanks for your support !!!!
  • To post as a guest, your comment is unpublished.
    May · 1 years ago
    Hi, how can I change the code to include the following

    1. duplicate based on number of days
    2. change column EOM from end of month to daily date
  • To post as a guest, your comment is unpublished.
    Talen · 1 years ago
    I used this macros in the past and it has always worked until lately. Any insight why it would only duplicate the top row even when using the multiple row macros? I have removed all filters/frozen panes.
  • To post as a guest, your comment is unpublished.
    Adnan · 1 years ago
    I want to duplicate rows based on a parameter. For example, I have a column named meeting duration time. If that column is more than 1, I want to duplicate meeting ID rows based on that number. If meeting duration is 2 hours, then duplicate meetingID two times. If meeting is 3 hours then duplicate three times.
    half an hour increments can be roundup to the hour.

    MeetingID Time of Start Time of End Meeting Duration
    43117 9:00 AM 11:00 AM 02:00
    43580 9:30 AM 11:00 AM 01:30
    42699 10:00 AM 11:30 AM 01:30
    12345 01:00 PM 2:00 PM 01:00
    • To post as a guest, your comment is unpublished.
      Erin · 1 years ago
      I have the same problem. Have you found a solution?
      • To post as a guest, your comment is unpublished.
        skyyang · 1 years ago
        Hi, guys,
        May be the following VBA code can do you a favor, please apply it:
        Sub insertrows()
        Dim I As Long
        Dim xRg As Range
        Dim xHour, xMin As Long
        On Error Resume Next
        Application.ScreenUpdating = False
        For I = Range("D" & Rows.CountLarge).End(xlUp).Row To 1 Step -1
        xHour = 0
        Set xRg = Range("D" & I)
        xHour = Hour(xRg.Value) + 1
        xMin = Minute(xRg.Value)
        If xMin < 30 Then xHour = xHour - 1
        Rows(I).Copy
        Rows(I).Resize(xHour).Insert
        Next
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
        End Sub

        Please try it. Hope it can help you!
  • To post as a guest, your comment is unpublished.
    Rushabh Patel · 1 years ago
    i want to generate duplicate rows randomly,

    for eg. generate random duplicate rows between 20 to 80.


    what will be updated code for the following


    Sub insertrows()
    'Updateby Extendoffice 2016616
    Dim I As Long
    Dim xCount As Integer
    LableNumber:
    xCount = Application.InputBox("Number of Rows", "Kutools for Excel", , , , , , 1)
    If xCount < 1 Then
    MsgBox "the entered number of rows is error ,please enter again", vbInformation, "Kutools for Excel"
    GoTo LableNumber
    End If
    For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 1 Step -1
    Rows(I).Copy
    Rows(I).Resize(xCount).Insert
    Next
    Application.CutCopyMode = False
    End Sub
  • To post as a guest, your comment is unpublished.
    mate · 1 years ago
    real nice macro mate!
  • To post as a guest, your comment is unpublished.
    BJ Conley · 1 years ago
    Awesome Macro - Saved me so much time!
  • To post as a guest, your comment is unpublished.
    Zach · 1 years ago
    Thank you!