Set print range to last row and last column across different sheets
 

Hi there – I’d like a dynamic print range to be coded in the
Workbook_BeforePrint sub. Here are the specs: from B1 down to the
last row of data (where some rows are skipped) -- over to the last
column of data based on row 12 (row 12 will always be the last data
col) -- and do this for 7 sheets (Name 1, Name 2…., Name 7) named with
the same prefix, like "Name _". Any help on this would be greatly
appreciated, as setting the print range manually is no fun. Thanks!

Set print range to last row and last column across different sheet
 

This can be done a number of ways, but I've done it here by setting a
worksheet level named range based on the parameters you describe. It also
could be done with an OFFSET equation of some type. I've set the named
range to be "myPrintArea" on all workksheets. You could just as easily
change the name to PrintArea and have the same result.

Option Explicit
Sub SetPrintArea()
'B1 down to last row of data
Dim lRow As Long
Dim aWB As Workbook
Dim WS As Worksheet
Dim lCol As Long
Dim myRange As Range
Dim myRefersTo As String

Set aWB = ActiveWorkbook

For Each WS In aWB.Worksheets
lRow = WS.Cells(WS.Rows.Count, "B").End(xlUp).Row
lCol = WS.Cells(12, WS.Columns.Count).End(xlToLeft).Column
Debug.Print lRow, lCol
Set myRange = WS.Range("B1")
If lCol = myRange.Column Then
Set myRange = myRange.Resize(lRow - myRange.Row + 1, lCol -
myRange.Column + 1)
End If
myRefersTo = "='" & WS.Name & "'!" & myRange.Address
WS.Names.Add Name:="myPrintArea", RefersTo:=myRefersTo

Next WS

End Sub
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



" wrote:

Hi there €“ Id like a dynamic print range to be coded in the
Workbook_BeforePrint sub. Here are the specs: from B1 down to the
last row of data (where some rows are skipped) -- over to the last
column of data based on row 12 (row 12 will always be the last data
col) -- and do this for 7 sheets (Name 1, Name 2€¦., Name 7) named with
the same prefix, like "Name _". Any help on this would be greatly
appreciated, as setting the print range manually is no fun. Thanks!

Set print range to last row and last column across different sheet
 

This is the code that I used maybe you can modify it to fit your needs, its
pretty self explanatory. It specifies what sheet and what range to set print
area.

Sheets("DATABASE").Select
ActiveSheet.PageSetup.PrintArea = "$D$9:$T$37"


-Charlie

" wrote:

Hi there €“ Id like a dynamic print range to be coded in the
Workbook_BeforePrint sub. Here are the specs: from B1 down to the
last row of data (where some rows are skipped) -- over to the last
column of data based on row 12 (row 12 will always be the last data
col) -- and do this for 7 sheets (Name 1, Name 2€¦., Name 7) named with
the same prefix, like "Name _". Any help on this would be greatly
appreciated, as setting the print range manually is no fun. Thanks!

Set print range to last row and last column across differentsheet
 

THANKS!