VBA for Summing a certain cell on multiple sheets

[Dclarke]

Hello,

I'm trying to figure out the VBA code to sum a certain cell on multiple sheets.

I've made a workbook to keep track of revenue for my job. I made a locked sheet where my team can enter the necessary information and then I added a button that will save this information onto a new tab and then reset the locked sheet for them to enter the next set of information.

The new tabs are named based on the information they enter into a certain cell on the locked tab. The locked tab is always the first tab. Because of this, i have no way of knowing what the new tabs will be named, as they will always be unique. Therefore I cannot just do a standard formula.

The number of tabs will also vary, but it should never exceed 15.

The cell that I would like to add will always be J27. I would like the VBA code to sum cell J27 from the first 15 tabs in the workbook. There may be less than 15, if that affects anything, but I need up to 15.

This will sum on to a summary tab that I have called "Monthly Tracker". I've put a button it for them to click to refresh the formula.

Any help would be appreciated. Sorry if this was confusing.

Thank you

 

[RedBeard]

You can count the number of worksheets using:

ActiveWorkbook.Worksheets.Count

You can then create a loop to go through the sheets one by one adding the value from J27 to your total.

 

[Dclarke]

Conceptually, I understand what you're saying. But today is basically my first day messing around with VBA.

I don't know how to loop into a sum formula. Sorry!

 

[RedBeard]

See if the below makes sense to you:

Private Sub CommandButton1_Click()

Dim i As Long
Dim x As Long

x = 0

For i = 1 To ActiveWorkbook.Worksheets.Count

    x = x + Sheets(i).Range("J27").Value2
    
Next i

Sheet1.Range("A1").Value2 = x

End Sub

 

[Dclarke]

Actually, I realized that my first and last tab are always the same no matter what. So I just did a standard sum formula for the range of worksheets FirstSheet:LastSheetJ27 and it works.

I do appreciate the help though! I was overthinking here