Selected Answer
First to answer your question, this is what you did wrong:-
LROW = Range("a1").End(xlDown).Row
Although the line is within the With block, the range referred to isn't linked to it. Range("a1") is on the ActiveSheet because no other sheet is specified. In order to link the range to the preceding With statement it must be preceded by a period, thus:-
With Workbooks("book1.XLSX").Worksheets("sheet1")
LROW = .Range("a1").End(xlDown).Row
With that said, xlDown will look for the first blank row from the top down. xlUp will search for the first used row from bottom up. Specify the last cell as Cells(Rows.Count, "A"), both linked to the worksheet on which you want the last row. Therefore,
With Workbooks("book1.XLSX").Worksheets("sheet1")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Bear in mind that "Activate" and "Select" are required only to tell Excel what to show on the screen. You can access any open workbook (open any closed workbook) without activating or selecting anything.
Private Sub CommandButton8_Click()
'RELOAD NEW DATA FOR EACH STORE FROM ASCEND QUERY REPORTS
Dim Ws As Worksheet
Dim LRow As Long
'RELOAD NEW DATA RICHARDSON
Set Ws = Workbooks("Book1.xlsx").Sheets("Sheet1")
With Ws
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
MsgBox "Book1 Last Row = " & LRow
End With
End Sub
Observe that the declaration of Ws isn't strictly required.
Set Ws = Workbooks("Book1.xlsx").Sheets("Sheet1")
With Ws
could be combined into
With Workbooks("Book1.xlsx").Sheets("Sheet1")
Newbies tend to be relieved that Excel can guess their wish. By the time they have become old hands at programming they prefer to tell Excel what to do. It's a bit like riding a horse. The horse knows its way home but it isn't always home where you want to go.
