Solved: Print problem

[blackie42]

Hi,

I used the macro recorder to set the print up on a 17 sheet workbook as below. Problem is after it prints the sheets it debugs 'printout method of worksheet class failed'. Any ideas what I need to fix? Also I have named the first sheet 'SUMMARY' and would like this to 'page fit' on portrait - but include this in the same routine.

Sub checkprintsettings()

Application.ScreenUpdating = False

For Each sht In Worksheets
sht.Activate
With ActiveSheet.PageSetup
.PrintGridlines = True
.Orientation = xlLandscape
.Zoom = 87

ActiveSheet.PrintOut copies:=1, Collate:=True

End With

Next sht

End Sub


Many thanks

Jon

[Bob Phillips]

Don't know about the error, I don't get it, but for the portrait print



Sub checkprintsettings()
Dim sht As Worksheet

Application.ScreenUpdating = False

For Each sht In Worksheets
With sht.PageSetup
.PrintGridlines = True
If sht.Name = "SUMMARY" Then
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
Else
.Orientation = xlLandscape
.Zoom = 87
End If

sht.PrintPreview 'Out copies:=1, Collate:=True
End With
Next sht

End Sub

[blackie42]

Thanks for help XLD - when I do this in test with just 3 sheets and very little other code it works fine. However when I copy to my project which has lots of modules/functions/user forms it still gives me the debug error.
Project too big to post as its 700Kbs - can only assume theres something in there thats causing the problem. I'll just ask users to use the 'end' button on the debug as it does actually print everything.

thanks again

regards

[blackie42]

There is a hidden sheet - could this be the problem? If so how can I tell the print macro tho ignore any hidden sheets?

thanks

[Charlize]

?Sub checkprintsettings()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In Worksheets
'Additional check
If sht.Visible = xlSheetVisible Then
With sht.PageSetup
.PrintGridlines = True
If sht.Name = "SUMMARY" Then
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
Else
.Orientation = xlLandscape
.Zoom = 87
End If
sht.PrintPreview 'Out copies:=1, Collate:=True
End With
End If
Next sht
End Sub

[Bob Phillips]

Sub checkprintsettings()
Dim sht As Worksheet

Application.ScreenUpdating = False

For Each sht In Worksheets
IF sht.Visible = xlSheetVisible Then
With sht.PageSetup
.PrintGridlines = True
If sht.Name = "SUMMARY" Then
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
Else
.Orientation = xlLandscape
.Zoom = 87
End If

sht.PrintPreview 'Out copies:=1, Collate:=True
End If
End With
Next sht

End Sub


_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

[blackie42]

Guys - thanks for your help - works fine both ways.

regards

Jon