Excel VBA – Perform Formula on Sheet Based on Cell Values of Looped-Through Worksheets

I currently have this code –

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Uptime" Then
   Worksheets("Uptime").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = ws.Name

This loops through all of the sheets and adds the sheetName into Col.A.

What I’m now struggling with is the formula that I want in Col.B. All of my sheets have a calculated value in cell ET40.

I want to take that value, apply formula =(ET40/60)/24 and then place it in the corresponding cell in Col.B on my “Uptime” sheet. I’m struggling with this one.

I’ve tried – Worksheets("Uptime").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Formula = "=(ET40/60)/24" But I think that just tries to look for ET40 on “Uptime” which is empty.

I’m not sure how I get the formula in that line of code to look at the current Worksheet on the loop.

Edit –

Full code written with Rory’s help

Sub Uptime()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Uptime" Then
       With Worksheets("Uptime").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .Value2 = ws.Name
        .Offset(, 1).Formula = "=('" & ws.Name & "'!ET40/60)/24"
       End With
    End If
Next ws


End Sub

Answer

You just need to add in the sheet name:

If ws.Name <> "Uptime" Then
   With Worksheets("Uptime").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
     .Value2 = ws.Name
     .Offset(, 1).Formula = "=('" & ws.name & "'!ET40/60)/24"
   End With

Leave a Reply

Your email address will not be published. Required fields are marked *