UsedRange (Best way to determine to use for PrintArea)

D

Dennis

Using XL 2003 & 97

How do I finish the following:

ActiveSheet.PageSetup.PrintArea = ???UsedRange???

Attempting to use a Macro to set all PageSetup paramaters for all Sheets in
the ActiveWorkbook.

Got all except that I want to set the PrintArea to the UsedRange of each
sheet.

(Attempted and it did not work):
Dim MyRange As Range
MyRange = ActiveSheet.UsedRange
With EachSheet.PageSetup
.PrintArea = MyRange
. ...........................
End With

Thoughts?

TIA Dennis
 
D

Dennis

Thanks Papou,

An Error Message occurs when I run the following in a loop

"Run-time error "91' Object variable or With block variable not set

CODE *************************************
Dim EachSheet As Worksheet
For Each EachSheet In ActiveWorkbook.Worksheets
Dim MyRange As Range
MyRange = ActiveSheet.UsedRange.Address
With EachSheet.PageSetup
.PrintArea = MyRange
................
End With
Next EachSheet
**************************************

I think the line [.PrintArea = MyRange] is incorrect

Thoughts?
 
D

Dave Peterson

..printarea is looking for a string.

You dimmed myRange as Range.

So you could either use a string:

Option Explicit
Sub testme()
Dim EachSheet As Worksheet
Dim MyRangeAddr As String
For Each EachSheet In ActiveWorkbook.Worksheets
MyRangeAddr = EachSheet.UsedRange.Address
With EachSheet.PageSetup
.PrintArea = MyRangeAddr
End With
Next EachSheet
End Sub

Or you could use a range:

Option Explicit
Sub testme2()
Dim EachSheet As Worksheet
Dim MyRange As Range
For Each EachSheet In ActiveWorkbook.Worksheets
Set MyRange = EachSheet.UsedRange
With EachSheet.PageSetup
.PrintArea = MyRange.Address
End With
Next EachSheet
End Sub

(and when you assign objects to object variables (myrange is a range object),
you have to use "Set".)

And watch out for this line:
MyRange = ActiveSheet.UsedRange.Address
I bet you want:
myrange EachSheet.usedrange.address



Thanks Papou,

An Error Message occurs when I run the following in a loop

"Run-time error "91' Object variable or With block variable not set

CODE *************************************
Dim EachSheet As Worksheet
For Each EachSheet In ActiveWorkbook.Worksheets
Dim MyRange As Range
MyRange = ActiveSheet.UsedRange.Address
With EachSheet.PageSetup
.PrintArea = MyRange
................
End With
Next EachSheet
**************************************

I think the line [.PrintArea = MyRange] is incorrect

Thoughts?

papou said:
Hello Dennis
MyRange = ActiveSheet.UsedRange.Address
HTH
Cordially
Pascal
Click to expand...
Click to expand...
 
Top