如何将行自动复制到新工作表 VBA Excel [英] how to auto-copy rows to new sheet VBA Excel
问题描述
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
.
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
.
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
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 Sheet:
A D E F
X 4 5 6
X 1 2 3
This is what I've attempted
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
But it returns an Error:
Microsoft Visual Basic
Object variable with block variable not set
I guess you start clearing "X" sheet from row 2 to preserve headers
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屋!