为何Excel VBA中rs.Activate可用而rs.Select失效？技术咨询

Great question! Let's break down why you're seeing this behavior, and how to fix it properly.

Core Issue: Unqualified Range References Depend on the Active Sheet #

The problem isn't that rs.Select is "broken"—it's that your subsequent code relies on the active worksheet to resolve unqualified Range calls, and rs.Select doesn't always guarantee rs becomes the active sheet in every scenario, while rs.Activate does.

Let's Break It Down #

• Difference Between Select and Activate

  • Worksheet.Select: This selects the sheet's tab, and usually activates it. But in edge cases (like if the workbook containing rs isn't the active workbook, or you're working with multiple Excel windows), it might not switch the active sheet to rs.
  • Worksheet.Activate: This explicitly makes rs the active worksheet, no matter what the prior state was. It cuts through any ambiguity about which sheet is currently active.

• The Hidden Bug in Your Code
  Look at this line in your With block:

  With rs.Range("A2:H" & Range("G" & Rows.Count).End(xlUp).Row)
  

  The Range("G" & Rows.Count).End(xlUp).Row part doesn't specify which sheet it belongs to! VBA defaults to using ActiveSheet.Range here.

  • When you use rs.Activate, ActiveSheet is rs, so this correctly references the G column in your Result sheet.
  • When you use rs.Select, if rs didn't become the active sheet (for example, if Checklist is the active workbook while rs is in ThisWorkbook), this Range call would target the active sheet in Checklist instead. That leads to an incorrect row number, making your fill operation seem like it didn't work.

The Best Fix: Stop Relying on Active Sheets #

You don't need Select or Activate at all—they're often the source of flaky VBA code. Instead, explicitly qualify all your Range and Rows references to their parent sheet:

Sub Extract_Data()
    Checklist.Sheets.Add.Name = "DataNew"
    Set msi = ThisWorkbook.Sheets("MS Info")
    Set rs = ThisWorkbook.Sheets("Result")
    Set tmp = ThisWorkbook.Sheets("Temp")
    Set evd = Checklist.Sheets("Evaluation Details")
    Set smm = Checklist.Sheets("Summary")
    
    '''''''''''''''''''''''''''
    '''''''few more codes''''''
    '''''''''''''''''''''''''''
    
    ' Calculate last row using rs explicitly
    Dim lastRow As Long
    lastRow = rs.Range("G" & rs.Rows.Count).End(xlUp).Row
    
    ' Work directly with rs's range, no activation needed
    With rs.Range("A2:H" & lastRow)
        .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    With rs.Range("N2:P" & lastRow)
        .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    
    '''''''''''''''''''''''''''
    '''''''few more codes''''''
    '''''''''''''''''''''''''''
End Sub

This code is more reliable, faster, and avoids any issues with active sheets entirely.

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