Loops...

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>