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
|