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