Excel VBA技术求助：非零值按位置复制至指定工作表

Hey there, let's work through the issues in your VBA macro. I noticed a couple of syntax mistakes and inefficiencies, especially around how you define your loop range and handle the copy-paste process. Here's a revised version of your code with explanations of the fixes:

Revised Working Code #

Option Explicit

Sub CopyNonZeroValuesToOverview()
    Dim Cell As Range, cRange As Range
    Dim wsDestination As Worksheet, wsSource As Worksheet
    Dim lastRowSource As Long, lastRowDest As Long
    
    ' Set worksheet references cleanly using variables
    With ThisWorkbook
        Set wsSource = .Worksheets(.Sheets.Count) ' Target the last worksheet
        Set wsDestination = .Worksheets("Overview")
    End With
    
    ' Find the last used row in the source sheet's column A
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Correctly define the range we want to loop through
    Set cRange = wsSource.Range("A1:A" & lastRowSource)
    
    ' Start pasting at row 1 of the Overview sheet (matches your A1→A1 request)
    lastRowDest = 1
    
    For Each Cell In cRange
        ' Check for non-zero values (swap to Cell.Value > 0 if you only want positives)
        If Cell.Value <> 0 Then
            ' Direct value assignment is faster and more reliable than copy-paste
            wsDestination.Cells(lastRowDest, "A").Value = Cell.Value
            lastRowDest = lastRowDest + 1
        End If
    Next Cell
    
    ' Optional: Clear the clipboard to avoid lingering copy selections
    Application.CutCopyMode = False
End Sub

Key Fixes & Improvements #

• Added Option Explicit: This forces you to declare all variables, which catches typos and undefined variable errors (your original LastRow1 wasn't declared, which could lead to unexpected behavior).
• Fixed range definition: Your original line for setting cRange had invalid syntax. The revised code uses wsSource.Range("A1:A" & lastRowSource) to clearly target the correct range on your source worksheet.
• Removed unnecessary Select: Using Sheets("Overview").Select is prone to errors (e.g., if the sheet is hidden) and slows down your macro. We work directly with the wsDestination variable instead.
• Replaced copy-paste with direct assignment: Copying and pasting is slow for large datasets. Directly assigning values (wsDestination.Cells(lastRowDest, "A").Value = Cell.Value) is much faster and avoids clipboard issues.
• Optimized destination row tracking: Instead of recalculating the last row in the Overview sheet every time through the loop, we start at row 1 and increment only when we copy a value—this ensures your non-zero values map directly to the same row positions as the source (e.g., source A3 → Overview A3 if it's non-zero).
• Clarified non-zero check: If you only want to copy positive values (not all non-zero), just change Cell.Value <> 0 back to Cell.Value > 0.

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