My code is broken, I'm afraid. I'm going to open an Excel file, count the number of rows of data it contains, and then use that row number to copy the data into the Excel sheet where the code resides. However, it continues to take the sheet's row number from which the button with the code is located. Despite my best efforts, I can't seem to figure out what I'm doing incorrectly.
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
The message box keeps returning 31, which is the number of rows with data in the destination sheet, but it should return the value 15.