+ Reply to Thread
Results 1 to 4 of 4

Subtotals with variable total/sum location

  1. #1
    Tom Ogilvy
    Guest

    RE: Subtotals with variable total/sum location

    On the limited information provided, that sounds attractive:

    It is actually select case

    Select Case sh.name
    Case "Sheet1"

    Case "Sheet2"

    Case Else

    end Select

    --
    Regards,
    Tom Ogilvy


    "Dawn Bjork Buzbee" wrote:

    > I am editing a macro that was working until the scope was expanded:
    >
    > 1) selects a variable range for sorting and subtotals
    > 2) sorts on 2 key fields (columns are same for all worksheets)
    > 3) creates nested subtotals (the GroupBy columns are consistent); the total
    > column varies with each worksheet (about 10)
    >
    > I want to easily set the column position (Array) for the totals. Is Do Case
    > the best option when there are at least 10 different variations?
    >
    > Any examples would be very appreciated!
    > --
    > Dawn Bjork Buzbee


  2. #2
    Dawn Bjork Buzbee
    Guest

    Subtotals with variable total/sum location

    I am editing a macro that was working until the scope was expanded:

    1) selects a variable range for sorting and subtotals
    2) sorts on 2 key fields (columns are same for all worksheets)
    3) creates nested subtotals (the GroupBy columns are consistent); the total
    column varies with each worksheet (about 10)

    I want to easily set the column position (Array) for the totals. Is Do Case
    the best option when there are at least 10 different variations?

    Any examples would be very appreciated!
    --
    Dawn Bjork Buzbee

  3. #3
    Dawn Bjork Buzbee
    Guest

    RE: Subtotals with variable total/sum location

    Thanks, Tom for the reminder on select case.

    Initially, the workbook will contain about 10 worksheets for different
    services. I already have a routine that will separate the different
    worksheets into their own files.

    Here are the lines I am working with for the active worksheet. This code was
    created by recording, and so, it's not that clean but it works for subtotals:

    Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(21), _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(21), _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True

    The variable that changes would be the value for =Array( ), as this column
    number can vary depending on the active worksheet. I want to create a simple
    input for the user so that they can type in the code for the service they are
    working on which will input the variable and process the subtotals.

    I know this is basic Excel programming and I've done it before but I don't
    have easy access to most of my references, and so, I was looking for some
    examples or expert help.

    Thanks again.
    --
    Dawn Bjork Buzbee


    "Tom Ogilvy" wrote:

    > On the limited information provided, that sounds attractive:
    >
    > It is actually select case
    >
    > Select Case sh.name
    > Case "Sheet1"
    >
    > Case "Sheet2"
    >
    > Case Else
    >
    > end Select
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Dawn Bjork Buzbee" wrote:
    >
    > > I am editing a macro that was working until the scope was expanded:
    > >
    > > 1) selects a variable range for sorting and subtotals
    > > 2) sorts on 2 key fields (columns are same for all worksheets)
    > > 3) creates nested subtotals (the GroupBy columns are consistent); the total
    > > column varies with each worksheet (about 10)
    > >
    > > I want to easily set the column position (Array) for the totals. Is Do Case
    > > the best option when there are at least 10 different variations?
    > >
    > > Any examples would be very appreciated!
    > > --
    > > Dawn Bjork Buzbee


  4. #4
    Tom Ogilvy
    Guest

    RE: Subtotals with variable total/sum location

    Dim sh as Worksheet, v as Variant
    for each sh in thisworkbook.Worksheets
    select Case sh.name
    Case "Sheet1"
    v = Array(21,22)
    Case "Sheet2"
    v = Array(20)
    Case "Sheet3"
    v= Array(15,17,23)
    Case Else
    v= Array(21)
    End Select
    sh.Select
    range("A1").currentRegion.Select
    Selection.Subtotal GroupBy:=10, _
    Function:=xlSum, _
    TotalList:=v, _
    Replace:=True, _
    PageBreaks:=False, _
    SummaryBelowData:=True
    range("A1").currentRegion.Select
    Selection.Subtotal GroupBy:=11, _
    Function:=xlSum, _
    TotalList:=v, _
    Replace:=False, _
    PageBreaks:=False, _
    SummaryBelowData:=True
    ' more code

    Next sh

    --
    Regards,
    Tom Ogilvy







    "Dawn Bjork Buzbee" wrote:

    > Thanks, Tom for the reminder on select case.
    >
    > Initially, the workbook will contain about 10 worksheets for different
    > services. I already have a routine that will separate the different
    > worksheets into their own files.
    >
    > Here are the lines I am working with for the active worksheet. This code was
    > created by recording, and so, it's not that clean but it works for subtotals:
    >
    > Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(21), _
    > Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    > Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(21), _
    > Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    >
    > The variable that changes would be the value for =Array( ), as this column
    > number can vary depending on the active worksheet. I want to create a simple
    > input for the user so that they can type in the code for the service they are
    > working on which will input the variable and process the subtotals.
    >
    > I know this is basic Excel programming and I've done it before but I don't
    > have easy access to most of my references, and so, I was looking for some
    > examples or expert help.
    >
    > Thanks again.
    > --
    > Dawn Bjork Buzbee
    >
    >
    > "Tom Ogilvy" wrote:
    >
    > > On the limited information provided, that sounds attractive:
    > >
    > > It is actually select case
    > >
    > > Select Case sh.name
    > > Case "Sheet1"
    > >
    > > Case "Sheet2"
    > >
    > > Case Else
    > >
    > > end Select
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "Dawn Bjork Buzbee" wrote:
    > >
    > > > I am editing a macro that was working until the scope was expanded:
    > > >
    > > > 1) selects a variable range for sorting and subtotals
    > > > 2) sorts on 2 key fields (columns are same for all worksheets)
    > > > 3) creates nested subtotals (the GroupBy columns are consistent); the total
    > > > column varies with each worksheet (about 10)
    > > >
    > > > I want to easily set the column position (Array) for the totals. Is Do Case
    > > > the best option when there are at least 10 different variations?
    > > >
    > > > Any examples would be very appreciated!
    > > > --
    > > > Dawn Bjork Buzbee


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1