技术需求：遍历销售数据，将超目标值的数据复制至新工作表

Got it, let's tackle this problem step by step. I'll walk you through a VBA script that perfectly fits your need—grabbing a user input threshold, scanning your sales data, and copying all rows with values above that threshold to a brand new worksheet.

Step 1: Core Logic Overview #

Here's the flow we'll implement:

• Prompt the user to enter their target filter value
• Validate the input to avoid errors
• Create a new worksheet to store the filtered data
• Copy the header row from your source data to the new sheet
• Loop through every row of sales data, checking if the value exceeds the threshold
• Copy matching rows to the new worksheet
• Finish with a confirmation message

Step 2: Full VBA Code #

Paste this into a VBA module in your Excel file:

Sub CopySalesAboveThreshold()
    Dim targetValue As Double
    Dim wsSource As Worksheet
    Dim wsNew As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim newRow As Long
    
    ' Get user input for the threshold value
    On Error Resume Next
    targetValue = InputBox("Enter the target threshold value:", "Sales Filter Threshold")
    On Error GoTo 0
    
    ' Handle cancel or invalid input
    If targetValue = 0 And Str(targetValue) <> "0" Then
        MsgBox "Invalid input or operation canceled. Exiting macro.", vbExclamation
        Exit Sub
    End If
    
    ' Set your source worksheet (update "SalesData" to your actual sheet name)
    Set wsSource = ThisWorkbook.Worksheets("SalesData")
    
    ' Create and name the new worksheet
    Set wsNew = ThisWorkbook.Worksheets.Add
    wsNew.Name = "SalesAbove_" & Format(targetValue, "#,##0.00")
    
    ' Copy header row to new sheet
    wsSource.Rows(1).Copy Destination:=wsNew.Rows(1)
    newRow = 2 ' Start pasting data from row 2 (after header)
    
    ' Find the last row with data in the source sheet (column A as reference)
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each data row
    For i = 2 To lastRow ' Skip header row (row 1)
        ' Adjust column "D" to the column where your sales values are stored
        If wsSource.Cells(i, "D").Value > targetValue Then
            ' Copy the entire row to the new sheet
            wsSource.Rows(i).Copy Destination:=wsNew.Rows(newRow)
            newRow = newRow + 1
        End If
    Next i
    
    ' Auto-fit columns in the new sheet for better readability
    wsNew.Columns.AutoFit
    
    ' Confirmation message with row count
    MsgBox "Completed! Copied " & newRow - 2 & " rows to the new worksheet.", vbInformation
End Sub

Step 3: Customization Tips #

• Update source sheet name: Change "SalesData" to the actual name of your worksheet with sales data
• Adjust sales value column: Replace "D" with the column letter where your numeric sales values are located (e.g., "B" if values are in column B)
• Modify new sheet naming: Tweak the Format(targetValue, "#,##0.00") part if you want a different name format for the new worksheet

Step 4: How to Run the Script #

1. Open your Excel file containing the sales data
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer → Insert → Module
4. Paste the code into the new module window
5. Go back to Excel, press Alt + F8, select CopySalesAboveThreshold, and click Run
6. Enter your target threshold value in the prompt and click OK

内容的提问来源于stack exchange，提问作者Jack Ellis