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]

Hi and welcome to MrExcel

In "DO NOT DELETE" sheet put the following: in column X the sheet name, in column Y the named range, and in column Z the 1 or 0. What really matters is the named range and the value in column Z. But in column X, you can enter the sheet name as a reference_

DANTE AMOR
	X	Y	Z
1	Sheet name	Named range	Print
2	Main Page	Main_Page_SHEET	1
3	Sales	Sales_SHEET	0
4	Additional Page-4	Additional_Page_4_SHEET	1
DO NOT DELETE

The macro will read that list of data, and anything with a 1 will be printed, as long as there is data in that range.

Sub print_named_range()
  Dim i As Long, n As Long
  Dim sRng As String
 
  Sheets("DO NOT DELETE").Select
  For i = 2 To Range("Z" & Rows.Count).End(3).Row
    If Range("Z" & i).Value = 1 Then
      sRng = Range("Y" & i).Value
      n = WorksheetFunction.CountIf(Range(sRng), "<>")
      If n > 0 Then
        Range(sRng).PrintOut
      End If
    End If
  Next
 
  ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "all.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Try and comment

 

[stormseeker75]

Thank you, Dante! This is SUPER and really really close. It's printing things to individual sheets. Can we make it print to 1 sheet with all my individual jobs in one file? That would be the coup de grace!

 

[stormseeker75]

One other thing. The 1s and 0s in the DO NOT DELETE sheet are sometimes static and sometimes formulas. When one of my formulas in Add_PG_1 was a 1, the macro threw an error:

Run-time error '1004':
Method 'Range' of object '_Global' failed

 

[DanteAmor]

Add_PG_1

Check that the names of the named ranges are spelled correctly.

The formula returns 1 or "1", if it returns "1"

Modify this line in the macro:
If Range("Z" & i).Value = 1 Then

For this line:
If Range("Z" & i).Text = "1" Then

Also check that the formula is not returning " 1 " and spaces before or after the 1

 

[stormseeker75]

Sub print_named_range()
  Dim i As Long, n As Long
  Dim sRng As String
 
  Sheets("DO NOT DELETE").Select
  For i = 2 To Range("Z" & Rows.Count).End(3).Row
    If Range("Z" & i).Text = "1" Then
      sRng = Range("Y" & i).Value
      n = WorksheetFunction.CountIf(Range(sRng), "<>")
      If n > 0 Then
        Range(sRng).PrintOut
      End If
    End If
  Next
 
  ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "all.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

That's the code as it sits right now. I have the columns setup as you suggested. In the Print column, I have this formula: =IF('Main Page'!G5>0,"1","0") The majority of it is moot but you'll see I'm using "1" and "0" so the code should be good. However, it prints the first page which is MAIN_PAGE_PA to PDF. It tries to print ADD_PG_1 and when I click OK, I get the run-time error again:

Run-time error '1004':
Method 'Range' of object '_Global' failed

 

[DanteAmor]

Run-time error '1004':
Method 'Range' of object '_Global' failed

ADD_PG_1

The error is because there is no range named with the name: ADD_PG_1


But if you have the range named in the sheet scope, then try the following. Considering you're going to put the sheet name in column X:
Check that the names and named ranges are correctly written in the cells.

Sub print_named_range()
  Dim i As Long, n As Long
  Dim sRng As String, sNam As String
 
  Sheets("DO NOT DELETE").Select
  For i = 2 To Range("Z" & Rows.Count).End(3).Row
    If Range("Z" & i).Text = "1" Then
      sRng = Range("Y" & i).Value
      sNam = Range("X" & i).Value
      n = WorksheetFunction.CountIf(Sheets(sNam).Range(sRng), "<>")
      If n > 0 Then
        Sheets(sNam).Range(sRng).PrintOut
      End If
    End If
  Next
 
  ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "all.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

 

[DanteAmor]

Try this one:

Sub print_named_range()
  Dim i As Long, n As Long
  Dim sRng As String, sNam As String
  Dim rng As Range
  Dim NameExist As Boolean
 
  Sheets("DO NOT DELETE").Select
  For i = 2 To Range("Z" & Rows.Count).End(3).Row
    If Range("Z" & i).Text = "1" Then
      sRng = Range("Y" & i).Value
      sNam = Range("X" & i).Value
      
      If Evaluate("IsRef('" & sNam & "'!" & sRng & ")") Then
      
        n = WorksheetFunction.CountIf(Sheets(sNam).Range(sRng), "<>")
        If n > 0 Then
          Sheets(sNam).Range(sRng).PrintOut
        End If
      Else
        MsgBox "Not exists: " & sNam & " " & sRng
      
      End If
    End If
  Next
 
  ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "all.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

 

[stormseeker75]

Alright, we are getting closer! Thank you for the continued help. It's not failing now. However, it is printing out the pages that it finds. Then it's combining the entire workbook into the PDF file and not selecting the ranges.

I bought a book so I can learn this stuff better.

 

[DanteAmor]

It's not failing now.

We've already achieved the first part.

Then it's combining the entire workbook into the PDF file and not selecting the ranges.

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?

I look forward to your comments while I prepare the code for that.