| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have to run macro twice to work? Why
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have to run macro twice to work? Why
'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 On Jun 22, 3:48*pm, Carlos wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have to run macro twice to work? Why
I think the "Exit Sub" on line 5 might be the culprit best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have to run macro twice to work? Why
Thanks Both, I had a feeling it was that, but I wasn't sure what to replace it with! Many thanks Carl "Per Jessen" wrote: HI Carlos In the "For Each ws...." loop, you have an Exit Sub statement when you have renamed the sheet, this will quit the macro. All you need is a "Exit For" statement instead. Best regards, Per "Carlos" skrev i meddelelsen ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have to run macro twice to work? Why
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I have to run macro twice to work? Why
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlos" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to update a column in a work based on another work sheet | New Users to Excel | |||
| macro doesn't work | Excel Programming | |||
| Macro works Macro does not work | Excel Discussion (Misc queries) | |||
| If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
| Why does the macro not work? | Excel Worksheet Functions | |||
Linear Mode
