Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area (ActiveSheet)
I am currently using the following code to set the print area in Sheet1 on my
workbook. Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets("Sheet1").PageSetup.PrintArea = Sheet1.Range("PrintArea").Address End Sub I would like to set this up to do the same thing for the Active Sheet a user is on. I have tried a number of ways, but I need some help. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area (ActiveSheet)
"Josh O." wrote:
I am currently using the following code to set the print area in Sheet1 on my workbook. Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets("Sheet1").PageSetup.PrintArea = Sheet1.Range("PrintArea").Address End Sub I would like to set this up to do the same thing for the Active Sheet a user is on. I have tried a number of ways, but I need some help. Any ideas? Ok. Correction. I got it to work on the active sheet. However, I want the print range to start at A3 instead of A1. Here is the code I am using: Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets(ActiveSheet.Name).PageSetup.PrintArea = Sheets(ActiveSheet.Name).Range("PrintArea").Addres s End Sub And the Named Ranges: LastRow = LOOKUP(2,1/('Sheet1'!$B$1:$B$5000<""),ROW('Sheet1'!$B$1:$B$5 000)) PrintArea = OFFSET('Sheet1'!$A$3,-2,0,'Workbook1.xls'!LastRow,19) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area (ActiveSheet)
give this a try...
Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.PrintArea = .Range(.Range("A3"), _ .Range("PrintArea").Cells(.Range("PrintArea").Cell s.Count)) End With End Sub -- HTH... Jim Thomlinson "Josh O." wrote: "Josh O." wrote: I am currently using the following code to set the print area in Sheet1 on my workbook. Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets("Sheet1").PageSetup.PrintArea = Sheet1.Range("PrintArea").Address End Sub I would like to set this up to do the same thing for the Active Sheet a user is on. I have tried a number of ways, but I need some help. Any ideas? Ok. Correction. I got it to work on the active sheet. However, I want the print range to start at A3 instead of A1. Here is the code I am using: Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets(ActiveSheet.Name).PageSetup.PrintArea = Sheets(ActiveSheet.Name).Range("PrintArea").Addres s End Sub And the Named Ranges: LastRow = LOOKUP(2,1/('Sheet1'!$B$1:$B$5000<""),ROW('Sheet1'!$B$1:$B$5 000)) PrintArea = OFFSET('Sheet1'!$A$3,-2,0,'Workbook1.xls'!LastRow,19) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area (ActiveSheet)
It gives me a "Runtime Error: 1004, Unable to Set PrintArea Property of the
PageSetup Class". "Jim Thomlinson" wrote: give this a try... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.PrintArea = .Range(.Range("A3"), _ .Range("PrintArea").Cells(.Range("PrintArea").Cell s.Count)) End With End Sub -- HTH... Jim Thomlinson "Josh O." wrote: "Josh O." wrote: I am currently using the following code to set the print area in Sheet1 on my workbook. Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets("Sheet1").PageSetup.PrintArea = Sheet1.Range("PrintArea").Address End Sub I would like to set this up to do the same thing for the Active Sheet a user is on. I have tried a number of ways, but I need some help. Any ideas? Ok. Correction. I got it to work on the active sheet. However, I want the print range to start at A3 instead of A1. Here is the code I am using: Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets(ActiveSheet.Name).PageSetup.PrintArea = Sheets(ActiveSheet.Name).Range("PrintArea").Addres s End Sub And the Named Ranges: LastRow = LOOKUP(2,1/('Sheet1'!$B$1:$B$5000<""),ROW('Sheet1'!$B$1:$B$5 000)) PrintArea = OFFSET('Sheet1'!$A$3,-2,0,'Workbook1.xls'!LastRow,19) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area (ActiveSheet)
OOPS...
Try this Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.PrintArea = .Range(.Range("A3"), _ .Range("PrintArea").Cells(.Range("PrintArea").Cell s.Count)).address End With End Sub -- HTH... Jim Thomlinson "Josh O." wrote: It gives me a "Runtime Error: 1004, Unable to Set PrintArea Property of the PageSetup Class". "Jim Thomlinson" wrote: give this a try... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.PrintArea = .Range(.Range("A3"), _ .Range("PrintArea").Cells(.Range("PrintArea").Cell s.Count)) End With End Sub -- HTH... Jim Thomlinson "Josh O." wrote: "Josh O." wrote: I am currently using the following code to set the print area in Sheet1 on my workbook. Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets("Sheet1").PageSetup.PrintArea = Sheet1.Range("PrintArea").Address End Sub I would like to set this up to do the same thing for the Active Sheet a user is on. I have tried a number of ways, but I need some help. Any ideas? Ok. Correction. I got it to work on the active sheet. However, I want the print range to start at A3 instead of A1. Here is the code I am using: Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets(ActiveSheet.Name).PageSetup.PrintArea = Sheets(ActiveSheet.Name).Range("PrintArea").Addres s End Sub And the Named Ranges: LastRow = LOOKUP(2,1/('Sheet1'!$B$1:$B$5000<""),ROW('Sheet1'!$B$1:$B$5 000)) PrintArea = OFFSET('Sheet1'!$A$3,-2,0,'Workbook1.xls'!LastRow,19) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area (ActiveSheet)
On Tue, 18 Mar 2008 13:21:01 -0700, Josh O.
wrote: It gives me a "Runtime Error: 1004, Unable to Set PrintArea Property of the PageSetup Class". "Jim Thomlinson" wrote: give this a try... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.PrintArea = .Range(.Range("A3"), _ .Range("PrintArea").Cells(.Range("PrintArea").Cell s.Count)) End With End Sub PrintArea takes a string, so just add .Address on to the end of the range. Or you could use Excel's built in print area. When you set the print area in the UI, it creates a name called Print_Area. Just manipulate that named range and you won't have to access the PageSetup object at all. With ActiveSheet .Names.Add "Print_Area", .Range("A3:F10") End With or whatever range you want. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
determining dynamic print area | Excel Programming | |||
Dynamic Print Area | Excel Discussion (Misc queries) | |||
dynamic print area | Excel Worksheet Functions | |||
Vb to set print area for dynamic pages | Excel Programming | |||
Dynamic print area | Excel Programming |