Change VBA code to separate outputs

[Jeremy H]

Hello,
I am new to forum, so going to give this problem a shot.

I have a workbook(Excel 2010) that outputs files into PDF's for Team, Sub-Region, and Regions. Without changing the formula's in the output files, there is a VBA code that separates each Sub-Region and Region, here:

' -- Procedure.

    LogStatus "Region"
    [rB1.MaxTeamIndex].Calculate
    ' Add two extra rows for final Sub-Region Total and Region Total.
    Set lobRegion = CreateNewTable([rB5.RegionTgt], "tB5.Region", [rB1.MaxTeamIndex] + 2, gstrBASIS_THEME)


    StaticCheck wksBasis5

So, there are 9 Teams with 6 different SubRegions that show total sales for each. The final out put looks like this:


You can see on the top that Amers AM West F2F is a Team, then the Central&West is a SubRegion, and finally the bottom line "Americas" is the Region. The totals on the right have been excluded.
What I want to see is if in the VBA code I can separate out Amers Academia(Team Name) and Z Academia(SubRegion) from Americas(Region) and have it show up on its own below the "Americas" totals, with its own totals.

So, something like this:


Not sure how to ask this exactly, but giving it a shot to start.

 

[Jeremy H]

Update, found this VBA code for the basis files for this output, which might be useful...

Loop over Regions; use Regions as listed in tB1.Regions.
    arrRegions = wksBasis1.ListObjects("tB1.Regions").ListColumns("Region").DataBodyRange.Value2
    For iRow = LBound(arrRegions, 1) To UBound(arrRegions, 1)
        strRegion = CStr(arrRegions(iRow, 1))

        LogStatus "[" & strRegion & "]"

        ' Set the Region name and make sure other control values are correct.
        [rF2.Region].Value2 = strRegion
        [rF5.GgoRegion].Value2 = vbNullString
        Application.Calculate

        ' Adjust filter tables where the number of rows changes for a Region.
        AdjustTableRows lobRegions, [rF5.RegionCount].Value2
        Application.Calculate

        ' Adjust number of rows in Regional Summary Dash.
        AdjustPrintArea [rO3.TopLeft], 3, [rF5.RegionCount].Value2 + 1, 4

        ' Update Region outputs.
        ProductiseSheet wksOutput3, True

        ' Remember sheet names.
        strOutputList = wksOutput3.Name & gstrLIST_SEPARATOR & wksOutput4.Name

        ' Loop over Sub-Regions; use Sub-Region Index as listed in tB1.SubRegions.
        strRegionLabel = [rF2.RegionLabel].Value2
        arrSubRegions = _
                wksBasis1.ListObjects("tB1.SubRegions").ListColumns("SubRegionIndex").DataBodyRange.Value2
        For iRow2 = LBound(arrSubRegions, 1) To UBound(arrSubRegions, 1)
            strSubRegionLabel = CStr(arrSubRegions(iRow2, 1))
            If Left$(strSubRegionLabel, 3) = strRegionLabel Then
                ' This is a Sub-Region in this Region, so we can create a page for it.
                strSubRegion = Right$(strSubRegionLabel, Len(strSubRegionLabel) - 4)
                LogStatus "[" & strRegion & "].[" & strSubRegion & "]"

                [rF2.SubRegion].Value2 = strSubRegion
                Application.Calculate

                ' Copy to new sheet and remember it, then convert to literals.
                wksOutput5.Copy before:=wksOutput5
                Set wksCopy = ActiveSheet
                wksCopy.Name = RANDOMSTRING(rsPrefixSheet)
                strOutputList = strOutputList & gstrLIST_SEPARATOR & wksCopy.Name
                ConvertSheetToLiterals wksCopy

            End If

 

[Jeremy H]

IF you would like a sample file and output I can use some fake name and numbers to try if anyone would like.

 

[Jeremy H]

Here is the layout I was looking for after.

 

[Jeremy H]

So, the SubRegions(Central&West, East, SCS, Unallocated, and X Campaigns) are all under the Region(Americas) and the Academia is separate in the output.

To note: in the input file the Region for Academia is Americas, just like all of the rest of the SubRegions. So, in essence without changing the Region of Americas in Academia, but including it with the Americas output file is the goal, but just separate line items with totals just for that SubRegion/Region combination and the other SubRegions totaled above it separately.