Add SubTotals To Data Table Based On Date

[paul_pearson]

Hi

I recorded a macro to sum up the totals when the commandbutton is selected.It is not the best code so how can I change the code so that it sums up the totals and the totals are in Bold red fonts...When I go to place more entries into the sheets can it keep the total but add the next entries on the first line after the totals..

This will be for the 3 sheets

Example sheet attached

Thanks

Paul

[Excel Fox]

I would recommend you use subtotals instead of individually placing formulas. And a conditional format can be used to give the colors. Check the attachment.

So I've selected the table from Column B to H starting from the first row after the headers. And used the formula RIGHT($B4,5) = "Total", and gave the required conditional format I needed. And then just used a very simple line of code


Sub SubTotalize()

Range("B3:H" & Cells(Rows.Count, 2).End(xlUp).Row).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3, 6), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub



This will easily club your daily totals and sum it up using subtotals.

[paul_pearson]

Thanks Excel Fox

I like the bars you added for hiding columns.How did you create and where can I find as I want to learn this procedure

thanks again

Paul

[alansidman]

Here is a tutorial to get you started on Grouping

MS Excel Functions GROUP and UNGROUP in Excel Tutorial (http://www.tutorialized.com/tutorial/GROUP-and-UNGROUP-in-Excel/23347)

[paul_pearson]

Thank you

[paul_pearson]

When pressing the Daily Total button it gives an error due to the 3 sheets having password protection...password is 1111

It will work if I unprotect the sheet each time.I would prefer to keep the sheets in protected mode so can the code be changed so that the Daily Total button when pressed can still work

Thanks

Paul

[Excel Fox]

Add this in the Userform



Private Sub UserForm_Activate()

Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.Unprotect "1111"
wks.Protect UserInterfaceOnly:=True, Password:="1111"
Next wks

End Sub

[paul_pearson]

Thanks
I placed the code in the userform and gives error- each sheet was protect with 'abc"


Runtime error 1004
The password you supplied is not correct.Verify that the CAPS LOCK
key is off and to be sure to use the correct capitalization


I protected each worksheet with "abc"


Private Sub UserForm_Activate()

Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.Unprotect "abc"
wks.Protect UserInterfaceOnly:=True, Password:="abc"
Next wks

End Sub

[Excel Fox]

If each sheet was protected with abc, try unprotecting each sheet with abc manually.

[paul_pearson]

Thanks
Yes successfully manually unprotected with "abc"....still gives error

wks.unprotect "abc" is highlighted yellow

[Admin]

Hi

Unprotect the sheets with your old password "1111" and protect it again "abc" (manually).