Help with VBA

[inspectorNDT]

I am really new to VBA's. I am trying to make a "create PDF" button to make a PDF out of the active tab. The file name would be using cells on the active tab then the name of the tab and then another cell.

I would also like it to prompt the user if a file of the same name exist and I would like it to auto save to the active users desktop.

thank you in advance for your help

 

[Logit]

.
This is one method :

Option Explicit


Sub ExportToPDFs()
Dim nm As String
Dim ws As Worksheet
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String
    
    saveInFolder = Environ("USERPROFILE") & "\Desktop"
    If Right(saveInFolder, 1) <> "\" Then
        saveInFolder = saveInFolder & "\"
    End If
                    nm = ActiveSheet.Name
                    celOne = Range("A1").Value
                    celTwo = Range("C2").Value
                    celThree = Range("F1").Value
            
                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
        
End Sub

 

[inspectorNDT]

thank you for you help but all that happens is an 'Run time error '1004' application-defined or object-defined error. I did change the cell numbers to reflect what I need.

Option Explicit




Sub ExportToPDFs()
Dim nm As String
Dim ws As Worksheet
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String

saveInFolder = Environ("USERPROFILE") & "\Desktop"
If Right(saveInFolder, 1) <> "" Then
saveInFolder = saveInFolder & ""
End If
nm = ActiveSheet.Name
celOne = Range("D1").Value
celTwo = Range("C5").Value
celThree = Range("O2").Value

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

 

[Michael M]

Ok, try this....AND make sure the cell references for the filenames are correct AND populated

Sub ExportToPDFs()
Dim nm As String
Dim ws As Worksheet
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String

saveInFolder = Environ("USERPROFILE") & "\Desktop\"
If Right(saveInFolder, 1) <> "" Then
saveInFolder = saveInFolder & ""
End If
nm = ActiveSheet.Name
celOne = Range("D1").Value
celTwo = Range("C5").Value
celThree = Range("O2").Value

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True
End Sub

 

[Logit]

.
You were missing part of the macro code :

Sub ExportPDF()
Dim nm As String
Dim ws As Worksheet
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String
    
    saveInFolder = Environ("USERPROFILE") & "\Desktop"
    If Right(saveInFolder, 1) <> "\" Then
        saveInFolder = saveInFolder & "\"
    End If
                    nm = ActiveSheet.Name
                    celOne = Range("D1").Value
                    celTwo = Range("C5").Value
                    celThree = Range("O2").Value


                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

 

[Logit]

.
You are welcome. Glad to help.

 

[inspectorNDT]

stilling getting the run time error with this code

 

[inspectorNDT]

Still getting the run time error with this code

.
You were missing part of the macro code :

Sub ExportPDF()
Dim nm As String
Dim ws As Worksheet
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String
    
    saveInFolder = Environ("USERPROFILE") & "\Desktop"
    If Right(saveInFolder, 1) <> "\" Then
        saveInFolder = saveInFolder & "\"
    End If
                    nm = ActiveSheet.Name
                    celOne = Range("D1").Value
                    celTwo = Range("C5").Value
                    celThree = Range("O2").Value


                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

 

[inspectorNDT]

Just asking but shouldn't the code have this in it?

Private Sub CommandButton1_Click()


End Sub

 

[inspectorNDT]

Also I can't tell what is different between this code and the first one you sent me.

.
You were missing part of the macro code :

Sub ExportPDF()
Dim nm As String
Dim ws As Worksheet
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String
    
    saveInFolder = Environ("USERPROFILE") & "\Desktop"
    If Right(saveInFolder, 1) <> "\" Then
        saveInFolder = saveInFolder & "\"
    End If
                    nm = ActiveSheet.Name
                    celOne = Range("D1").Value
                    celTwo = Range("C5").Value
                    celThree = Range("O2").Value


                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub