Vba loop for many sheets

Question

I have vba code that adds calculations for 4 sheets. I want a loop that will calculate hundreds of sheet without adding sheetname again & again in code.

Private Sub CommandButton2_Click()

   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

Thank you for your help.

How will it determine which tabs to run in the loop? Are you saying you want it to run this code against all tabs in a workbook? — DiegoAndresJAY
– DiegoAndresJAY, Commented Aug 1, 2015 at 19:37
I believe that Don's answer below will work. Please test it out and let us know. Thanks. — DiegoAndresJAY
– DiegoAndresJAY, Commented Aug 2, 2015 at 1:22
I found some error like: "Subscript out of range" i think i do some mistake for adding "final calculations" in Second routine or may be an error in final routine. did you know how to modify it. — FaizanRoshan
– FaizanRoshan, Commented Aug 2, 2015 at 2:37
Make sure you change "NameOfTotalsSheet" to match the name of the tab you are storing all of your output. I'm not sure what you mean about the mistake related to "final calculations". — DiegoAndresJAY
– DiegoAndresJAY, Commented Aug 2, 2015 at 2:40

DiegoAndresJAY · Accepted Answer · 2015-08-02 03:28:33Z

3

First, create a routine that does what you want for one Sheet using parameters:

Private Sub AddTableFormulas(ByVal sName As String, ByVal nOffset As Long)
    With Sheets("NameOfTotalsSheet")
        .Range("A5").Offset(nOffset).Value = sName
        .Range("B5").Offset(nOffset).Value = getOrdinal(nOffset + 1)
        .Range("I5").Offset(nOffset).Formula = "=AVERAGE('" & "adt" & sName & "'!$P:$P)"

        'etc

    End With
End Sub

Private Function getOrdinal(ByVal nNumber As Long) As String
    Dim sNumber As String 

    sNumber = nNumber
    Select Case Right(sNumber,1)
        Case "1"
            getOrdinal = nNumber & "st"
        Case "2"
            getOrdinal = nNumber & "nd"
        Case "3"
            getOrdinal = nNumber & "rd"
        Case Else
            getOrdinal = nNumber & "th"
    End Select

End Function

Second, write a routine that does it for all Sheets that match your criteria:

Public Sub AddAllFormulas()
    Dim oSheet As Excel.Worksheet
    Dim sName As String
    Dim nOffset As Long

    For Each oSheet In Worksheets
        If Left(oSheet.Name, 3) = "adt" Then
            sName = Right(oSheet.Name, Len(oSheet.Name) - 3)
            AddTableFormulas sName, nOffset
            nOffset = nOffset + 1
        End If
    Next 'oSheet

    'add final calculations here. use offset to determine location

End Sub

Finally, call this routine from your button:

Private Sub CommandButton2_Click()
    AddAllFormulas
End Sub

edited Aug 2, 2015 at 3:28

DiegoAndresJAY

7064 silver badges11 bronze badges

answered Aug 1, 2015 at 19:38

Don Jewett

2,00715 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Don Jewett Over a year ago

"NameOfTotalsSheet" is hardcoded because I believe the OP wants all formulas on the same sheet.

DiegoAndresJAY Over a year ago

Then I would say the row on that sheet should increment with each iteration. Otherwise, you are just overwriting the data with each new sheet.

Don Jewett Over a year ago

No problem. Thanks for correcting the dots after the with. I missed that in my edit.

FaizanRoshan Over a year ago

Thank for help, but i found some error, run time error, may be i have mistake for calling all routine or add final calculation in code. please help me to modify it.

Collectives™ on Stack Overflow

Vba loop for many sheets

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related