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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlos" wrote in message
...
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