时间戳

首页 > 解决方案 > 自动过滤、复制和插入。即使代码运行没有错误，也不会插入数据

excel - 自动过滤、复制和插入。即使代码运行没有错误，也不会插入数据

问题描述

下面的代码“完美”运行。我可以看到数据被过滤了，我可以看到选择了正确的范围。而且我假设该范围内的数据被复制。

但是，当尝试插入复制的数据时，什么也没有出现。我可以看到应该复制的范围内有数据。

奇怪的是，我知道它在某个时候有效，但突然间就没有了。

Const sFILE_PATH    As String = "C:\Downloads\"
Const sEXTENSION    As String = ".xlsm"

Dim lastrow As Long
Dim lastrow_Offset As Long

Dim wbk As Workbook
Dim sFileName As String

sFileName = "2018"

Set wbk = Workbooks(sFileName & sEXTENSION)


lastrow_Offset = ThisWorkbook.Sheets("Test").Cells(Rows.Count, "h").End(xlUp).Row
lastrow = wbk.Sheets("Acc").Cells(Rows.Count, "C").End(xlUp).Row

Application.ScreenUpdating = False




With wbk.Sheets("Acc")
    .AutoFilterMode = False
    .Range("A4:AL" & lastrow).AutoFilter Field:=37, Criteria1:="=NC", Operator:=xlFilterValues
    .Range("A4:AL" & lastrow).AutoFilter Field:=38, Criteria1:="=New", Operator:=xlFilterValues



     Debug.Print Range("C5:C" & lastrow).SpecialCells(xlCellTypeVisible).Address

    .Range("C5:C" & lastrow).SpecialCells(xlCellTypeVisible).Copy



    .AutoFilterMode = False
End With

ThisWorkbook.Sheets("Test").Range("H26").Offset((lastrow_Offset + 1) - 26, 0).Insert

Application.ScreenUpdating = True

标签： excelvba

解决方案

You don't paste your data. The Insert-command simply creates a new (empty) line, that's all.

Change your code to

With wbk.Sheets(1)
    .AutoFilterMode = False
    .Range("A4:AL" & lastrow).AutoFilter Field:=37, Criteria1:="=NC", Operator:=xlFilterValues
    .Range("A4:AL" & lastrow).AutoFilter Field:=38, Criteria1:="=New", Operator:=xlFilterValues

    lastrow_Offset = ThisWorkbook.Sheets("Test").Cells(Rows.Count, "H").End(xlUp).row
    Dim destCell As Range
    Set destCell = ThisWorkbook.Sheets("Test").Range("H26").Offset((lastrow_Offset + 1) - 26, 0)
    .Range("C1:C" & lastrow).SpecialCells(xlCellTypeVisible).Copy destCell

    .AutoFilterMode = False
End With

Update: as Siddharth Rout pointed out, I was wrong, the Insert pastes data. However, it seems that this is not working if a non-contiguous range was selected for copy - as it is the most cases for filtered data.

In that case, either provide the destination-parameter already when copying the data (as I do in my code above) or use the PasteSpecial-method of the destination range. I prefer to use the first method as it doesn't keep the data in the paste-buffer, but that's a minor thing.

推荐阅读