|
code question
i have a routine i wrote to add a range to an exiting formula. it has 24
formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks -- Gary |
code question
Go ahead and post it. I'm sure someone will be able to take it on.
Gary Keramidas wrote: i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks |
code question
Yes, OK to post the code.
Tim "Gary Keramidas" wrote in message ... i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks -- Gary |
code question
Don't post attachments: no-one will read them. Paste the code into the
message body - work on line breaks as required. Tim "Gary Keramidas" wrote in message ... ok, here it is. i used an attachment to help with line breaks -- Gary "Gareth" wrote in message ... Go ahead and post it. I'm sure someone will be able to take it on. Gary Keramidas wrote: i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks |
code question
ok
Option Explicit Dim uName As String Dim rNum As Integer Dim mName As Integer Dim cnt As Integer Dim Counter As Integer Sub Add_User() Application.ScreenUpdating = False Counter = 1 cnt = 1 mName = 4 uName = "New Name Here" ' enter new person's name (workbook name) rNum = 39 + mName ' starting row for data in new sheet (43) Do For Counter = 1 To 12 Step 1 Sheets(mName).Select With Sheets(mName) ActiveSheet.Unprotect .Range("C5").Formula = .Range("c5").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C6").Formula = .Range("c6").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D5").Formula = .Range("D5").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D6").Formula = .Range("D6").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E5").Formula = .Range("E5").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E6").Formula = .Range("E6").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C7").Formula = .Range("c7").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C8").Formula = .Range("c8").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D7").Formula = .Range("D7").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D8").Formula = .Range("D8").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E7").Formula = .Range("E7").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E8").Formula = .Range("E8").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C9").Formula = .Range("c9").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C10").Formula = .Range("c10").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D9").Formula = .Range("D9").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D10").Formula = .Range("D10").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E9").Formula = .Range("E9").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E10").Formula = .Range("E10").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C11").Formula = .Range("c11").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C12").Formula = .Range("c12").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D11").Formula = .Range("D11").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D12").Formula = .Range("D12").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E11").Formula = .Range("E11").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E12").Formula = .Range("E12").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" End With mName = mName + 1 rNum = 39 + mName ActiveSheet.Protect Next Counter Exit Do Loop Application.ScreenUpdating = True MsgBox "Done" End Sub -- Gary "Tim Williams" <saxifrax@pacbell*dot*net wrote in message ... Don't post attachments: no-one will read them. Paste the code into the message body - work on line breaks as required. Tim "Gary Keramidas" wrote in message ... ok, here it is. i used an attachment to help with line breaks -- Gary "Gareth" wrote in message ... Go ahead and post it. I'm sure someone will be able to take it on. Gary Keramidas wrote: i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks |
code question
Hi Gary,
Independently of your code, I would suggest that you expand the ScreenUpdate wrapper to include calculation and (while you are at it) also include pagebreak displays. somehing like: Dim CalcMode As Long Dim PgBreakMode As Boolean With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet PgBreakMode = .DisplayPageBreaks .DisplayPageBreaks = False End With ' Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With ActiveSheet.DisplayPageBreaks = PgBreakMode End Sub --- Regards, Norman |
code question
hi norman:
i added your code. it ran ok, still took a minute and a half. each formula has 12 links to other files, so they are quite long. they're about 738 characters long to begin with. thanks for the info and code - - Gary "Norman Jones" wrote in message ... Hi Gary, Independently of your code, I would suggest that you expand the ScreenUpdate wrapper to include calculation and (while you are at it) also include pagebreak displays. somehing like: Dim CalcMode As Long Dim PgBreakMode As Boolean With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet PgBreakMode = .DisplayPageBreaks .DisplayPageBreaks = False End With ' Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With ActiveSheet.DisplayPageBreaks = PgBreakMode End Sub --- Regards, Norman |
code question
Seems odd it should take so long, so we are dealing with small improvements
here, but here are a couple more. The Do loop seems totally superfluous, so remove the lines Do Exit Do Loop As you only deal with one sheet, no need to select it each loop, and the With reference can be outside the Loop With Sheets(mName) For Counter = 1 To 12 Step 1 .Unprotect 'main code mName = mName + 1 rNum = 39 + mName ActiveSheet.Protect Next Counter End With -- HTH Bob Phillips "Gary Keramidas" wrote in message ... ok Option Explicit Dim uName As String Dim rNum As Integer Dim mName As Integer Dim cnt As Integer Dim Counter As Integer Sub Add_User() Application.ScreenUpdating = False Counter = 1 cnt = 1 mName = 4 uName = "New Name Here" ' enter new person's name (workbook name) rNum = 39 + mName ' starting row for data in new sheet (43) Do For Counter = 1 To 12 Step 1 Sheets(mName).Select With Sheets(mName) ActiveSheet.Unprotect .Range("C5").Formula = .Range("c5").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C6").Formula = .Range("c6").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D5").Formula = .Range("D5").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D6").Formula = .Range("D6").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E5").Formula = .Range("E5").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E6").Formula = .Range("E6").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C7").Formula = .Range("c7").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C8").Formula = .Range("c8").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D7").Formula = .Range("D7").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D8").Formula = .Range("D8").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E7").Formula = .Range("E7").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E8").Formula = .Range("E8").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C9").Formula = .Range("c9").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C10").Formula = .Range("c10").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D9").Formula = .Range("D9").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D10").Formula = .Range("D10").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E9").Formula = .Range("E9").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E10").Formula = .Range("E10").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C11").Formula = .Range("c11").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C12").Formula = .Range("c12").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D11").Formula = .Range("D11").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D12").Formula = .Range("D12").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E11").Formula = .Range("E11").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E12").Formula = .Range("E12").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" End With mName = mName + 1 rNum = 39 + mName ActiveSheet.Protect Next Counter Exit Do Loop Application.ScreenUpdating = True MsgBox "Done" End Sub -- Gary "Tim Williams" <saxifrax@pacbell*dot*net wrote in message ... Don't post attachments: no-one will read them. Paste the code into the message body - work on line breaks as required. Tim "Gary Keramidas" wrote in message ... ok, here it is. i used an attachment to help with line breaks -- Gary "Gareth" wrote in message ... Go ahead and post it. I'm sure someone will be able to take it on. Gary Keramidas wrote: i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks |
code question
BTW, it took about 1 sec on my machine, what do you have?
-- HTH Bob Phillips "Gary Keramidas" wrote in message ... hi norman: i added your code. it ran ok, still took a minute and a half. each formula has 12 links to other files, so they are quite long. they're about 738 characters long to begin with. thanks for the info and code - - Gary "Norman Jones" wrote in message ... Hi Gary, Independently of your code, I would suggest that you expand the ScreenUpdate wrapper to include calculation and (while you are at it) also include pagebreak displays. somehing like: Dim CalcMode As Long Dim PgBreakMode As Boolean With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet PgBreakMode = .DisplayPageBreaks .DisplayPageBreaks = False End With ' Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With ActiveSheet.DisplayPageBreaks = PgBreakMode End Sub --- Regards, Norman |
code question
Looked at it some more and the loop is also unnecessary. Just use an Excel
formula that sums Sub Add_User() With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet PgBreakMode = .DisplayPageBreaks .DisplayPageBreaks = False End With cnt = 1 mName = 4 uName = "Bob" ' enter new person's name (workbook name) Rnum = 39 + mName ' starting row for data in new sheet (43) With Sheets(mName) .Unprotect .Range("C5").Formula = .Range("C5").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C6").Formula = .Range("C6").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D5").Formula = .Range("D5").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D6").Formula = .Range("D6").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E5").Formula = .Range("E5").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E6").Formula = .Range("E6").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C7").Formula = .Range("c7").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C8").Formula = .Range("c8").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D7").Formula = .Range("D7").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D8").Formula = .Range("D8").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E7").Formula = .Range("E7").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E8").Formula = .Range("E8").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C9").Formula = .Range("c9").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C10").Formula = .Range("c10").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D9").Formula = .Range("D9").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D10").Formula = .Range("D10").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E9").Formula = .Range("E9").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E10").Formula = .Range("E10").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C11").Formula = .Range("C11").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C12").Formula = .Range("C12").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D11").Formula = .Range("D11").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D12").Formula = .Range("D12").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E11").Formula = .Range("E11").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$H$" & Rnum + 11 & ")" .Range("E12").Formula = .Range("E12").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" mName = mName + 1 Rnum = 39 + mName .Protect End With With Application CalcMode = .Calculation .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With MsgBox "Done" End Sub -- HTH Bob Phillips "Gary Keramidas" wrote in message ... ok Option Explicit Dim uName As String Dim rNum As Integer Dim mName As Integer Dim cnt As Integer Dim Counter As Integer Sub Add_User() Application.ScreenUpdating = False Counter = 1 cnt = 1 mName = 4 uName = "New Name Here" ' enter new person's name (workbook name) rNum = 39 + mName ' starting row for data in new sheet (43) Do For Counter = 1 To 12 Step 1 Sheets(mName).Select With Sheets(mName) ActiveSheet.Unprotect .Range("C5").Formula = .Range("c5").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C6").Formula = .Range("c6").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D5").Formula = .Range("D5").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D6").Formula = .Range("D6").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E5").Formula = .Range("E5").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E6").Formula = .Range("E6").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C7").Formula = .Range("c7").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C8").Formula = .Range("c8").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D7").Formula = .Range("D7").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D8").Formula = .Range("D8").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E7").Formula = .Range("E7").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E8").Formula = .Range("E8").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C9").Formula = .Range("c9").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C10").Formula = .Range("c10").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D9").Formula = .Range("D9").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D10").Formula = .Range("D10").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E9").Formula = .Range("E9").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E10").Formula = .Range("E10").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C11").Formula = .Range("c11").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C12").Formula = .Range("c12").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D11").Formula = .Range("D11").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D12").Formula = .Range("D12").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E11").Formula = .Range("E11").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E12").Formula = .Range("E12").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" End With mName = mName + 1 rNum = 39 + mName ActiveSheet.Protect Next Counter Exit Do Loop Application.ScreenUpdating = True MsgBox "Done" End Sub -- Gary "Tim Williams" <saxifrax@pacbell*dot*net wrote in message ... Don't post attachments: no-one will read them. Paste the code into the message body - work on line breaks as required. Tim "Gary Keramidas" wrote in message ... ok, here it is. i used an attachment to help with line breaks -- Gary "Gareth" wrote in message ... Go ahead and post it. I'm sure someone will be able to take it on. Gary Keramidas wrote: i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks |
code question
it takes about a second here with blank worksheets. i have an athlon64 3200
with a gb of ram. -- Gary "Bob Phillips" wrote in message ... BTW, it took about 1 sec on my machine, what do you have? -- HTH Bob Phillips "Gary Keramidas" wrote in message ... hi norman: i added your code. it ran ok, still took a minute and a half. each formula has 12 links to other files, so they are quite long. they're about 738 characters long to begin with. thanks for the info and code - - Gary "Norman Jones" wrote in message ... Hi Gary, Independently of your code, I would suggest that you expand the ScreenUpdate wrapper to include calculation and (while you are at it) also include pagebreak displays. somehing like: Dim CalcMode As Long Dim PgBreakMode As Boolean With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet PgBreakMode = .DisplayPageBreaks .DisplayPageBreaks = False End With ' Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With ActiveSheet.DisplayPageBreaks = PgBreakMode End Sub --- Regards, Norman |
code question
Gary,
So where does a minute come from? Bob "Gary Keramidas" wrote in message ... it takes about a second here with blank worksheets. i have an athlon64 3200 with a gb of ram. -- Gary "Bob Phillips" wrote in message ... BTW, it took about 1 sec on my machine, what do you have? -- HTH Bob Phillips |
code question
bob:
this appears to work faster, it just adds a range to the existing formula and all i need is one cell. i'll change the your code back so it just a single cell reference and let you know how it goes. [Manager.xls]Branch'!$D$43:$D$54 instead of [Manager.xls]Branch'!$D$43 thanks for looking at this for me. -- Gary "Bob Phillips" wrote in message ... Looked at it some more and the loop is also unnecessary. Just use an Excel formula that sums Sub Add_User() With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet PgBreakMode = .DisplayPageBreaks .DisplayPageBreaks = False End With cnt = 1 mName = 4 uName = "Bob" ' enter new person's name (workbook name) Rnum = 39 + mName ' starting row for data in new sheet (43) With Sheets(mName) .Unprotect .Range("C5").Formula = .Range("C5").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C6").Formula = .Range("C6").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D5").Formula = .Range("D5").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D6").Formula = .Range("D6").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E5").Formula = .Range("E5").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E6").Formula = .Range("E6").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C7").Formula = .Range("c7").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C8").Formula = .Range("c8").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D7").Formula = .Range("D7").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D8").Formula = .Range("D8").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E7").Formula = .Range("E7").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E8").Formula = .Range("E8").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C9").Formula = .Range("c9").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C10").Formula = .Range("c10").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D9").Formula = .Range("D9").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D10").Formula = .Range("D10").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E9").Formula = .Range("E9").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E10").Formula = .Range("E10").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C11").Formula = .Range("C11").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C12").Formula = .Range("C12").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D11").Formula = .Range("D11").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D12").Formula = .Range("D12").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E11").Formula = .Range("E11").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$H$" & Rnum + 11 & ")" .Range("E12").Formula = .Range("E12").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" mName = mName + 1 Rnum = 39 + mName .Protect End With With Application CalcMode = .Calculation .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With MsgBox "Done" End Sub -- HTH Bob Phillips "Gary Keramidas" wrote in message ... ok Option Explicit Dim uName As String Dim rNum As Integer Dim mName As Integer Dim cnt As Integer Dim Counter As Integer Sub Add_User() Application.ScreenUpdating = False Counter = 1 cnt = 1 mName = 4 uName = "New Name Here" ' enter new person's name (workbook name) rNum = 39 + mName ' starting row for data in new sheet (43) Do For Counter = 1 To 12 Step 1 Sheets(mName).Select With Sheets(mName) ActiveSheet.Unprotect .Range("C5").Formula = .Range("c5").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C6").Formula = .Range("c6").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D5").Formula = .Range("D5").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D6").Formula = .Range("D6").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E5").Formula = .Range("E5").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E6").Formula = .Range("E6").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C7").Formula = .Range("c7").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C8").Formula = .Range("c8").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D7").Formula = .Range("D7").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D8").Formula = .Range("D8").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E7").Formula = .Range("E7").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E8").Formula = .Range("E8").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C9").Formula = .Range("c9").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C10").Formula = .Range("c10").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D9").Formula = .Range("D9").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D10").Formula = .Range("D10").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E9").Formula = .Range("E9").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E10").Formula = .Range("E10").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C11").Formula = .Range("c11").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C12").Formula = .Range("c12").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D11").Formula = .Range("D11").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D12").Formula = .Range("D12").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E11").Formula = .Range("E11").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E12").Formula = .Range("E12").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" End With mName = mName + 1 rNum = 39 + mName ActiveSheet.Protect Next Counter Exit Do Loop Application.ScreenUpdating = True MsgBox "Done" End Sub -- Gary "Tim Williams" <saxifrax@pacbell*dot*net wrote in message ... Don't post attachments: no-one will read them. Paste the code into the message body - work on line breaks as required. Tim "Gary Keramidas" wrote in message ... ok, here it is. i used an attachment to help with line breaks -- Gary "Gareth" wrote in message ... Go ahead and post it. I'm sure someone will be able to take it on. Gary Keramidas wrote: i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks |
code question
i guess because each formula is over 700 characters long to begin with. my
original routine must be slowed by that, i guess -- Gary "Bob Phillips" wrote in message ... Gary, So where does a minute come from? Bob "Gary Keramidas" wrote in message ... it takes about a second here with blank worksheets. i have an athlon64 3200 with a gb of ram. -- Gary "Bob Phillips" wrote in message ... BTW, it took about 1 sec on my machine, what do you have? -- HTH Bob Phillips |
code question
Did my loopless code help at all?
-- HTH Bob Phillips "Gary Keramidas" wrote in message ... i guess because each formula is over 700 characters long to begin with. my original routine must be slowed by that, i guess -- Gary "Bob Phillips" wrote in message ... Gary, So where does a minute come from? Bob "Gary Keramidas" wrote in message ... it takes about a second here with blank worksheets. i have an athlon64 3200 with a gb of ram. -- Gary "Bob Phillips" wrote in message ... BTW, it took about 1 sec on my machine, what do you have? -- HTH Bob Phillips |
code question
ok, i made the changes to the formula and it works. but i noticed, since you
removed my loop, it only made changes to the january sheet. i need the loop to modify feb through dec. too. after adding the loop back, it still took a minute and a half to complete. there are actually 24 formulas on 12 sheets that need to be modified. each sheet appears to take about 8 seconds to modify. thanks for your time and work -- Gary "Bob Phillips" wrote in message ... Looked at it some more and the loop is also unnecessary. Just use an Excel formula that sums Sub Add_User() With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet PgBreakMode = .DisplayPageBreaks .DisplayPageBreaks = False End With cnt = 1 mName = 4 uName = "Bob" ' enter new person's name (workbook name) Rnum = 39 + mName ' starting row for data in new sheet (43) With Sheets(mName) .Unprotect .Range("C5").Formula = .Range("C5").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C6").Formula = .Range("C6").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D5").Formula = .Range("D5").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D6").Formula = .Range("D6").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E5").Formula = .Range("E5").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E6").Formula = .Range("E6").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C7").Formula = .Range("c7").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C8").Formula = .Range("c8").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D7").Formula = .Range("D7").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D8").Formula = .Range("D8").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E7").Formula = .Range("E7").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E8").Formula = .Range("E8").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C9").Formula = .Range("c9").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C10").Formula = .Range("c10").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D9").Formula = .Range("D9").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D10").Formula = .Range("D10").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E9").Formula = .Range("E9").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E10").Formula = .Range("E10").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C11").Formula = .Range("C11").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C12").Formula = .Range("C12").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D11").Formula = .Range("D11").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D12").Formula = .Range("D12").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E11").Formula = .Range("E11").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$H$" & Rnum + 11 & ")" .Range("E12").Formula = .Range("E12").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" mName = mName + 1 Rnum = 39 + mName .Protect End With With Application CalcMode = .Calculation .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With MsgBox "Done" End Sub -- HTH Bob Phillips "Gary Keramidas" wrote in message ... ok Option Explicit Dim uName As String Dim rNum As Integer Dim mName As Integer Dim cnt As Integer Dim Counter As Integer Sub Add_User() Application.ScreenUpdating = False Counter = 1 cnt = 1 mName = 4 uName = "New Name Here" ' enter new person's name (workbook name) rNum = 39 + mName ' starting row for data in new sheet (43) Do For Counter = 1 To 12 Step 1 Sheets(mName).Select With Sheets(mName) ActiveSheet.Unprotect .Range("C5").Formula = .Range("c5").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C6").Formula = .Range("c6").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D5").Formula = .Range("D5").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D6").Formula = .Range("D6").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E5").Formula = .Range("E5").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E6").Formula = .Range("E6").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C7").Formula = .Range("c7").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C8").Formula = .Range("c8").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D7").Formula = .Range("D7").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D8").Formula = .Range("D8").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E7").Formula = .Range("E7").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E8").Formula = .Range("E8").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C9").Formula = .Range("c9").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C10").Formula = .Range("c10").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D9").Formula = .Range("D9").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D10").Formula = .Range("D10").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E9").Formula = .Range("E9").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E10").Formula = .Range("E10").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C11").Formula = .Range("c11").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C12").Formula = .Range("c12").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D11").Formula = .Range("D11").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D12").Formula = .Range("D12").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E11").Formula = .Range("E11").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E12").Formula = .Range("E12").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" End With mName = mName + 1 rNum = 39 + mName ActiveSheet.Protect Next Counter Exit Do Loop Application.ScreenUpdating = True MsgBox "Done" End Sub -- Gary "Tim Williams" <saxifrax@pacbell*dot*net wrote in message ... Don't post attachments: no-one will read them. Paste the code into the message body - work on line breaks as required. Tim "Gary Keramidas" wrote in message ... ok, here it is. i used an attachment to help with line breaks -- Gary "Gareth" wrote in message ... Go ahead and post it. I'm sure someone will be able to take it on. Gary Keramidas wrote: i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks |
code question
Hi Gary,
Given that you run this procedure only a couple of times per year and that, with the 'bad' version taking 1.5 minutes to run, this equtes to 3 minutes per year, are you really that bothered - apart of course from the perspectives of efficient code practice and personal satisfaction? --- Regards, Norman "Gary Keramidas" wrote in message ... ok, i made the changes to the formula and it works. but i noticed, since you removed my loop, it only made changes to the january sheet. i need the loop to modify feb through dec. too. after adding the loop back, it still took a minute and a half to complete. there are actually 24 formulas on 12 sheets that need to be modified. each sheet appears to take about 8 seconds to modify. thanks for your time and work -- Gary "Bob Phillips" wrote in message ... Looked at it some more and the loop is also unnecessary. Just use an Excel formula that sums Sub Add_User() With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet PgBreakMode = .DisplayPageBreaks .DisplayPageBreaks = False End With cnt = 1 mName = 4 uName = "Bob" ' enter new person's name (workbook name) Rnum = 39 + mName ' starting row for data in new sheet (43) With Sheets(mName) .Unprotect .Range("C5").Formula = .Range("C5").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C6").Formula = .Range("C6").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D5").Formula = .Range("D5").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D6").Formula = .Range("D6").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E5").Formula = .Range("E5").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E6").Formula = .Range("E6").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C7").Formula = .Range("c7").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C8").Formula = .Range("c8").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D7").Formula = .Range("D7").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D8").Formula = .Range("D8").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E7").Formula = .Range("E7").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E8").Formula = .Range("E8").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C9").Formula = .Range("c9").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C10").Formula = .Range("c10").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D9").Formula = .Range("D9").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D10").Formula = .Range("D10").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E9").Formula = .Range("E9").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11 & ")" .Range("E10").Formula = .Range("E10").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" Rnum = Rnum + 14 .Range("C11").Formula = .Range("C11").Formula & _ "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11 & ")" .Range("C12").Formula = .Range("C12").Formula & _ "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11 & ")" .Range("D11").Formula = .Range("D11").Formula & _ "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11 & ")" .Range("D12").Formula = .Range("D12").Formula & _ "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11 & ")" .Range("E11").Formula = .Range("E11").Formula & _ "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$H$" & Rnum + 11 & ")" .Range("E12").Formula = .Range("E12").Formula & _ "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11 & ")" mName = mName + 1 Rnum = 39 + mName .Protect End With With Application CalcMode = .Calculation .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With MsgBox "Done" End Sub -- HTH Bob Phillips "Gary Keramidas" wrote in message ... ok Option Explicit Dim uName As String Dim rNum As Integer Dim mName As Integer Dim cnt As Integer Dim Counter As Integer Sub Add_User() Application.ScreenUpdating = False Counter = 1 cnt = 1 mName = 4 uName = "New Name Here" ' enter new person's name (workbook name) rNum = 39 + mName ' starting row for data in new sheet (43) Do For Counter = 1 To 12 Step 1 Sheets(mName).Select With Sheets(mName) ActiveSheet.Unprotect .Range("C5").Formula = .Range("c5").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C6").Formula = .Range("c6").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D5").Formula = .Range("D5").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D6").Formula = .Range("D6").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E5").Formula = .Range("E5").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E6").Formula = .Range("E6").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C7").Formula = .Range("c7").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C8").Formula = .Range("c8").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D7").Formula = .Range("D7").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D8").Formula = .Range("D8").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E7").Formula = .Range("E7").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E8").Formula = .Range("E8").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C9").Formula = .Range("c9").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C10").Formula = .Range("c10").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D9").Formula = .Range("D9").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D10").Formula = .Range("D10").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E9").Formula = .Range("E9").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E10").Formula = .Range("E10").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" rNum = rNum + 14 .Range("C11").Formula = .Range("c11").Formula & "+[" & uName & ".xls]Branch!$C$" & rNum & "" .Range("C12").Formula = .Range("c12").Formula & "+[" & uName & ".xls]Branch!$F$" & rNum & "" .Range("D11").Formula = .Range("D11").Formula & "+[" & uName & ".xls]Branch!$D$" & rNum & "" .Range("D12").Formula = .Range("D12").Formula & "+[" & uName & ".xls]Branch!$G$" & rNum & "" .Range("E11").Formula = .Range("E11").Formula & "+[" & uName & ".xls]Branch!$H$" & rNum & "" .Range("E12").Formula = .Range("E12").Formula & "+[" & uName & ".xls]Branch!$I$" & rNum & "" End With mName = mName + 1 rNum = 39 + mName ActiveSheet.Protect Next Counter Exit Do Loop Application.ScreenUpdating = True MsgBox "Done" End Sub -- Gary "Tim Williams" <saxifrax@pacbell*dot*net wrote in message ... Don't post attachments: no-one will read them. Paste the code into the message body - work on line breaks as required. Tim "Gary Keramidas" wrote in message ... ok, here it is. i used an attachment to help with line breaks -- Gary "Gareth" wrote in message ... Go ahead and post it. I'm sure someone will be able to take it on. Gary Keramidas wrote: i have a routine i wrote to add a range to an exiting formula. it has 24 formulas on 12 sheets hat need to be updated. the linked cells are an external workbook with a sheet called branch. the routine takes about a minute and a half to execute, but it will only execute when a new person is added, couple times a year. it is about 50 lines including the dims. is it appropriate to post it so i can ask someone if there is a more efficient way? thanks |
| All times are GMT +1. The time now is 02:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com