>我有一组数据,这些数据已被自动过滤以满足某些标准。我想将这个数据子集粘贴到新工作表中,因此它本身,然后将其从主工作表中删除。
我已经记下了大部分,除了最后一部分,将其从主表中删除。主要问题是我需要保留主工作表的标题行,并且由于这些结果是自动过滤的,因此我不能指望第二行是 2。我尝试了许多不同的东西,最新的是在我将我需要的东西复制到第二张纸后偏移到第二行。这是我到目前为止所拥有的(是的,有些东西被注释掉进行测试,我不需要用宝贵的时间做我知道有效的事情)
Sub copyToN2r()
'
' copyton2r Macro
'
'
Dim Lr As Long
Dim start As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
'Range("A" & Lr).SpecialCells(xlCellTypeVisible).Copy
Destination:=Sheets("N2R Register").Range("A1")
'Range("A1:F1").Copy
Range("A1").Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
'Range("A1").Paste
End Sub
看看下面的代码段是否适合您 - 内联评论:
Sub copyToNewSheet()
' data that will be copied
Dim dataWithHeader As Range
Dim dataOnly As Range
Set dataWithHeader = [Sheet3!a1].CurrentRegion
With dataWithHeader
Set dataOnly = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With
' so now you copy data and the header
dataWithHeader.Copy [Sheet4!a1]
' and delete copied visible rows
' you can't iterate throw rows and delete those rows at the same time
' as it will cause issues. We first need to store all the ranges we'll delete
' in a collection -- and then iterate through that collection
Dim rw As Range
Dim cRowsToDel As New Collection
For Each rw In dataOnly.SpecialCells(xlCellTypeVisible).Rows
cRowsToDel.Add rw
Next
For Each rw In cRowsToDel
rw.Delete xlShiftUp
Next
' remove autofilter to see the results
If [Sheet3].AutoFilterMode Then [Sheet3].ShowAllData
End Sub
过滤掉列标题和第 2 行和第 3 行的测试数据集(粘贴到 A1 中):
col1 col2 col3
data11 data21 data31
data12 data22 data32
data13 data23 data33
data14 data24 data34
data15 data25 data35