Printing in Excel 2010 2

[integritycare]

Hello,

I am looking for some advice on how to prevent the cover of an excel 2010 spreadsheet from printing.
There are 10 spreadsheets in the workbook, but I need to prevent sheet 1 or the name "Coversheet" from printing out.

I print the work book to PDF with the following code;

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False


Dim MyFullName As String
MyFullName = ThisWorkbook.FullName

        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        MyFullName, Quality:=xlQualityMinimum, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True


Application.ScreenUpdating = True
End Sub

Thank You,

KP

 

[SkipVought]

Hi,

What happens if you were to HIDE that sheet prior to "printing?

 

[integritycare]

Hi SkipVought,

Thanks for the reply. Yes I had thought of that, But the cover sheet has a series of Check boxes. The check boxes select which worksheet has to be printed, by unhiding them.
As this workbook is fundamentally a finance workbook there will be times when different worksheets will have to be selected for printing for different departments. Would be quite easy if I was able to use Access! So my thoughts were to always have the cover sheet visible, but not to print it..
Just don't know where to go from here.

Thank you,

KP

 

[Andrzejek]

I just tried it ‘by hand’, so I am sure you can do it easily in the code, too.
Excel file: Sheet1, Sheet2, Sheet3 with the information on all 3 Sheets
Select Sheet2, hold down Ctrl and select Sheet3 (so only Sheet2 and Sheet3 are selected)
File – Save As - *.PDF
Creates PDF with just the information from selected Sheets (2 and 3)


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False


Dim MyFullName As String
MyFullName = ThisWorkbook.FullName

        ThisWorkbook.Worksheets("Coversheet").Visible = xlSheetHidden

        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        MyFullName, Quality:=xlQualityMinimum, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

        ThisWorkbook.Worksheets("Coversheet").Visible = xlSheetVisible

Application.ScreenUpdating = True
End Sub

 

[integritycare]

Hello SkipVought,
many thanks for your reply. This works really great.

Thank you for your help. Much appreciated

KP