open spreadsheet in print view when clicked on command button

[ManvinderKaur]

Hi, I have 28 excel sheets in a workbook. There is one main form in workbook. When i click on one button 3 spreedsheets become visible.I need a button on each sheet, so that I can print out other sheets. How come I do that.

 

[Andrew Poulsom]

Maybe try:

Sub Test()
    Dim ws As Worksheet
    For Each ws in Worksheets
        If ws.Visible Then ws.PrintOut
    Next ws
End Sub

 

[ManvinderKaur]

Thanks Andrew ..
But I want to modify my question ..
I want a button on one spreadsheet...when any one click on that button-they will get print out of another hidden sheet
Thanks
Manvinder

 

[Andrew Poulsom]

To print a hidden sheet you need to unhide it first. Like this:

Sub Test()
    With Worksheets("Sheet1")
        .Visible = True
        .PrintOut
        .Visible = False
    End With
End Sub

 

[ManvinderKaur]

Thanks a lot
I have used the following code......It will show print preview of page..because this sheet is for different people in Australia...So I could not use .printout...because they have to select there own printer..so I showed only print preview....Any more suggestions????....



Private Sub cmdPrintSiteSurveyEnergy_Click()

Dim SiteSurveyEnegy As Integer
' message box to prompt print out
SiteSurveyEnegy = MsgBox("Do you want to take print out of Site Survey Energy Sheet", vbYesNo + vbInformation, "Print Out")
If SiteSurveyEnegy = vbYes Then
With Worksheets("3.0 Site Survey Energy Print")
.Visible = True
.Activate
.Range("A1").Select

With Worksheets("3.0 Site Survey Energy Print").PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
.PrintArea = "$A$1:$J$38"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Page &P"
.CenterFooter = ""
.RightFooter = "&T &D"
.LeftMargin = Application.InchesToPoints(0.26)
.RightMargin = Application.InchesToPoints(0.26)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments

.Zoom = 77

End With
.PrintPreview
End With

End If
Exit Sub
ErrorLine:
MsgBox ("Error Number:" & Err.Number & ". " & Err.Description & ". Source:" & Err.Source)

ActiveWorkbook.Close
End Sub