Dynamic Print Range

[ryanyoungsma]

Hi,

I have seen some posts regarding dynamic print ranges, but I have not been able to find quite what I need. So here it goes:

Problem Description:
I have an application where I allow a user to select from a list of "reports" on a form. Each report is on the same worksheet, so say 5 reports on the same worksheet. The reason for this is that for each worksheet we have 22 variations of the worksheet.

So once the user selects the reports(s) that they want we run a data retrieval method. To finalize the resulting workbook, I need to remove the reports that the user did not select from each worksheet. This is where things break.


What I have done:
- I have tried setting the print range on each worksheet in the main file, but once I delete the reports not needed, the print range disappears.

Here is the code for the sub that cleans up the worksheet as it is processed:

Sub runReportCleanUp(ws As Worksheet)
'/------------------------------------------/'
'   Delete reports not selected             '
'        OR Paste Special the data          '
'   @param: ws as Worksheet, Current WS Obj
'/------------------------------------------/'
Dim counter As Integer      'report counter
Dim printRange1 As String, printRange2 As String, printRange3 As String, printRange4 As String, printRange5 As String
Dim wsValue As String

'Update Status info
With UserForm1
    .prgStatus = "(Cleaning up report page...)"
End With
DoEvents

'Activate the current worksheet
ws.Select

counter = 0
wsValue = ws.Range("A2").Value
    
'ws.PageSetup.PrintArea = Range(printRange1).Address & ", " & Range(printRange2).Address & ", " & _
                          Range(printRange3).Address & ", " & Range(printRange4).Address & ", " & _
                          Range(printRange5).Address

On Error Resume Next
    'Paid Loss Development
    If Not UserForm1.rpt_pld Then
        Range(wsValue & "_PLD").Select                   'Specific range on WS for specific report
        selection.EntireColumn.Delete           'Deletes Columns from selection
        ActiveWorkbook.names(wsValue & "_PLD").Delete    'Delete Named Range
    Else
        Range(wsValue & "_PLD").Select
        selection.Copy
        selection.PasteSpecial Paste:=xlValues
        counter = counter + 1                   'Increment counter
    End If
                    
    'Part 1
    If Not UserForm1.rpt_pt1 Then
        Range(wsValue & "_PT1").Select
        selection.EntireColumn.Delete           'Deletes Columns from selection
        ActiveWorkbook.names(wsValue & "_PT1").Delete    'Delete Named Range
    Else
        Range(wsValue & "_PT1").Select
        selection.Copy
        selection.PasteSpecial Paste:=xlValues
        counter = counter + 1                   'Increment counter
    End If
    
    'Incurred Loss Development
    If Not UserForm1.rpt_ild Then
        Range(wsValue & "_ILD").Select
        selection.EntireColumn.Delete
        ActiveWorkbook.names(wsValue & "_ILD").Delete    'Delete Named Range
    Else
        Range(wsValue & "_ILD").Select
        selection.Copy
        selection.PasteSpecial Paste:=xlValues
        counter = counter + 1                   'Increment counter
    End If
    
    'Loss Reserves
    If Not UserForm1.rpt_lrs Then
        Range(wsValue & "_LRS").Select
        selection.EntireColumn.Delete
        ActiveWorkbook.names(wsValue & "_LRS").Delete    'Delete Named Range
    Else
        Range(wsValue & "_LRS").Select
        selection.Copy
        selection.PasteSpecial Paste:=xlValues
        counter = counter + 1                   'Increment counter
    End If
    
    'Parts 2- 6
    If Not UserForm1.rpt_p26 Then
        Range(wsValue & "_P26").Select
        selection.EntireColumn.Delete
        ActiveWorkbook.names(wsValue & "_P26").Delete    'Delete Named Range
    Else
        Range(wsValue & "_P26").Select
        selection.Copy
        selection.PasteSpecial Paste:=xlValues
        counter = counter + 1                   'Increment counter
    End If
    
'Clean up selected range
Range("A1").Select
    
    With ws.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    
    On Error GoTo 0
    With ws.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = counter
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With


End Sub

Other Information:

• Each report has a defined name for it on each worksheet. This is what I use to delete the report.
• the worksheet object is passed to this sub from another process.

Is there a way to add to the print range as I go through each report? My goal is to make sure each report prints in an acceptable manner. Primarily each report should print on its own page.

If anyone has any thoughts it would be appreciated. I am definitely stumped on this one.


Thank you,
Ryan

btw - My first post here so hopefully I have provided enough information.

 

[Datsmart]

Hi ryanyoungsma,
Welcome to the board.

Without knowing more about your data, it is hard to give you better advice.
Sounds like you are making it very difficult to change between needed report pages.

I would suggest looking at Advanced Filtering to do what you are after.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html
AdvancedFilter Demo
http://www.contextures.com/xlVideos04.html
Another good link with sample VBA code here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet. The original data remains unchanged and the data "Filtered" to another page can then be printed as desired. No need to change Print Settings.

 

[ryanyoungsma]

Hi John,

I am not sure the items you posted take care of my situation. Maybe a little more information would help.

- I have a workbook that has say 22 worksheets in it. This is the main application workbook and it is used as a template engine in a way.
- Each worksheet has 5 "templated" reports on it.
- the user, uses our add-in to retrieve new data via a web service.
- I then process the new data and create a new workbook of only the reports they selected. So each worksheet contains 5 reports, the user may have only selected 2 of them. So I delete the reports the user does not want.

All of that is handled... except for the fact that I now need to make the reports in the New workbook print nicely. So I process each sheet, code in my original post. I just can not figure out the best way to set the print area.