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