Rows Count for Multiple Worksheets in Multiple Workbook

[Dahlia]

Hello Excel Expert,

Can anyone pls correct my code below, please. It supposed to read the rows count for each sheet then do something based on rows count value for that sheet but when I run the code below, it only read once only at the first sheet then use that same value towards all sheets in that same workbook.

Code:

For Each Sheet In ActiveWorkbook.Sheets
Application.DisplayAlerts = False

Sheet.Visible = xlSheetVisible
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
With Sheet
.Columns(1).Insert
.Cells(1, 1) = "Source"
.Cells(2, 1) = tix
.Range("A2").Copy Destination:=.Range("A2:A" & LRow)
.Cells(1, 13) = "File"
.Cells(2, 13) = FileName
.Range("M2").Copy Destination:=.Range("M2:M" & LRow)
End With
Sheet.Copy After:=ThisWorkbook.Sheets("Macros")
Next Sheet

Appreciate some prompt help.

Thanks a million.
DZ

 

[Marc L]

Hi !

Bad logic here :​

LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
With Sheet

Last row comes from the active sheet ‼

Instead of the pointed sheet :​

With Sheet
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

Do you like it ? So thanks to click on bottom right Like !
​

 

[Dahlia]

Hi Marc L,

Thanks so much for saving my day. Your solution totally works! Awesome!