C
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
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