MS-Excel在填入数据后复制新工作表中的特定单元格
问题描述:
我有工作表“重新计算的FS”列A:B,C,D,E ..AI 如果AI =“YES”然后在新的工作表“Sheet2”中复制列B和D,我有这个代码复制所有列,我不知道如何为列B和D工作, Ps:我还想重新命名“Sheet2”,B - > columnB和C - > columnCMS-Excel在填入数据后复制新工作表中的特定单元格
Sub tgr()
Dim wsData As Worksheet
Dim wsDest As Worksheet
Set wsData = Sheets("Recalculated FS")
Set wsDest = Sheets("Sheet2")
With wsData.Range("AI2", wsData.Cells(Rows.Count, "AI").End(xlUp))
.AutoFilter 1, "YES"
.CurrentRegion.Copy wsDest.Range("A1")
.AutoFilter
End With
End Sub
请帮忙吗? 现在我需要添加到过滤器1(AI列),另一个过滤器2(列AK),所以我怎么能做到这一点的验证码
Option Explicit
Sub tgr()
Dim wsData As Worksheet
Dim wsDest As Worksheet
Set wsData = Sheets("Recalculated FS")
Set wsDest = Sheets("Sheet2")
' set wsData sheet to be active, to allow filterring
wsData.Select
With wsData.Range("AI2", wsData.Cells(Rows.Count, "AI").End(xlUp))
' Column AI is col num 35
.AutoFilter 35, "YES"
End With
wsData.Columns("B:B").Select
Selection.Copy
wsDest.Select
Columns("B:B").Select
ActiveSheet.Paste
wsData.Select
wsData.Columns("D:D").Select
Selection.Copy
wsDest.Select
Columns("D:D").Select
ActiveSheet.Paste
wsDest.Range("B1").Value = "ColumnB"
wsDest.Range("D1").Value = "ColumnD"
End Sub
答
以下副本列B和列代码D工作表Sheet2,当列AI =是。
编辑1:拷贝列B和列d逐个表2(而不复制C栏)
编辑2:柱AK的添加的第二过滤标准=是的
Option Explicit
Sub tgr()
Dim wsData As Worksheet
Dim wsDest As Worksheet
Set wsData = Sheets("Recalculated FS")
Set wsDest = Sheets("Sheet2")
' set wsData sheet to be active, to allow filterring
wsData.Select
With wsData.Range("AI2", wsData.Cells(Rows.Count, "AI").End(xlUp))
' Column AI is col num 35
.AutoFilter 35, "YES"
.AutoFilter 37, "YES"
End With
wsData.Columns("B:B").Select
Selection.Copy
wsDest.Select
Columns("B:B").Select
ActiveSheet.Paste
wsData.Select
wsData.Columns("D:D").Select
Selection.Copy
wsDest.Select
Columns("D:D").Select
ActiveSheet.Paste
wsDest.Range("B1").Value = "ColumnB"
wsDest.Range("D1").Value = "ColumnD"
End Sub
谢谢@Shai Rado,但它也需要列C这是一个范围,我在VBA新的,所以很抱歉我的问题,目标文件开始s B列而不是A – BKChedlia
@BKChedlia查看编辑的答案代码,您想将列B和D复制到列B和D而不复制列C,是否正确? –
它的工作原理,非常感谢 – BKChedlia