0

I have an issue with my code. I'm opening a Excel file, check how much rows of data it has and then using this row number to copy data from this file into the Excel sheet that the code is in. However, it keeps grabbing the row number from the sheet that the button with the code is in. I've tried multiple things, but I can't seem to find what I'm doing wrong.

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)

The message box keeps returning 31, which is the amount of rows with data in the destination sheet, but it should return the value 15.

Suraido
  • 11
  • 4
  • This `Cells(Rows.Count, "B")` is not qualified, so it assumes `ActiveSheet`. It does not assume that just because you have it mixed in with code from another worksheet that it's the same one. – braX Jan 09 '23 at 00:24
  • 1
    Thanks, braX. That fixed it! Don't know why I didn't notice this after staring at this code for hours. – Suraido Jan 09 '23 at 00:26

1 Answers1

0

Thanks to BraX for providing the answer. I changed this:

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

to this:

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

Suraido
  • 11
  • 4