Sum Across Multiple Sheets

alj1977

New Member
Joined
Oct 11, 2019
Messages
1
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
Code:
Sub t()
Sheet1.Range("A2").Formula = "=SUM(Sheet1:Sheet" & Sheets.Count & "!Q2)"
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top