I have to run macro twice to work? Why

[Carlos]

I've put two sub's together, the first part to rename the the sheet, then the
second part that adds all the formulas.

when i F8 through it works fine, when I F5 or run it stops after renaming
the sheet. then I run again and it goes through. What am I missing after the
first part to make it run through without stopping?

Thanks
Carl

Sub calculations()

'This part renames the worksheet from ilxoverdue to calculations

For Each ws In ActiveWorkbook.Worksheets
If LCase(Left(ws.Name, 3)) = "ilx" Then
newname = "Calculations"
ws.Name = newname
Exit Sub
End If

Next

'this adds 2 rows to to the spreasheet and the totals

Sheets("Calculations").Select
Rows("1:2").Select
Selection.Insert Shift:=xlDown
Range("T1").Formula = "=sum($T$4:$T$6000)"
Range("U1").Formula = "=sum($U$4:$U$6000)"
Range("V1").Formula = "=sum($V$4:$V$6000)"
Range("W1").Formula = "=sum($W$4:$W$6000)"
Range("X1").Formula = "=sum($X$4:$X$6000)"


Lastrowcalc = Sheets("Calculations").Range("D" & Rows.Count).End(xlUp).Row
Sheets("Calculations").Select

Range("T3").Formula = "50% Against Query"
Range("T4").Select
ActiveCell.Formula = "=$G4*0.5"
Selection.AutoFill Destination:=Range("T4:T" & Lastrowcalc),
Type:=xlFillDefault

Range("U3").Formula = "25%"
Range("U4").Select
ActiveCell.Formula = "=$O4*0.25"
Selection.AutoFill Destination:=Range("U4:U" & Lastrowcalc),
Type:=xlFillDefault

Range("V3").Formula = "75%"
Range("V4").Select
ActiveCell.Formula = "=$P4*0.75"
Selection.AutoFill Destination:=Range("V4:V" & Lastrowcalc),
Type:=xlFillDefault

Range("W3").Formula = "100%"
Range("W4").Select
ActiveCell.Formula = "=SUM($Q4:$S4)"
Selection.AutoFill Destination:=Range("W4:W" & Lastrowcalc),
Type:=xlFillDefault

Range("X3").Formula = "Total Provision"
Range("X4").Select
ActiveCell.Formula = "=IF($F4<=0,0,IF(SUM($T4:$W4)<=0,0,SUM($T4:$W4)))"
Selection.AutoFill Destination:=Range("X4:X" & Lastrowcalc),
Type:=xlFillDefault



End Sub

 

[Susan]

'This part renames the worksheet from ilxoverdue to calculations

'and this part also tells it to exit the sub once you've
'named the new sheet "Calculations"!
For Each ws In ActiveWorkbook.Worksheets
If LCase(Left(ws.Name, 3)) = "ilx" Then
newname = "Calculations"
ws.Name = newname
'--------> Exit Sub
End If

try removing the exit sub & see if that works.

susan

 

[Bernard Liengme]

I think the "Exit Sub" on line 5 might be the culprit
best wishes

 

[Carlos]

Thanks Both,

I had a feeling it was that, but I wasn't sure what to replace it with!

Many thanks
Carl

 

[Don Guillett]

Sub Doitlikethis()
For Each ws In ActiveWorkbook.Worksheets
If LCase(Left(ws.Name, 3)) = "ilx" Then
With ws
lastrowcalc = .Cells(Rows.Count, "d").End(xlUp).Row
.Rows("1:2").Insert Shift:=xlDown
.Range("t1:x1").Formula = "=sum(T4:T" & lastrowcalc & ")"
.Range("T3").Formula = "50% Against Query"
.Range("T4").Formula = "=$G4*0.5"
.Range("U3").Formula = "25%"
.Range("U4").Formula = "=$O4*0.25"
.Range("V3").Formula = "75%"
.Range("V4").Formula = "=$P4*0.75"
.Range("W3").Formula = "100%"
.Range("W4").Formula = "=SUM($Q4:$S4)"
.Range("X3").Formula = "Total Provision"
.Range("X4").Formula = "=IF($F4<=0,0," & _
"IF(SUM($T4:$W4)<=0,0,SUM($T4:$W4)))"
.Range("t4:x4").AutoFill Destination:= _
.Range("t4:x" & lastrowcalc)
.Name = "Calculations"
End With
Exit Sub
End If
Next ws
End Sub

 

[Carlos]

Wow, Thanks Don. It's going to take time for me to get that efficient at
VBA!. It does help having this sort of guidance though.

Many thanks
Carl

 

[Don Guillett]

Glad to help. It takes a while. Just try to keep it simple and only select
when absolutely necessary. In this case I think what you were doing would
have worked had you simply done the naming at the END and then exited sub.