Need to print several named ranges based on the value of a cell

[stormseeker75]

Hello! I've tried everything here and I hope you folks can help me. I have a spreadsheet with several tabs. On each tab, I've defined a named range for the tab. For example, on my Sales Sheet tab, I have the named range called SALES_SHEET. I also have Additional Pages-1 through -8. Sometimes these have data and sometimes they don't.

I have another sheet in my workbook that contains a variable driven by each sheet in my tab, 1 or 0 based on a cell value on the corresponding worksheet. For example, when Additional Pages-4 has a value in a particular cell, it sets my table to show a 1. If there was no value, it would show a 0.

Here's what we are currently up to:

Sub TEST_PRINT()
On Error Resume Next
Application.Dialogs(xlDialogPrinterSetup).Show

Sheets("DO NOT DELETE").Select ' <<<<---Page where data is


If Range("Z2").Value = 1 Then
Sheets("Main Page").PrintOut

ElseIf Range("Z3").Value = 1 Then
Sheets("Additional Page-1").PrintOut


ElseIf Range("Z4").Value = 1 Then
Sheets("Additional Page-2").PrintOut

ElseIf Range("Z5").Value = 1 Then
Sheets("Additional Page-3").PrintOut

ElseIf Range("Z6").Value = 1 Then
Sheets("Additional Page-4").PrintOut

ElseIf Range("Z7").Value = 1 Then
Sheets("Additional Page-5").PrintOut

ElseIf Range("Z8").Value = 1 Then
Sheets("Additional Page-6").PrintOut

ElseIf Range("Z9").Value = 1 Then
Sheets("Additional Page-7").PrintOut

ElseIf Range("Z10").Value = 1 Then
Sheets("Additional Page-8").PrintOut

ElseIf Range("Z11").Value = 1 Then
Sheets("Maintenance Check List").PrintOut
 
ElseIf Range("Z12").Value = 1 Then
Sheets("Chassis Inspection").PrintOut
 
ElseIf Range("Z13").Value = 1 Then
Sheets("Estimate").PrintOut
 
ElseIf Range("Z14").Value = 1 Then
Sheets("Sales Sheet").PrintOut

  End If

End Sub

What I'd like to be able to do is have VBA look at my list and if it has a 1, print the appropriate named range. If it has a 0, move on along to the next one and don't error out.

As an added bonus, I'd like this entire document to be printed as a PDF. I can make things print out to PDF when needed, but I can't make the whole thing dance.

 

[DanteAmor]

That is, if you have 20 ranges, but only 10 of them are numbered 1, then the PDF will have 10 pages. Is that correct?

Try:

Sub print_named_range()
  Dim i As Long, n As Long
  Dim sRng As String, sNam As String
  Dim rng As Range
  Dim wb As Workbook
  Dim sh1 As Worksheet, sh As Worksheet
 
  Application.ScreenUpdating = False
  
  Set sh1 = ThisWorkbook.Sheets("DO NOT DELETE")
  For i = 2 To sh1.Range("Z" & Rows.Count).End(3).Row
    If sh1.Range("Z" & i).Text = "1" Then
      sRng = sh1.Range("Y" & i).Value
      sNam = sh1.Range("X" & i).Value
      ThisWorkbook.Activate
      If Evaluate("IsRef('" & sNam & "'!" & sRng & ")") Then
        n = WorksheetFunction.CountIf(Sheets(sNam).Range(sRng), "<>")
        If n > 0 Then
          If wb Is Nothing Then
            Set wb = Workbooks.Add(xlWBATWorksheet)
            Set sh = wb.Sheets(1)
          Else
            wb.Sheets.Add after:=wb.Sheets(wb.Sheets.Count)
            Set sh = wb.ActiveSheet
          End If
          
          With ThisWorkbook.Sheets(sNam).Range(sRng)
            .PrintOut                   'print sheet
            .Copy sh.Range("A1")        'add named range to pdf
          End With
        End If
      Else
        MsgBox "Not exists: " & sNam & " " & sRng
      End If
    End If
  Next
  
  If Not wb Is Nothing Then
    wb.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "all.pdf", _
      xlQualityStandard, True, False, , , False
    wb.Close False
  End If
  Application.ScreenUpdating = True
End Sub

 

[stormseeker75]

You're a star, sir! I would like the PDF to have each range as it's own data and it's own pages, just all in one PDF file. Sorry for the delay, I didn't work this weekend for a change!

 

[stormseeker75]

Double post, stand by!

 

[stormseeker75]

Okay, we are doing better. It's failing on one of my sheets but I suspect that's my problem not yours. I would like it to print to one workbook while maintaining the page layout I've setup. I'd also LOVE to be able to decide where to save the PDF.

 

[stormseeker75]

Hello! I wanted to follow up on this. I've added a bunch of worksheets with printable ranges. It all works really well. I have one problem. The macro is trying to print each page separately to a PDF and then it's saving the whole thing to the location where the file lives. That's fine and I don't mind it saving there. I would really like the individual named ranges to not print.

 

[stormseeker75]

In this piece of code, I removed the .printout string

With ThisWorkbook.Sheets(sNam).Range(sRng)
.PrintOut 'print sheet
.Copy sh.Range("A1") 'add named range to pdf

That's causing it to ignore the named range on the sheet.

 

[stormseeker75]

At this point, it is now creating one PDF, but it is ignoring my printable areas and printing the whole sheet to the PDF. Or it is possible it's

 

[stormseeker75]

One more follow up here. It appears that this is a problem with the ExportAsFixedFormat. Is there a way to do a .printout using this same loop? It's perfect for my application, I just think we want to skip the ExportAsFixedFormat. I'd like to just force it to printout to a file with all the sheets present.

Thanks for sticking with me, friend!

 

[DanteAmor]

I honestly don't understand what you need and you didn't answer this:

How do you want the PDF generated?
That is, you only want to send the range. But each range on a separate page?
That is, if you have 20 ranges, but only 10 of them are numbered 1, then the PDF will have 10 pages. Is that correct?

Explain with images what you have on each page and what you want in the PDF.

 

[stormseeker75]

I honestly don't understand what you need and you didn't answer this:



Explain with images what you have on each page and what you want in the PDF.

I have a workbook with 30 pages in it.
Each of those pages has a named print range.
For example, the page named Main Page has a printable range called MAIN_PAGE_PA
We have a sheet in the workbook named "DO NOT DELETE" that contains a list with three items:
1. The sheet name (Col Z)
2. The printable range name (Col AA)
3. A formula that creates a "1" or "0" depending on a value in each of the sheets (COL AB)
You were kind enough to write a program that goes through the list of sheets and checks the third column for a "1". If it finds a "1", it is supposed to print the printable range of the corresponding sheet into 1 PDF.
The PDF was supposed to contain all of the printable ranges. I did not want any of the ranges combined, each range on it's own pages.

Currently, the program is printing out each page of the workbook that has been identified in the DO NOT DELETE sheet to a separate file. It is then creating one unified PDF as well, however the unified PDF is not printing the printable ranges and is unusable.

I hope that helps.