Excel 2007 print problem

[Graham H]

Using a procedure which was originally created in Excel 2002 I am getting an error at the
following line when running the same procedure on Excel 2007.

ActiveSheet.PageSetup.PrintArea = Selection.Address

I am at a loss as to why it comes out at this point and would value any guidance.

Graham

 

[papou]

Hello Graham

There's obviously some hiccup with Selection.Address in your code.
What is the error message?
Does your selection contain any object?
Try and get what Selection.Address actually returns, for instance:
Debug.Print Selection.address
or
Msgbox Selection.Address

Cordially
Pascal

 

[Graham H]

Hi Pascal,
Thanks for reply. This occurs when the procedure with a de-bug window which
says 'PrintArea' of object 'PageSetup' failed. As I said it works perfectly fine in Exccel
2002, I just wondered what modification was required for 2007.The code it is included in is
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Cells.Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range("A1").Select
Sheets("Commands").Select


Thanks for your comments.

Graham

 

[papou]

Graham
Your code (ActiveSheet.PageSetup.PrintArea = Selection.Address) works fine
for me in Excel 2007, so there should not be any need for modification at
this point.
Are there other sheets selected?
Are there any calls to word application? (in which case the expression
"Selection" could be misinterprated)
Try and check with :
MsgBox TypeName(Selection) = "Range"
If it returns True then it means the selection is correct.
One alternative could be to fully qualify objects, ie:
Dim Ws As Worksheet
Set Ws = ActiveWorkbook.Worksheets("Sheet1")
Ws.PageSetup.PrintArea = Selection.Address

Another point in MHO is that you are using too many selections in your code.
Selection of cells should not be necessary, and you should consider avoiding
it.

HTH
Cordially
Pascal

 

[papou]

Ok got it.
Just pasted the whole code and tested, I have the same error.
Once you have selected the whole cells in your sheet (Cells.Select), the
Selection.Address returns "$1:$1048576"
This is what causes the error.
If your goal is actually to print all sheets of your workbook then I would
recommend you define the print area for each sheet.
Something like for instance:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.PrintArea = ws.UsedRange.Address
ws.PrintOut copies:=1
Next ws

HTH
Cordially
Pascal

 

[Graham H]

Many thanks for that Pascal and apologies for being so long in responding, I was actually
away. I apprecitae the time and effort you put into this and of course the successful
outcome!