VBA代码将选定的列从符合条件的行复制到另一个工作表 [英] VBA code to copy selected columns from rows that meet a condition to another sheet

问题描述

我刚刚开始使用Excel的VBA代码，所以如果这看起来是基本的，那么道歉。我想要执行以下操作...

检查名为索引的工作表的列J（J5到J500）是否存在值Y。这是我的情况。
然后，我只想将满足条件的任何行的列C只复制到到现有工作表和不同位置的单元格，即如果索引值C3到I3被复制我想将它们粘贴到我进入的活动工作表的A5到G5，说Sheet2。

如果索引表中有更改，我希望自动复制数据，如有可能。如果新的数据被添加到索引中，它可以如何工作？

在这里进行了大量的搜索后，我发现此。从这个问题我稍微改变了代码，以适应我的要求，这将把满足条件的整个行复制到我从中运行宏的一个工作表，但是我仅仅为了选择某些列而困惑。

  Sub CopyRowsAcross（）
 
 Dim i As Integer 
 Dim ws1 As Worksheet：Set ws1 = ThisWorkbook.Sheets （索引）
 Dim ws2 As Worksheet：设置ws2 = ActiveSheet 
 
对于i = 2到ws1.Range（B65536）。End（xlUp）.Row 
如果ws1.Cells（i，2）=Y然后ws1.Rows（i）.Copy ws2.Rows（ws2.Cells（ws2.Rows.Count，2）.End（xlUp）.Row + 1）
 Next i 
 
 End Sub 
  

任何帮助都赞赏

John

编辑：我创建了一个模型，它位于 https://docs.google.com/file/d/0B0RttRif9NI0TGl0N1BZQWZfaFk/edit?usp=sharing

A和B列复制时不需要 - 或者是列J - 这是我用来检查条件。

感谢您的帮助，迄今为止。

解决方案

这是更优雅的解决方案，更类似于我的原始帖子。唯一的区别是单元格引用符合正确的表格。

  Sub try3（）
 Dim i，x As Long 
 Dim Y as String 
 Dim ws1 As Worksheet：设置ws1 = ActiveWorkbook.Sheets（Index）
 Dim ws2 As Worksheet：设置ws2 = ActiveWorkbook.Sheets（Sheet2）'活动工作表让你陷入困境
 
x = 5 
 Y =Y
对于i = 2到500：
如果ws1.Cells（i，10）= Y Then 
 Range（ws2.Cells（x，1），ws2.Cells（x，7））。Value = Range（ws1.Cells（i，3），ws1.Cells（i，9））。 b $ bx = x + 1 
 End If 
 Next i 
 End Sub 
  

I've just started out with VBA code for Excel so apologies if this appears basic. I want to do the following...

Check Column J (J5 to J500) of a sheet called "Index" for the presence of value "Y". This is my condition. Then I want to only copy Columns C to I Only of any row that meets the condition to an existing Sheet and to Cells in a different position, i.e. If Index values C3 to I3 are copied I would like to paste them to A5 to G5 of the active sheet i'm in, say Sheet2.

If there is a change to the index sheet I would like the copied data to automatically, If possible. How could it work if new data is added to Index?

After a lot of searching here I found this. From this question I changed the code slightly to suit my requirements and this will copy entire rows that meet the condition to a sheet that I run the macro from, but I'm stumped for how to select certain columns only.

Sub CopyRowsAcross() 

Dim i As Integer 
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Index") 
Dim ws2 As Worksheet: Set ws2 = ActiveSheet 

For i = 2 To ws1.Range("B65536").End(xlUp).Row 
If ws1.Cells(i, 2) = "Y" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1) 
Next i 

End Sub 

Any Help is appreciated

John

EDIT: I have created a mock-up and its located at https://docs.google.com/file/d/0B0RttRif9NI0TGl0N1BZQWZfaFk/edit?usp=sharing

The A and B Columns are not required when copied - either is Column J - thats what I am using to check for the condition.

Thanks for all your help so far.

解决方案

Here is the more elegant solution, more similar to my original post. The only difference is that the Cells reference is qualified to the correct sheet.

Sub try3()
Dim i, x As Long
Dim Y as String
Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("Index")
Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Sheet2") 'active sheet can get  you into trouble

 x = 5
 Y = "Y"
 For i = 2 To 500:
    If ws1.Cells(i, 10) = Y Then
       Range(ws2.Cells(x, 1), ws2.Cells(x, 7)).Value = Range(ws1.Cells(i, 3), ws1.Cells(i, 9)).Value
      x = x + 1
    End If
 Next i
End Sub

这篇关于VBA代码将选定的列从符合条件的行复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！