Why is my code grabbing row number from the wrong Excel sheet

0 votes

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
    MsgBox (iTotalRows)

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.

Jan 10 in Others by Kithuzzz
• 33,140 points

1 answer to this question.

0 votes

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
answered Jan 10 by narikkadan
• 58,640 points

Related Questions In Others

0 votes
0 answers

Why is Excel not calculating the cube root as the cube root?

I think Excel treats exponents with recurring ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 33,140 points
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 58,640 points
0 votes
1 answer

Calculate the mean from excel sheet for specific rows

Generate a list for each means you ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 58,640 points
0 votes
1 answer

Excel Formula which places date/time in cell when data is entered in another cell in the same row

Here's how to accomplish things in another ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 58,640 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
0 votes
1 answer

Is there a reason why these bars wont line up in my excel chart?

STEPS Hover your mouse over any of the ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 58,640 points
0 votes
1 answer

I have filtered my Excel data and now I want to number the rows. How do I do that?

Solution Filter your data. Select the cells you want ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 58,640 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP