Change vba code for unlimited next loop

[Closed]
Report
Posts
4
Registration date
Friday July 31, 2015
Status
Member
Last seen
August 1, 2015
-
Hi, i have vba code that calculate 3 sheet data and paste in dashboard sheet with different row, now what i need to update next loop for calculation unlimited sheets and paste in row as first 3 done.
Here is code:

Private Sub CommandButton2_Click()
'
Macro1 Macro
'
Dim TabNames As Variant, Ordinals As Variant
TabNames = Array("4-16 - 4-22", "4-23 - 4-29", "4-30 - 5-6")
Ordinals = Array("1st", "2nd", "3rd")

For i = 0 To UBound(TabNames, 1)
Range("A5").Offset(i).Value = TabNames(i)
Range("B5").Offset(i).Value = Ordinals(i)
Range("I5").Offset(i).Formula = "=AVERAGE('" & "adt" & TabNames(i) & "'!$P:$P)"
Range("J5").Offset(i).Formula = "=COUNTIFS('" & "adt" & TabNames(i) & "'!$P:$P,"">=""&1)"
Range("C5").Offset(i).Formula = "=AVERAGEIFS('" & "adt" & TabNames(i) & "'!$P:$P, '" & "adt" & TabNames(i) & "'!$P:$P, "">301"",'" & "adt" & TabNames(i) & "'!$P:$P, ""<480"")"
Range("D5").Offset(i).Formula = "=COUNTIFS('" & "adt" & TabNames(i) & "'!$P:$P,"">""&301,'" & "adt" & TabNames(i) & "'!$P:$P,""<""&480)"
Range("F5").Offset(i).Formula = "=AVERAGEIFS('" & "adt" & TabNames(i) & "'!$P:$P, '" & "adt" & TabNames(i) & "'!$P:$P, "">=1"",'" & "adt" & TabNames(i) & "'!$P:$P, ""<300"")"
Range("G5").Offset(i).Formula = "=COUNTIFS('" & "adt" & TabNames(i) & "'!$P:$P,"">=""&1,'" & "adt" & TabNames(i) & "'!$P:$P,""<""&300)"
Next

Range("E5:E7,H5:H7,K5:K7").FormulaR1C1 = "=(R2C3-R[0]C[-2])*(R1C4*R[0]C[-1])"
End Sub



Thanks