Utilizing a dynamic last row over multiple worksheets-CodePudding

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

     Sub Rloop()
        
        Dim WScount As Integer Dim WSraw As Object Dim i As Integer Dim LastRow As Long Dim LastRowRD As Long Dim 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 contents first 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.

CodePudding user response：

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

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