为什么我的代码从错误的Excel表抓取行号?

Question

我的代码有一个问题。我打开一个Excel文件，检查它有多少行数据，然后使用这个行号将数据从这个文件复制到代码所在的Excel工作表中。但是，它会一直从包含代码的按钮所在的工作表中获取行号。我试了很多方法，但我似乎找不到我做错了什么。

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 & "censoredcensoredExtract 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
iTotalRows = source.Worksheets("Export").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count
MsgBox (iTotalRows)

消息框一直返回31，这是目标工作表中包含数据的行数，但它应该返回值15。

Answer 1

感谢BraX提供的答案。我改了这个:

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

:

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