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.

 

[stormseeker75]

I figured it out! Here's the code:

Sub print_named_range_MR_EXCEL_STEVE_VERSION()
  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)
            '.PrintOut                   'print sheet
            .Copy sh        '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

I commented out the .printout line because I didn't want it making individual PDFs. In the line that says With ThisWorkbook.Sheets(sNam), I removed the Range from the end of that. Correspondingly, I removed the range from the .Copy command as well. This resulted in it copying the entire worksheet over. Because it copied the worksheet, it kept all the formatting and most importantly the Named Range for printing.

This is perfect now, thank yoU!!

 

[DanteAmor]

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

In your original post, you said you wanted to print.
But then you said you didn't want to print:

I would really like the individual named ranges to not print.

----- --
The macro sent only the printable area to a sheet:

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

----- --

But if you've already fixed my code to work for you, I'll just add this part:

I'd also LOVE to be able to decide where to save the PDF.

Sub print_named_range_v2()
  Dim i As Long, n As Long
  Dim sRng As String, sNam As String, sPath 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)
            '.PrintOut                   'print sheet
            .Copy sh        '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
    With Application.FileDialog(msoFileDialogFolderPicker)
      .Title = "Select Folder"
      If .Show <> -1 Then Exit Sub
      sPath = .SelectedItems(1)
    End With
  
    wb.ExportAsFixedFormat xlTypePDF, sPath & "\" & "all.pdf", _
      xlQualityStandard, True, False, , , False
    wb.Close False
   
  End If
  Application.ScreenUpdating = True
End Sub

 

[stormseeker75]

Sorry if I was contradictory. This process was evolving as I was doing it and I probably didn't have the correct verbiage to explain to you. Nonetheless, you were able to help me not only with the code but also with the learning. I'm forever in your debt, friend!