Excel VBA如何仅粘贴值不保留单元格格式？PasteSpecial报错求助

First, let's get to the bottom of your error: that "Expected end of statement" message almost always points to a syntax misstep in how you're using PasteSpecial. Common culprits include missing required syntax elements or overreliance on Select/Selection (which is notoriously error-prone in VBA). Let's fix both issues and get your paste-values-only operation working.

Corrected Code Example #

Here's a refined version of your code that applies filters properly and pastes only values—without relying on Select:

Sub PasteFilteredValuesOnly()
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim filteredDataRange As Range
    
    ' Set your target worksheet (replace "DataSheet" with your actual sheet name)
    Set targetSheet = ThisWorkbook.Worksheets("DataSheet")
    
    ' Find the last used row in column B
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Row
    
    ' Clear any existing filters to start fresh
    If targetSheet.AutoFilterMode Then targetSheet.AutoFilterMode = False
    
    ' Apply filters directly to the range (no Select needed!)
    With targetSheet.Range("A1:S" & lastRow) ' Include header row for proper filtering
        .AutoFilter Field:=2, Criteria1:=Array("PT"), Operator:=xlFilterValues
        .AutoFilter Field:=1, Criteria1:=Array("AMD"), Operator:=xlFilterValues
        .AutoFilter Field:=4, Criteria1:=Array("201701", "201702", "201703", _
                                               "201704", "201705", "201706", _
                                               "201707", "201708"), Operator:=xlFilterValues
    End With
    
    ' Grab only the visible (filtered) rows (skip header row)
    On Error Resume Next ' Prevent crash if no rows match filters
    Set filteredDataRange = targetSheet.Range("A2:S" & lastRow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not filteredDataRange Is Nothing Then
        ' Copy the filtered range
        filteredDataRange.Copy
        
        ' Paste ONLY values to your destination (adjust "A20" to your target cell)
        targetSheet.Range("A20").PasteSpecial Paste:=xlPasteValues
        
        ' Clear the clipboard to remove the lingering copy selection
        Application.CutCopyMode = False
    Else
        MsgBox "No rows matched your filter criteria—nothing to paste!"
    End If
    
    ' Clean up: remove filters from the sheet
    targetSheet.AutoFilterMode = False
End Sub

Key Fixes & Explanations: #

• Ditched Select/Selection: Working directly with ranges is faster, more reliable, and avoids errors caused by unexpected active cells or sheets.
• Proper PasteSpecial Syntax: Using Paste:=xlPasteValues explicitly tells Excel to paste only cell values (no formatting, formulas, or comments). You can also shorten it to PasteSpecial xlPasteValues (since Paste is the first argument), but the explicit version is easier to read later.
• Error Handling for Empty Filters: The On Error Resume Next block prevents your code from crashing if no rows match your filter criteria.
• Filter Cleanup: Resets filters after the operation so your sheet is ready for next use.

Common Mistakes to Avoid: #

• Forgetting to call Copy before PasteSpecial—you can't paste something that hasn't been copied first!
• Misspelling xlPasteValues (it's case-insensitive, but typos will throw errors).
• Trying to use PasteSpecial on a non-visible range (make sure you target only visible cells after filtering).

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