VBA printing using a named print range

[GerrypFL]

I have created a named print range (dynamic range) that I want to create a VBA trigger button to print the named print range. I have attempted to create the VBA coding but end up with errors.

Sub PrintCells()
With Sheet6
.PageSetup.PrintArea = Range("PAYROLL_DEDUCTION_NOTIFICATION")
.PrintOut

End With
End Sub

I am struggling with how to use the named print range to preserve the dynamic nature. Excel has a bug in that if you reference the dynamic print range and someone makes any changes to the printer options the print range is converted to the last fixed range that was used. If I can call the named range when using the VBA code to print the dynamic range will always be valid. Any solution for using VBA to print the named range is appreciated.

 

[DanteAmor]

Hi and welcome to MrExcel.
Try this

Sub PrintCells()
  With Sheet6
    .PageSetup.PrintArea = Range("PAYROLL_DEDUCTION_NOTIFICATION").Address
    .PrintOut
  End With
End Sub

 

[GerrypFL]

Hi and welcome to MrExcel.
Try this

Sub PrintCells()
  With Sheet6
    .PageSetup.PrintArea = Range("PAYROLL_DEDUCTION_NOTIFICATION").Address
    .PrintOut
  End With
End Sub

I gave it a try and ended up getting a Global fault error

 

[DanteAmor]

According to your formula to determine the named range:
The value of the rows is determined by the data you have in cell P4 of the "Criteria" sheet, then:

Sub PrintCells()
  Dim vRow
  vRow = Sheets("criteria").Range("P4").Value
  With Sheet6
    .PageSetup.PrintArea = .Range("A1", .Cells(vRow, 5)).Address
    .PrintOut
  End With
End Sub

 

[GerrypFL]

View attachment 17380

According to your formula to determine the named range:
The value of the rows is determined by the data you have in cell P4 of the "Criteria" sheet, then:

Sub PrintCells()
  Dim vRow
  vRow = Sheets("criteria").Range("P4").Value
  With Sheet6
    .PageSetup.PrintArea = .Range("A1", .Cells(vRow, 5)).Address
    .PrintOut
  End With
End Sub

Thanks!! That solved the issue.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[GerrypFL]

I'm glad to help you. Thanks for the feedback.

Sorry, is there coding that can be inserted into what you had suggested to keep scaling to print all of the columns in the range on one page?

 

[GerrypFL]

This does not work!

Sub NewPrinting()
Dim vRow
vRow = Sheets("criteria").Range("P4").Value

confirm = InputBox("Are you sure you want to print? (enter 'y' to continue)")

If confirm <> "y" Then GoTo TheEnd

With Sheet6
.PageSetup.PrintArea = .Range("A1", .Cells(vRow, 5)).Address
.FitToPagesWide = 1
.PrintOut
End With
TheEnd:

End Sub

 

[DanteAmor]

Try this

Sub PrintCells()
  Dim vRow
  vRow = Sheets("criteria").Range("P4").Value
  If MsgBox("Are you sure you want to print?", vbYesNo + vbQuestion, "New Printing") = vbNo Then Exit Sub
  
  With Sheet6
    .PageSetup.PrintArea = .Range("A1", .Cells(vRow, 5)).Address
    With .PageSetup
      .Orientation = xlPortrait
      .FitToPagesWide = 1
      .FitToPagesTall = 1
    End With
    .PrintOut
  End With
End Sub