Utilizing a dynamic last row over multiple worksheets

I have some code below, which is intended to find the lastrow of each worksheet ("r.xls" within a single workbook) and copy and paste the information over to a sheet (r) within a workbook ("priority.xls"). there are 15 sheets within the "r.xls" workbook. The issue that I am having is that the macro is not recognizing the lastrow for each individual worksheet, rather it recognizing the last row for sheet 14 and using that as the basis for the lastrow for each sheets. hence some sheet entries are cut early.

my code is below

Option Explicit
Sub Rloop()
  Dim WScount As Integer, WSraw As Object, i As Integer
  Dim LastRow As Long, LastRowRD As Long, LastRowU As Long
  
  'Open raw data file from "priority.xlsx" spreadsheet
  Set WSraw = Workbooks.Open(Sheets("Dashboard").Range("E4"))
  Application.CutCopyMode = False
  Application.DisplayAlerts = False
  
  'Set number of tabs in workbook
  WScount = WSraw.Worksheets.Count
  
  'Clear data
  ThisWorkbook.Sheets("Returns").Range("C23:X1000000").ClearContents ' clear 1st section
  ThisWorkbook.Sheets("Returns").Range("Y24:AD100000").ClearContents ' second section
  
  For i = 1 To 15 'To 15 sheets
    'Define last row/column in respective tab
    LastRow = Cells(Sheets(i).Rows.Count, "A").End(xlUp).Row 'ISSUE
    LastRowRD = ThisWorkbook.Sheets("R").Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row
    Sheets(i).Range("A9:T" & LastRow).Copy
    ThisWorkbook.Sheets("R").Range("E23:X" & LastRowU).PasteSpecial xlPasteValues
  Next i
End Sub

any help would be greatly appreciated. Thank you.

Solution 1:

You don't qualify Cells in the statement where you set LastRow, and therefore the cells of the Active Sheet are used. You need to qualify all Cells and Range references (tell VBA which worksheet you want to use).

The full statement would be

LastRow = Sheets(i).Cells(Sheets(i).Rows.Count, "A").End(xlUp).Row

As this is a little hard to read, usually either a With-statement or a variable is used, but that is completely up to you.

Btw: You should also tell with Workbook you want to use, else the Active Workbook will be used.

With WSraw.Sheets(i)
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Or

Dim ws as Worksheet 
Set ws = WSraw.Sheets(i)  
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row