Got it, let's build this dynamic extraction tool step by step. I'll cover everything from setting up the button to writing the VBA code that handles the filtering and table creation.

Step 1: Insert the ActiveX Button #

First, you need to add the ActiveX command button to your workbook:

• Go to the Developer tab (if you don't see it, enable it via Excel Options > Customize Ribbon).
• Click Insert, then select Command Button (ActiveX Control) from the ActiveX section.
• Draw the button on your worksheet (I recommend putting it on the target sheet or a dedicated control sheet).
• Right-click the button, select Properties, and rename it to something descriptive like btnExtractData (this makes it easier to reference later if needed).

Step 2: Write the VBA Code #

Double-click the button to open the VBA editor, then paste this code. I've added comments to explain each part so you can tweak it to your specific needs:

Sub ExtractComplianceData()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRowSource As Long
    Dim lastRowTarget As Long
    Dim i As Long
    
    ' Update these sheet names to match your workbook
    Set wsSource = ThisWorkbook.Worksheets("SourceData") ' Replace with your source sheet name
    Set wsTarget = ThisWorkbook.Worksheets("ComplianceReport") ' Replace with your target sheet name
    
    ' Clear existing data in target sheet (preserves header row if you have one)
    wsTarget.Range("A2:" & wsTarget.Cells(wsTarget.Rows.Count, wsTarget.Columns.Count).Address).ClearContents
    
    ' Find the last row with data in the source sheet (column BF = 62)
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, 62).End(xlUp).Row
    
    ' Loop through each row in the source sheet (start at row 2 assuming row 1 is header)
    For i = 2 To lastRowSource
        ' Check if column 62 (BF) matches our target statuses
        Select Case UCase(wsSource.Cells(i, 62).Value) ' UCase makes it case-insensitive
            Case "NOT COMPLIANT", "PARTIALLY COMPLIANT"
                ' Find the next empty row in the target sheet
                lastRowTarget = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row + 1
                
                ' Copy your specific columns here - adjust these numbers to match your needs!
                ' Example: Copy source columns A, C, E, BF to target columns A, B, C, D
                wsTarget.Cells(lastRowTarget, 1).Value = wsSource.Cells(i, 1).Value ' Source Col A → Target Col A
                wsTarget.Cells(lastRowTarget, 2).Value = wsSource.Cells(i, 3).Value ' Source Col C → Target Col B
                wsTarget.Cells(lastRowTarget, 3).Value = wsSource.Cells(i, 5).Value ' Source Col E → Target Col C
                wsTarget.Cells(lastRowTarget, 4).Value = wsSource.Cells(i, 62).Value ' Source Col BF → Target Col D
        End Select
    Next i
    
    ' Convert the extracted data into an Excel Table for dynamic functionality (sort/filter/etc.)
    Dim targetTable As ListObject
    On Error Resume Next ' Ignore error if table already exists
    Set targetTable = wsTarget.ListObjects("ComplianceTable")
    On Error GoTo 0
    
    If targetTable Is Nothing Then
        ' Create new table if it doesn't exist
        Set targetTable = wsTarget.ListObjects.Add( _
            SourceType:=xlSrcRange, _
            Source:=wsTarget.Range("A1:" & wsTarget.Cells(lastRowTarget, 4).Address), _
            XlListObjectHasHeaders:=xlYes _
        )
        targetTable.Name = "ComplianceTable"
    Else
        ' Resize existing table to include new data
        targetTable.Resize wsTarget.Range("A1:" & wsTarget.Cells(lastRowTarget, 4).Address)
    End If
    
    ' Optional: Add a success message
    MsgBox "Data extraction finished! Check your Compliance Report sheet.", vbInformation
End Sub

Key Customizations to Make: #

• Sheet Names: Replace "SourceData" and "ComplianceReport" with your actual source and target worksheet names.
• Column Mapping: Adjust the column numbers in the wsTarget.Cells(...) lines to match the specific information you want to extract. For example, if you need to pull column G (7) from the source, change wsSource.Cells(i, 3) to wsSource.Cells(i,7).
• Header Row: If your source sheet doesn't have a header row (row 1), change the loop start from i=2 to i=1.
• Case Sensitivity: I added UCase() to make the status check case-insensitive—remove it if you need exact case matching.

Step 3: Test the Button #

Close the VBA editor, click the Design Mode button in the Developer tab to exit design mode, then click your ActiveX button. The code will:

1. Clear old data from the target sheet (keeping headers)
2. Scan the source sheet for rows with "Not Compliant" or "Partially Compliant" in column BF
3. Copy your specified columns to the target sheet
4. Turn the data into a dynamic Excel Table that supports sorting, filtering, and automatic formatting

Troubleshooting Tips: #

• If nothing happens, make sure macros are enabled (check Excel's Trust Center settings).
• If you get an error, verify that your sheet names and column numbers are correct.
• If the table doesn't update properly, ensure the target sheet's header row matches the columns you're copying.

内容的提问来源于stack exchange，提问作者Aditya pareek