Copy filtered data from one sheet to another.

huangapple go评论52阅读模式
英文:

Copy filtered data from one sheet to another

问题

我正在尝试将sht1上表格的筛选数据复制到sht2(sht2没有表格)。以下是使用以下代码可以正常工作的部分。我遇到的问题是,它总是将sht1上表格的所有数据复制到sht2。我想要的是一种只复制筛选数据到sht2的方法。我查阅了几个类似的问题,但是(考虑到我的知识有限),我无法在我的代码中实现它们。非常感谢任何帮助。

Private Sub CommandButton1_Click()
   Range("A2:P100").ClearContents
   Set sht1 = ThisWorkbook.Sheets("DRIVE input form")
   Set sht2 = ThisWorkbook.Sheets("Dowlex 2023")
   If sht2.Range("C1").Value = "" Then Exit Sub

   Dim lastRow As Long
   sht1.Range("F2:F100").Value = sht2.Range("C2:C100").Value
   sht1.Range("G2:G100").Value = sht2.Range("E2:E100").Value
   sht1.Range("K2:K100").Value = sht2.Range("F2:F100").Value
   sht1.Range("M2:M100").Value = sht2.Range("G2:G100").Value
   sht1.Range("J2:J100").Value = sht2.Range("A2:A100").Value
   sht1.Range("P2:P100").Value = sht2.Range("H2:H100").Value
   sht1.Range("N2:N100").Value = sht2.Range("I2:I100").Value
End Sub
英文:

I am trying to copy the filtered data from a table on sht1 to sht2 (which does not have a table). This works fine using the following code. The problem I encounter, it always copies all the data from the table on sht1 to sht2. What I am looking for is a way to copy only the filtered data to sht2. I looked up several similar questions, but (with my very limited knowledge) I am not able to implement them in my code. Any help is much appreciated.

Private Sub CommandButton1_Click()
   Range("A2:P100").ClearContents
   Set sht1 = ThisWorkbook.Sheets("DRIVE input form")
   Set sht2 = ThisWorkbook.Sheets("Dowlex 2023")
   If sht2.Range("C1").Value = "" Then Exit Sub

   Dim lastRow As Long
   sht1.Range("F2:F100").Value = sht2.Range("C2:C100").Value
   sht1.Range("G2:G100").Value = sht2.Range("E2:E100").Value
   sht1.Range("K2:K100").Value = sht2.Range("F2:F100").Value
   sht1.Range("M2:M100").Value = sht2.Range("G2:G100").Value
   sht1.Range("J2:J100").Value = sht2.Range("A2:A100").Value
   sht1.Range("P2:P100").Value = sht2.Range("H2:H100").Value
   sht1.Range("N2:N100").Value = sht2.Range("I2:I100").Value
End Sub

答案1

得分: 2

您可以使用SpecialCells来仅获取可见行:

Sub Tester()
    Dim wsSrc As Worksheet, wsDest As Worksheet
    
    Set wsSrc = ThisWorkbook.Worksheets("data")
    Set wsDest = ThisWorkbook.Worksheets("filtered")
    
    CopyVisibleValues wsSrc.Range("A2:A200"), wsDest.Range("B2")
    CopyVisibleValues wsSrc.Range("B2:B200"), wsDest.Range("A2")
    
    Application.CutCopyMode = False
End Sub

Sub CopyVisibleValues(rngSrc As Range, rngDest As Range)
    Dim rngVis As Range
    On Error Resume Next 'ignore error if no visible rows
    Set rngVis = rngSrc.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0      'stop ignoring errors
    
    If Not rngVis Is Nothing Then
        rngVis.Copy
        rngDest.PasteSpecial xlPasteValues
    Else
        Debug.Print "No visible cells in " & rngSrc.Address(False, False)
    End If
End Sub
英文:

You can use SpecialCells to get only visible rows:

Sub Tester()
    Dim wsSrc As Worksheet, wsDest As Worksheet
    
    Set wsSrc = ThisWorkbook.Worksheets("data")
    Set wsDest = ThisWorkbook.Worksheets("filtered")
    
    CopyVisibleValues wsSrc.Range("A2:A200"), wsDest.Range("B2")
    CopyVisibleValues wsSrc.Range("B2:B200"), wsDest.Range("A2")
    
    Application.CutCopyMode = False
End Sub



Sub CopyVisibleValues(rngSrc As Range, rngDest As Range)
    Dim rngVis As Range
    On Error Resume Next 'ignore error if no visible rows
    Set rngVis = rngSrc.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0      'stop ignoring errors
    
    If Not rngVis Is Nothing Then
        rngVis.Copy
        rngDest.PasteSpecial xlPasteValues
    Else
        Debug.Print "No visible cells in " & rngSrc.Address(False, False)
    End If
End Sub

huangapple
  • 本文由 发表于 2023年6月30日 00:46:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76583064.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定