PDA

View Full Version : Need Help changing outputs in Excel Workbook



kornholio
12-11-2015, 01:29 PM
I have a workbook that has outputs within various modules in VBA. The output is a PDF of various regions and their respective teams. For each Region there is a output summary, then another page with each region and their amounts broken out by; top 5/bottom 5.

What I want to do is see if the VBA coding can be changed to output 1 of the regions as its own PDF with its own summary page and the other page with its own top 5/bottom 5.

I am attaching the output file, the region that I am trying to separate is called, "Z Academ". I think the output VBA is within this logic in the workbook within building the Regional and Team Reports.14957

Procedure BuildOutputs
' Author Jack
' Date 12/06/2014
' Purpose Master routine for building and exporting the outputs.
'
Public Sub BuildOutputs()


Const strROUTINE As String = "BuildOutputs"


' -- Error Handling, validation, initialisation
On Error GoTo ErrHandler


gstrStep = "OUTPUTS"


LogStatus "Building outputs"


' -- Procedure.


' Update creation date (using latest date of orders).
[rI2.LatestDate].Calculate


' Size all Filter sheet tables and insert formulae.
gstrSubStep = "Initialise Filters"
InitialiseFilters


' Productise Output sheets.
gstrSubStep = "Productise Outputs"
ProductiseOutputs


' Initialise the Exported list.
gstrSubStep = "Initialise Exported List"
PruneTables wksWorking2
CreateNewTable [rW2.ExportedTgt], "tW2.Exported", 1, gstrWORKING_THEME


' Build and export Sales Manager (Team) reports.
gstrSubStep = "Team Reports"
BuildTeamReports


' Build and export Regional Summary reports.
gstrSubStep = "Regional Summary Reports"
BuildRegionalSummaryReports


' Write audit log showing the configuration for this run.
gstrSubStep = "Audit log"
ExportReport "Audit log", "-", "-", "EXCEL", Array(wksConfig2.Name, wksConfig3.Name, wksSelect1.Name, _
wksSelect2.Name)


' Finished building outputs, so convert filters to literals to prevent delays with Calculation when
' program done -- BUT if in Debug mode then follow choice made in C1.Defaults sheet.
If Not gbooDEBUG Or Not [rS1.LeaveFilterFormulaeIntact].Value Then
gstrSubStep = "Convert Filters to Literals"
ConvertFiltersToLiterals
End If


gstrStep = vbNullString
gstrSubStep = vbNullString
LogStatus "", lsCLEAR
'
ErrHandler: ' -- Error handling and Routine termination.
If Err.Number <> 0 Then
' Argument True only for Entry Points. Press F8 to jump to error.
If CentralErrorHandler(mstrMODULE, strROUTINE) Then Stop: Resume
End If
' Routine tidy-up
End Sub

Also, I found this in the VBA, this would have to work for the Region SUmmary Pages. This is the page that I posted from which the "Z Academ" will need to be on its own.

Procedure BuildRegionalSummaryReports
' Author jack
' Date 02/09/2014
' Purpose
'
Private Sub BuildRegionalSummaryReports()


Const strROUTINE As String = "BuildRegionalSummaryReports"
Dim arrRegions As Variant
Dim iRow As Long
Dim strRegion As String
Dim lobRegions As ListObject
Dim iRow2 As Long
Dim arrSubRegions As Variant
Dim strRegionLabel As String
Dim strSubRegionLabel As String
Dim strSubRegion As String
Dim strOutputList As String
Dim wksCopy As Worksheet
Dim iRowGgo As Long
Dim strGgoRegion As String


' -- Error Handling, validation, initialisation
On Error GoTo ErrHandler


Set lobRegions = wksFilter5.ListObjects("tF5.Region")


' -- Procedure.


' 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
Next iRow2


' Finally, add the Help sheet.
strOutputList = strOutputList & gstrLIST_SEPARATOR & wksHelp1.Name


' Export output sheets.
ExportReport [rS1.RegionalSummaryPrefix].Text, strRegion, "Region", "PDF", Split(strOutputList, _
gstrLIST_SEPARATOR)


' Remove temporary sheets.
RemoveTempSheets


' Extra: if the Region is GGO, create split reports for proper regions.
If strRegionLabel = "GGO" Then


For iRowGgo = LBound(arrRegions, 1) To UBound(arrRegions, 1)
strGgoRegion = CStr(arrRegions(iRowGgo, 1))
[rF5.GgoRegion].Value2 = strGgoRegion
Application.Calculate


If strGgoRegion <> "GGO" Then


LogStatus "[" & strRegion & " " & strGgoRegion & "]"


' Only export if there are some unhidden rows.
If [rF5.HidingCount].Value2 < [rF5.RegionCount].Value2 Then
FilterOutput wksOutput3, 6
ExportReport [rS1.RegionalSummaryPrefix].Text, "GGO " & strGgoRegion, _
"Region", "PDF", Array(wksOutput3.Name)
wksOutput3.UsedRange.EntireRow.Hidden = False
End If


End If


Next iRowGgo


End If


Next iRow


'
ErrHandler: ' -- Error handling and Routine termination.
If Err.Number <> 0 Then
' Argument True only for Entry Points. Press F8 to jump to error.
If CentralErrorHandler(mstrMODULE, strROUTINE) Then Stop: Resume
End If
' Routine tidy-up
End Sub