Sum Across Multiple Sheets

[alj1977]

I'm working on a sheet to track inventory. I'd like it to be 'user friendly' and all the associate would have to do is update the inventory quantity each day. The VBA code below automatically creates a 'new sheet' named for the date the file is opened. I do have a Master sheet that needs to keep the on-hand quantity. I'm struggling to sum cell (Q2) from every daily sheet (sheets that exist and the new one created each day) and have that total on the Master without having to adjust the formula regularly. Any help would be appreciated. - Allen

Private Sub Workbook_Open()

'Get Todays Date
Dim TodaysDate As String
TodaysDate = Format(Now(), "mm-dd-yyyy")
'Check Todays Sheet Doesn't Already Exist
Dim SheetExists As Boolean
For sheetnames = Worksheets.Count To 1 Step -1
If Worksheets(sheetnames).Name = TodaysDate Then
SheetExists = True
Exit For
End If
Next
If SheetExists = False Then
Worksheets("09-24-2019").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = TodaysDate
End If

End Sub

 

[JLGWhiz]

I'm struggling to sum cell (Q2) from every daily sheet (sheets that exist and the new one created each day) and have that total on the Master without having to adjust the formula regularly. Any help would be appreciated. - Allen

Maybe you can work this into your existing macro. I am not sure how your master sheet is set up so I will let you figure our how to exempt it from the sheets in the folrmula.

Sub t()
Sheet1.Range("A2").Formula = "=SUM(Sheet1:Sheet" & Sheets.Count & "!Q2)"
End Sub