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?

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)

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)

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)

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)

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