EXCEL VBA複製符合條件的列?

2016-12-23 2:26 pm
各位大大好:
我的A欄有 YES 和FALSE兩種選項,我想根據A欄的條件將屬於YES的資料通通複製到另一個工作表 SHEET1 SHEET2
A B C A B C
YES 12 10 YES 12 10
NO 0 12 YES 15 12
NO 11 10
YES 15 12

這是我目前的寫法,但是只有第一列有複製過去,想請問該如何修改呢?

Sub yes()
Dim i, h As Integer
Sheets("工作表1").Select
For i = 1 To 1200
If Cells(i, "F") = "YES" Then
h = h + 1
Rows(i).Select
Selection.Copy
Sheets("工作表2").Select
Rows(h).Select
ActiveSheet.Paste
End If
Next i
End Sub

回答 (2)

2016-12-23 4:17 pm
✔ 最佳答案
try this

Sub yes()
Sheets("Sheet2").Select
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select

For Each r In Range("a1:a4")
If ActiveCell.Value = "YES" Then
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
End If
ActiveCell.Offset(1, 0).Select
Next
End Sub
2016-12-23 10:58 pm
使用篩選
With Sheets("Sheet1")
.UsedRange.AutoFilter Field:=1, Criteria1:="YES"
.UsedRange.Copy Sheets("Sheet2").Cells(1, 1)
End With


收錄日期: 2021-05-03 13:49:43
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20161223062622AAhKoCi

檢視 Wayback Machine 備份