calculation on every sheet through VBA

[zeno]

I try to make the same calculation on every specifically named sheet of a file. The structure of every sheet is the same, only the length of rows varies.
How can I write this in VBA?
Thank you.

 

[John Davis]

Without knowing which calculations, and where on each sheet, perhaps something like this might give you some ideas.

Sub zeno()
Dim lr As Long
Dim i As Long

For i = 1 To 20

    With Sheets("Sheet" & i)
    
        lr = Cells(Rows.Count, 1).End(xlUp).Row
    

        .Range("A2:A" & lr).Formula = "=YOUR FORMULA"
        .Range("A2:A" & lr).Value = .Range("A2:A" & lr).Value
        
    End With
    
Next i


End Sub

 

[zeno]

Thank you for the code!
I have a quick follow-up:
I have particular names for the sheets, example "Data1", "Process1", etc.
How can I go through these sheets with those particular names?

 

[John Davis]

You're welcome. Maybe:

Sub zeno()
Dim y As Worksheet
Dim lr As Long

For Each y In Sheets(Array("Sheet1", "Sheet2", "Sheet4"))

    y.Activate
    
        lr = Cells(Rows.Count, 2).End(xlUp).Row  

        Range("A2:A" & lr).Formula = "YOUR FORMULA"
        Range("A2:A" & lr).Value = Range("A2:A" & lr).Value

Next y

End Sub

Change the names in the array to the sheets names you want too use.

 

[zeno]

Thank you. This is doing the job!

 

[John Davis]

You're welcome. Glad to help out, and thanks for the feedback.