Loop through all WorkSheets is not working, code review please.

[Belair58]

Hello,

I have a workbook with multiple sheets and I'd like to run this macro on all sheets. The code below will run on the first worksheet, but not on all. If anyone can see why it's not looping through all the sheets I'd appreciate it.

<Code>
Sub Add_On_Time_Or_Not_To_Each_Sheet()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

Set shtJT = ActiveWorkbook.ActiveSheet

Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-14]=""Finished"",RC[-1]<=RC[-2]),""On-Time"",IF(AND(RC[-14]=""Finished"",RC[-1]>=RC[-2]),""Late"",IF(AND(RC[-14]=""Pending"",RC[-2]<TODAY()),""Late"",IF(AND(RC[-14]=""Pending"",RC[-2]>TODAY()),""Pending"",IF(AND(RC[-14]="""",RC[-1]=""""),"""",""INCORRECT"")))))"
Range("O2").Select
With shtJT

.Range("O2").AutoFill .Range("O2:O" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
shtJT.Select
Columns("A:O").Select
shtJT.Sort.SortFields. _
Clear
shtJT.Sort.SortFields. _
Add2 Key:=Range("J2:J1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
shtJT.Sort.SortFields. _
Add2 Key:=Range("D2:D1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With shtJT.Sort
.SetRange Range("A1:O1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
'End With
Range("O1").Select
ActiveCell.FormulaR1C1 = "On-Time?"

Range("P1").Value = "Late"
Range("Q1").Value = "Incorrect"
Range("R1").Value = "Pending"
Range("S1").Value = "On-Time"
Range("T1").Value = "Total Pecentage"


ActiveSheet.Select
Range("O65536").End(xlUp).Cells(2, 1).Select
'ActiveCell.Offset(rowOffset:=0, columnOffset:=15).Activate
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1)).Select
'ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=ROUND(COUNTIF(R2C15:R1000C15,""Late"")/COUNTA(R2C15:R1000C15)*100,2)"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1)).Select
ActiveCell.FormulaR1C1 = _
"=ROUND(COUNTIF(R2C15:R1000C15,""Incorrect"")/COUNTA(R2C15:R1000C15)*100,2)"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1)).Select
ActiveCell.FormulaR1C1 = _
"=ROUND(COUNTIF(R2C15:R1000C15,""Pending"")/COUNTA(R2C15:R1000C15)*100,2)"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1)).Select
ActiveCell.FormulaR1C1 = _
"=ROUND(COUNTIF(R2C15:R1000C15,""On-Time"")/COUNTA(R2C15:R1000C15)*100,2)"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1)).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
'Next ws
End With

Columns("A:T").Select
Columns("A:T").EntireColumn.AutoFit
Rows("1:1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True

Next ws


End Sub

</Code>

 

[Fluff]

Are ThisWorkbook and ActiveWorkbook the same workbook?

 

[Belair58]

Are ThisWorkbook and ActiveWorkbook the same workbook?

Yes they are the same.
Thanks

 

[rlv01]

I don't see you using the worksheet loop variable "ws" anywhere.

For each ws in ThisWorkbook.Worksheets
  .
  .
  .
Next ws

You should use "ws" inside the loop somewhere or else there is no point. Instead of

Set shtJT = ActiveWorkbook.ActiveSheet

try

ws.Activate
Set shtJT = ws

and see if that does what you want.

 

[Belair58]

I don't see you using the worksheet loop variable "ws" anywhere.

For each ws in ThisWorkbook.Worksheets
  .
  .
  .
Next ws

You should use "ws" inside the loop somewhere or else there is no point. Instead of

Set shtJT = ActiveWorkbook.ActiveSheet

try

ws.Activate
Set shtJT = ws

and see if that does what you want.

rlv01,

I added your code which does work, but it runs it on my Personal.xlsb. How do I keep it running on the page that I'm on when I start the macro?

Thanks for your help, I really appreciate the help.

 

[Fluff]

Change ThisWorkbook to ActiveWorkbook.

 

[Belair58]

Thank you, work great!!

 

[Fluff]

Glad we could help & thanks for the feedback