Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Add SubTotals To Data Table Based On Date

  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12

    Add SubTotals To Data Table Based On Date

    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
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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

    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.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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

  4. #4
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    Here is a tutorial to get you started on Grouping

    MS Excel Functions GROUP and UNGROUP in Excel Tutorial

  5. #5
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    Thank you

  6. #6
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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
    Attached Files Attached Files

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Add this in the Userform

    Code:
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    Thanks
    I placed the code in the userform and gives error- each sheet was protect with 'abc"
    Code:
    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"
    Code:
    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

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    If each sheet was protected with abc, try unprotecting each sheet with abc manually.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    Thanks
    Yes successfully manually unprotected with "abc"....still gives error

    wks.unprotect "abc" is highlighted yellow
    Attached Files Attached Files

Similar Threads

  1. Replies: 3
    Last Post: 07-25-2013, 01:35 PM
  2. Conditional Insert Query Based on Data in Target Table
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 06-23-2013, 04:24 PM
  3. Calculating Subtotals Within Groups of Data Using UNION
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 1
    Last Post: 05-24-2013, 11:54 AM
  4. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  5. automatic Add date in cells
    By Ryan_Bernal in forum Excel Help
    Replies: 1
    Last Post: 01-23-2013, 02:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •