使用VBA将数据从一张表复制到另一张 [英] Copy data from one sheet to another using VBA

问题描述

在excel 2007中尝试循环（未固定长度）列（例如C）和行值匹配的位置（例如高），然后将单元格Dx和Bx的值转移到新行中的传输其中x是找到匹配的行＃。假设转移存在。

in excel 2007 trying to loop through (un-fixed length) column (say C) and where row value matches (say "High"), then transfer value of cells Dx and Bx to sheet "transfer" in new row, where x is the row# where the matches are found. Assume "transfer" exists.

到目前为止，我已经得到了：

So far I've got this:

Public Sub CopyRows()
  Sheets("Sheet1").Select 

  'Find the last row of data
  LastRow = Cells(Rows.Count, 1).End(xlUp).Row

  'Loop through each row
  For x = 1 To FinalRow
    'Decide if to copy based on column C
    ThisValue = Cells(x, 3).Value

    If ThisValue = "High" Then
      Cells(x, 1).Resize(1, 33).Copy
      Sheets("Transfer").Select
      NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
      Cells(NextRow, 1).Select
      ActiveSheet.Paste
      Sheets("Sheet1").Select
    End If
  Next x
End Sub 

推荐答案

事实上，这是最好的高级过滤器。

In fact, that's best done with advanced filter.

创建两个范围 - 一个条件（标题和一个单元格）：

Create two ranges -- one for the condition (header and a cell):

*C column header* |
-------------------
High              |

一个用于想要的数据（仅限标题）：

And one for wanted data (header only):

*B column header* | *D column header*
-------------------------------------

然后使用

range("A:D").advancedfilter xlFilterCopy, range("criteria range"), range("copy range")

显然，使用Excel界面（Data - Advanced Filter）可以轻松完成。

Obviously, this is easier done with Excel interface (Data - Advanced Filter).

这篇关于使用VBA将数据从一张表复制到另一张的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！