如何将行自动复制到新工作表VBA Excel [英] how to auto-copy rows to new sheet VBA Excel

问题描述

我正在尝试从母版 电子表格<< c $ c>自动复制行/ code>到另一个电子表格。当 master 中的输入值等于 X 时，就会发生这种情况。

I'm trying to auto-copy a row from a master spreadsheet to another spreadsheet. This should occur when the input value in the master is equal to X.

因此，如果在列A 中输入 X 母版，然后自动复制以分隔电子表格（我们称之为X）。基本上， Sheet X 应该始终包含 master 行的精确副本。 c $ c>其中 A列= X 。

So if X is entered into Column A in the master, then auto-copy to separate spreadsheet (let's call it X). Basically Sheet X should always contain an exact copy of all the rows in master where Column A = X.

我不确定这是否会影响自动复制，但主表包含脚本，该隐藏/取消隐藏列。因此，如果将 X 输入到主表中的列A 中，然后列B，C 将被隐藏，并显示 D，E，F 。

I'm not sure if this would affect the auto-copy but the master sheet contains a script that hides/unhides Columns. So if X is entered into Column A in the master sheet then Column B,C will be hidden and D,E,F will be shown.

下面是我要实现的目标的示例：

An example of what I'm trying to achieve is shown below:

Master Sheet 包含此信息。但是，如果将 X 输入到列A 中，则仅 D，E，F 将可见

Master Sheet contains this info. But if X is entered into Column A only D,E,F will be visible

A B C D E F
X     4 5 6
Y 1 2 3 4 5
X     1 2 3

X工作表：

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

推荐答案

我猜你开始清除第2行中的 X工作表以保留标题

I guess you start clearing "X" sheet from row 2 to preserve headers

如果是这种情况，您可以清除所有 X工作表行并将表头粘贴到主工作表中

should this be the case, you can clear all "X" sheet rows and paste headers form "Master" sheet back

Option Explicit

Sub FilterAndCopy()
    Dim sht1 As Worksheet, sht2 As Worksheet

    Set sht1 = Sheets("Master")
    Set sht2 = Sheets("X")

    sht2.UsedRange.ClearContents
    Dim rng As Range

    With sht1.Cells(1, 1).CurrentRegion
        .AutoFilter
        .AutoFilter 1, "X"
        For Each rng In .SpecialCells(xlCellTypeVisible).Areas ' loop through visible cells "groups"
            rng.Copy sht2.Range(rng.Address) ' copy current group and paste it to 'sht2' (i.e. sheet "X") corresponding address
        Next
        .AutoFilter
    End With

    With sht2.UsedRange ' reference 'sht2' used range
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete ' delete referenced sheet blank rows
        .Rows(1).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete ' delete referenced sheet blank columns
    End With
End Sub

已编辑，以说明可能存在的主工作表隐藏列

Edited to account for possible "Master" sheet hidden columns

这篇关于如何将行自动复制到新工作表VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！