如何用VBA将Excel中每个ID的最新修订数据复制到新工作表？

Got it, let's get this sorted for you. Since your source data is already grouped by ID with the latest revision at the top of each group, we can simplify things by just grabbing the first row of each ID group and copying it to your new sheet. Here's the completed, corrected VBA code that does exactly that:

Sub ExtractLatestRevisions()
    Dim source As Worksheet
    Dim target As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim targetRow As Long
    Dim prevID As Variant
    
    ' Set your source sheet (adjust the name if yours is different)
    Set source = ThisWorkbook.Worksheets("owssvr (1)")
    ' Create a new target sheet at the end of your workbook
    Set target = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    target.Name = "Latest Revisions" ' Optional: give the new sheet a clear name
    
    ' Copy the header row from source to target
    source.Rows(1).Copy target.Rows(1)
    targetRow = 2 ' Start pasting data below the header
    
    ' Find the last row with data in your source sheet (checks column A; adjust if needed)
    lastRow = source.Cells(source.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize previous ID to a value that won't match the first ID in your data
    prevID = ""
    
    ' Loop through each data row in the source sheet
    For i = 2 To lastRow
        ' **Important**: Adjust "D" to the column letter where your ID is stored
        If source.Cells(i, "D").Value <> prevID Then
            ' Copy this row (the latest revision for the current ID) to the target sheet
            source.Rows(i).Copy target.Rows(targetRow)
            targetRow = targetRow + 1 ' Move to the next empty row in target
            prevID = source.Cells(i, "D").Value ' Update to track the current ID
        End If
    Next i
    
    ' Optional: Auto-fit columns in the target sheet for readability
    target.Columns.AutoFit
    
    ' Let you know when it's done
    MsgBox "Latest revisions extracted successfully!", vbInformation
End Sub

Key Notes to Adjust for Your Data: #

• ID Column: If your ID isn't in column D, replace "D" with the correct column letter (e.g., "C" if it's column C) or use the column number (like 4 for column D).
• Source Sheet Name: Double-check that "owssvr (1)" matches your actual source sheet name—update it if needed.
• Target Sheet Name: The code names the new sheet "Latest Revisions", but you can change that to something else if you prefer.

This works because your data is already sorted with the latest revision at the top of each ID group. The code skips duplicate IDs after the first occurrence, so you only keep the most recent entry for each ID.

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