Loops...
Perfect... Thankyou very much!!
"Bob Phillips" wrote:
Sub TB()
Dim aryIndex, arySite
Dim i As Long
aryIndex = Array("a very long formula!", "a very long formula!", "a very
long formula!")
arySite = Array("CAS", "ADM", "ADMIN")
Application.Calculation = xlCalculationManual
For i = LBound(aryIndex) To UBound(aryIndex)
With Sheets(arySite(i))
.Range("D8").FormulaR1C1 = aryIndex(i)
.Range("D8").Copy
.Range("D8:AC846").PasteSpecial Paste:=xlPasteFormulas
.Calculate
.Range("D8").Copy
.Range("D8:AC846").PasteSpecial Paste:=xlPasteValues
End With
End With
Application.CutCopyMode = False
Application.Calculation = xlCalculationManual
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Willabo" wrote in message
...
Hi,
I have a section of code for updating a TB Workbook and would like to
create
a loop circuling through the constants...
Option Explicit
Const INDEX1 As String = "a very long formula!"
Const INDEX2 As String = "a very long formula!"
Const INDEX3 As String = "a very long formula!"
Const SITE1 As String = "CAS"
Const SITE2 As String = "ADM"
Const SITE3 As String = "ADMIN"
Sub TB()
Application.Calculation = xlCalculationManual
Sheets(SITE1).Select
Range("D8").Select
ActiveCell.FormulaR1C1 = INDEX1
Range("D8").Select
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Calculate
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("D8").Select
Sheets(SITE2).Select
Range("D8").Select
ActiveCell.FormulaR1C1 = INDEX2
Range("D8").Select
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Calculate
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("D8").Select
Sheets(SITE3).Select
Range("D8").Select
ActiveCell.FormulaR1C1 = INDEX3
Range("D8").Select
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Calculate
Selection.Copy
Range("D8:AC846").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("D8").Select
Application.Calculation = xlCalculationManual
End Sub
Nothing i've tried works!!
Thanks
Will
|