VBA代码为何从错误Excel工作表获取行数？

问题场景 #

打开外部Excel文件后，需要统计其中指定工作表的有效行数，再将对应数据复制到代码所在工作簿，但代码执行后，消息框返回的始终是带执行按钮的工作表行数（31），而非源文件的正确行数（15）。

错误原因 #

问题出在获取行数的代码行：

iTotalRows = source.Worksheets("Export").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count

其中Cells(Rows.Count, "B").End(xlUp).Row没有指定所属的工作表对象，VBA会默认使用当前活动工作表（即运行按钮所在的目标工作簿工作表），导致读取的是目标表的行数，而非源文件的Export工作表行数。

修正后的代码 #

需要将Cells和Rows.Count都明确绑定到源工作表对象上，确保读取的是源文件的数据：

Private Sub BrewhouseDataAdd_Click()
    
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    ' Set username
    Dim UserName As String
    UserName = VBA.Environ("username")
    
    ' Set up destination
    Dim destination As Workbook
    Set destination = ThisWorkbook

    ' Get date for filename
    Dim dateFromCell As String
    dateFromCell = destination.Worksheets("Front Page").Range("E4").Value

    Dim dateForFileName As String
    dateForFileName = Format(dateFromCell, "YYYYMMDD")

    ' Create source workbook name
    Dim sourceFileName As String
    sourceFileName = "Brewhouse " & dateForFileName & ".xlsx"
    
    Dim sourceFilePath As String
    sourceFilePath = "C:\Users\" & UserName & "\censored\censored\Extract Waste"

    ' Set source workbook (False for "Update Links" and True for "Read-Only Mode")
    Dim source As Workbook
    Set source = Workbooks.Open(sourceFilePath & "\Data Import\" & sourceFileName, False, True)

    ' Get the total rows from the source workbook (using column B as this will only pick the relevant rows)
    Dim iTotalRows As Integer
    ' 修正：明确指定Cells和Rows属于源工作表
    With source.Worksheets("Export")
        iTotalRows = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Rows.Count
    End With
    MsgBox (iTotalRows)
    
    ' 后续复制数据的逻辑也需要保持对象前缀的一致性，避免类似错误
    ' ...
    
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub

补充说明 #

• 使用With语句可以简化代码，同时确保所有单元格操作都指向源工作表，避免对象混淆。
• 后续复制数据时，同样要注意明确指定源和目标的工作表/范围对象，避免默认活动表导致的错误。

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