Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set print range to last row and last column across differentsheet
THANKS!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel ~ print same range across sheets | Excel Discussion (Misc queries) | |||
print sheets from a range with names | Excel Programming | |||
How do I add Watermark, across all the sheets in the print range, | Excel Worksheet Functions | |||
Print Same Range on Multiple Sheets? | Excel Programming | |||
how do you set or reset the print range for several sheets at a ti | Excel Programming |