Save as PDF but only selected sheets and hidden sheets

[charliew]

Evening everyone,

Firsty i'd like to say thank you for taking the time to read my problem and help me.

Secondly I have tried figuring this problem out myself but my deadline is tomorrow and i am no closer to solving it!!

Private Sub CommandButton3_Click()
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="U:\sales\Quotes test\2020\May\" & Range("d4") & " " & Range("F1").Value & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
    
    If Application.Sheets("Sheet2").Range("D7").Value = "" Then
        MsgBox "Please Insert Quote Reference"
        
    End If
End Sub

This is my code and it works perfectly for when the document i wanted to save was only the 3 tabs i had open. My boss now wants these 3 sheets part of larger document (12 sheets!) so now I still need my button to save these 3 sheets as PDF but 2 of them will be hidden (Sheet1, Sheet2) and one will not be hidden (sheet3).
I know there is a way to open the hidden sheets save them and then hide them again, and i have tried a few variations but to be honest i'm stabbing in the dark with it!

Thank you again!

 

[DanteAmor]

Try this:

Private Sub CommandButton3_Click()
  Dim sPath As String, sFile As String
  
  sPath = "U:\sales\Quotes test\2020\May\"
  sFile = Range("D4").Value & " " & Range("F1").Value & ".pdf"
  
  Sheets("Sheet1").Visible = True
  Sheets("Sheet2").Visible = True

  Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
  Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sFile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
    
  If Application.Sheets("Sheet2").Range("D7").Value = "" Then
    MsgBox "Please Insert Quote Reference"
  End If
  
  Sheets("Sheet1").Visible = False
  Sheets("Sheet2").Visible = False
  
End Sub

 

[charliew]

That has worked perfectly thank you! and it has neatened up my code which was really bugging me haha!

You have saved my life!! thank you for taking the time!

The print areas and formatting is a bit wonky but i can sort that out!!

Thanks again.

 

[charliew]

@DanteAmor i tried it a second time after having a fiddle with print areas and it now throwing out an error highlihgting the below string:

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sFile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True

arrow pointing at the last line. i broke it!

 

[charliew]

It is now working again but only printing the page headers...none of the content!!

 

[DanteAmor]

Do you have the print area set on each sheet? You must remove the print area.

Change this
IgnorePrintAreas:=False

To this
IgnorePrintAreas:=True

Try.

 

[charliew]

ahhhh my tired brain missed that!! feel silly now!!

thanks again!!

 

[charliew]

@DanteAmor I did this, removed all print areas and made the code alterations...its still not saving the content just headers and footers...

 

[DanteAmor]

If you already removed the printing areas, try again with the code from post #2