• • 此问题在此处已有答案**：

Excel VBA, getting range from an inactive sheet（3个答案）
17小时前关门了。
我的代码有问题。我正在打开一个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 & "\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
    iTotalRows = source.Worksheets("Export").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count
    MsgBox (iTotalRows)

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