请求编写VBA宏：将最旧记录移至当前工作表并保存文件

Hey there, I've put together a VBA macro that fits your exact requirements—finding the oldest record (smallest Age value), copying it to your current worksheet, removing it from the main table, and saving the file. Let's break this down:

This macro will:

• Target your main table worksheet (just update the sheet name if yours is different)
• Identify the row with the smallest Age value (the oldest record)
• Copy that entire row to the first empty row of your currently active worksheet
• Delete the original row from the main table
• Save the Excel file automatically

Sub MoveOldestRecord()
    Dim wsMain As Worksheet
    Dim wsCurrent As Worksheet
    Dim lastRowMain As Long
    Dim lastRowCurrent As Long
    Dim minAge As Double
    Dim minAgeRow As Long
    
    ' Set your main table worksheet name here - CHANGE IF NEEDED!
    Set wsMain = ThisWorkbook.Worksheets("MainTable")
    ' Set current worksheet to the one you're running the macro from
    Set wsCurrent = ActiveSheet
    
    ' Handle case where main table has no data (except header)
    lastRowMain = wsMain.Cells(wsMain.Rows.Count, "A").End(xlUp).Row
    If lastRowMain <= 1 Then
        MsgBox "No records found in the main table!", vbExclamation
        Exit Sub
    End If
    
    On Error GoTo ErrorHandler
    
    ' Find the smallest Age value in column C (Age column)
    minAge = WorksheetFunction.Min(wsMain.Range("C2:C" & lastRowMain))
    
    ' Find the row number of the first occurrence of this min Age
    minAgeRow = WorksheetFunction.Match(minAge, wsMain.Range("C:C"), 0)
    
    ' Find the first empty row in current worksheet (starting from column A)
    lastRowCurrent = wsCurrent.Cells(wsCurrent.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Copy the entire row from main table to current worksheet
    wsMain.Rows(minAgeRow).Copy Destination:=wsCurrent.Rows(lastRowCurrent)
    
    ' Delete the original row from main table
    wsMain.Rows(minAgeRow).Delete Shift:=xlUp
    
    ' Save the workbook
    ThisWorkbook.Save
    
    MsgBox "Oldest record moved successfully!", vbInformation
    
    Exit Sub
    
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

• Worksheet Name: Make sure to update "MainTable" to the actual name of your main table worksheet—just check the tab name at the bottom of Excel if you're unsure.
• Column Assumptions: This code assumes Number is in column A, Priority in B, Age in C, with headers in row 1. If your columns are different, adjust the range references (like change "C2:C" to your Age column letter).
• Multiple Matching Records: If there are multiple records with the same smallest Age, this macro picks the first one it finds. If you need to handle ties differently (like prioritize High over Low), let me know and we can tweak the code!
• Error Handling: The macro includes basic error handling to catch issues like no data in the main table or unexpected errors, with clear message boxes to keep you informed.

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