Code to save sheet as "PDF" to Desktop - Based on ComboBox 1 Value

**matrixpom** · 11-13-2012, 10:01 PM

Hi all,

Does anyone have a code that can be attached to a cmd button on a userform that will copy the worksheet as displayed in "combobox1" to the desktop as a PDF file? Trying to record the macro but failing miserably, this is my code so far & any assistance will be greatly appreciated, thank you all & have a great day

Private Sub CommandButton5_Click()
  
Set ws = Worksheets(Me.ComboBox1.Value)

 If Blank Then
    MsgBox "Please Enter Branch"
    Exit Sub
  End If
    
    Worksheets(Me.ComboBox1.Value).Select
    ChDir "C:\Users\username\Desktop"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\username\Desktop\Rcd1.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
        
    
        Application.ScreenUpdating = True
        
End Sub

**patel45** · 11-14-2012, 03:13 AM

Private Sub CommandButton5_Click()
  
Set ws = Worksheets(Me.ComboBox1.Value)
If Blank Then
    MsgBox "Please Enter Branch"
    Exit Sub
End If
strFilePath "C:\Users\username\Desktop\"
strPdfName = "Rcd1.pdf"
Worksheets(Me.ComboBox1.Value).Copy
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFilePath & strPdfName, _
  Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=False
ActiveWorkbook.Close (False)
   
End Sub

**matrixpom** · 11-20-2012, 08:29 PM

Hi patel,

Sorry for the delayed response, I've been away. Tried your code but for some reason it fails @ strFilePath "C:\Users\username\Desktop\" ??? Thanks for your time patel! - Marco

**matrixpom** · 11-21-2012, 12:40 AM

Hi Patel,

A few tweaks & now works perfect! grazie mille!

 Private Sub CommandButton5_Click()
  
Set ws = Worksheets(Me.ComboBox1.Value)

 If Blank Then
    MsgBox "Please Enter Branch"
    Exit Sub
  End If
     Worksheets(Me.ComboBox1.Value).Visible = True
    Worksheets(Me.ComboBox1.Value).Select
    ChDir "C:\Users\username\Desktop"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\username\Desktop\CM1.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
ActiveSheet.Visible = False
   
End Sub