VBA - 将表行复制并粘贴到另一个表 [英] VBA - Copy and Paste Table Row to Another Table

问题描述

我对VBA非常新鲜，我正在努力解决我认为应该是一个非常简单的问题（我有一个Java / J2EE背景）...我循环一个表，想要将行复制到一个表在另一个基于一些条件语句的工作表上。请参阅下面的代码。

  Sub closeActionItems（）
 Dim i，iLastRow As Integer 
 Dim date1 As Date 
 Dim oLastRow As ListRow 
 
 date1 = Date 
 iLastRow = ActiveSheet.ListObjects（Open_Items）。ListRows.Count 
 
 For i = 6 To iLastRow 
如果Cells（i，7）.Value< = date1和Cells（i，7）.Value< vbNullString然后
 Rows（i）.Copy 
设置oLastRow =工作表（已关闭）ListObject（Closed_Items）。ListRows.Add 
'粘贴到新行
 
 Application.CutCopyMode = False 
 Rows（i）.EntireRow.Delete 
 End If 
 Next 
 End Sub 
  

我已经尝试了许多不同的迭代，但是找不到正确的方式将剪贴板的内容复制到新创建的行。

任何帮助将不胜感激。提前感谢

解决方案

将srcRow定义为如下所示的范围：

  Dim srcRow as Range 
  

然后在你的循环中尝试这样做： p>

 设置srcRow = ActiveSheet.ListObjects（Open_Items）。ListRows（i）.Range 
设置oLastRow = Worksheets（关闭）ListObjects（Closed_Items）。ListRows.Add 
 
 srcRow.Copy 
 oLastRow.Range.PasteSpecial xlPasteValues 
 
 Application.CutCopyMode = False 
行（i）.EntireRow.Delete 
  

请注意，您仍然有一个问题，删除行，因为你试图循环它们，所以你可能想要改变你的循环，使它从底部开始上升。

I am very new to VBA and I am trying to solve what I think should be a very simple problem (I have a Java/J2EE background)... I am looping through a table and want to copy rows to a table on another worksheet based on some conditional statements. See code below.

Sub closeActionItems()
    Dim i, iLastRow As Integer
    Dim date1 As Date
    Dim oLastRow As ListRow

    date1 = Date
    iLastRow = ActiveSheet.ListObjects("Open_Items").ListRows.Count

    For i = 6 To iLastRow
        If Cells(i, 7).Value <= date1 And Cells(i, 7).Value <> vbNullString Then
            Rows(i).Copy
            Set oLastRow = Worksheets("Closed").ListObject("Closed_Items").ListRows.Add
            'Paste into new row

            Application.CutCopyMode = False
            Rows(i).EntireRow.Delete
        End If
    Next
End Sub

I have tried many different iterations but have been unable to find the correct way to copy the contents of the clipboard to the newly created row.

Any help would be appreciated. Thanks ahead of time.

解决方案

Define srcRow as a Range like so:

Dim srcRow as Range

Then in your loop try doing this:

        Set srcRow = ActiveSheet.ListObjects("Open_Items").ListRows(i).Range
        Set oLastRow = Worksheets("Closed").ListObjects("Closed_Items").ListRows.Add

        srcRow.Copy
        oLastRow.Range.PasteSpecial xlPasteValues

        Application.CutCopyMode = False
        Rows(i).EntireRow.Delete

Notice that you still have a problem in that you are deleting rows as you are trying to loop through them, so you probably want to change your loop so that it starts at the bottom and goes up.

这篇关于VBA - 将表行复制并粘贴到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！