我试图auto-copy
一行从master
spreadsheet
到另一个spreadsheet
。当输入值在master
等于X
.
So if X
被输入到Column A
in the master
, then auto-copy
分开spreadsheet
(我们称之为 X)。基本上Sheet X
应始终包含所有内容的精确副本rows
in master
where Column A = X
.
我不确定这是否会影响auto-copy
但是master sheet
包含一个script
隐藏/取消隐藏Columns
. So if X
被输入到Column A
in the master sheet
then Column B,C
将被隐藏并且D,E,F
将显示。
我想要实现的目标的示例如下所示:
Master Sheet
包含此信息。但如果X
被输入到Column A
only D,E,F
将可见
A B C D E F
X 4 5 6
Y 1 2 3 4 5
X 1 2 3
X Sheet:
A D E F
X 4 5 6
X 1 2 3
这就是我尝试过的
Sub FilterAndCopy()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Sheets("Master")
Set sht2 = Sheets("X")
Intersect(sht2.UsedRange, sht2.Rows("2:" & Rows.Count)).ClearContents
sht1.Cells(1, 1).CurrentRegion.AutoFilter
sht1.Cells(1, 1).CurrentRegion.AutoFilter 1, "X"
sht1.Cells(1, 1).CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy sht2.Cells(2, 1)
sht1.Cells(1, 1).CurrentRegion.AutoFilter
End Sub
但它返回一个错误:
Microsoft Visual Basic
Object variable with block variable not set