时间戳

首页 > 解决方案 > 如何根据输入条件将数据提取到另一张表

excel - 如何根据输入条件将数据提取到另一张表

问题描述

工作表“数据”由 4 列组成 - 年、日、月和性别。我想将数据提取到工作表“报告” - 年份和性别。应在输入年份时提取数据。你能告诉我如何使用 Excel VBA 代码吗？

标签： excelvba

解决方案

尝试：选项显式

Sub test()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim LastRow1 As Long, LastRow2 As Long, i As Long

    With ThisWorkbook

        'Set Worksheets
        Set ws1 = .Worksheets("Data")
        Set ws1 = .Worksheets("Report")

        'Find last row in Data
        LastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row

        'Loop from row 2 to  last row
        For i = 2 To LastRow1
            'Test Year
            If ws1.Range("A" & i).Value = "2019" Then

                LastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
                'Copy - Paste Year to ws2 at column A
                ws1.Range("A" & i).Copy ws2.Range("A" & LastRow2 + 1)
                'Copy - Paste Gender to ws2 at column B
                ws1.Range("D" & i).Copy ws2.Range("B" & LastRow2 + 1)

            End If

        Next i

    End With

End Sub

推荐阅读