英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论