CODEWITHSUNDEEP
excelexcel-formulavba
Meghan Lewis
Meghan Lewis

Reputation: 1

save pdf as cell name with multiple cells

I am trying to save a pdf with the name being info in a cell, but the name involves multiple cells. the cells have formulas inputted because they pull from a seperate sheet in my spreadsheet. how would i could about doing this? this is the formulas in the cells that i want to pull the name for the pdf from:

=VLOOKUP('Work Order'!F26,'Fort McMurray File Services'!A2:AJ1515,6,FALSE)
=VLOOKUP('Work Order'!F26,'Fort McMurray File Services'!A2:AJ1515,6,FALSE)

And this is the code i am using in vba:

Private Sub filename_cellvalue()
    'Update 20141112
    Dim Path As String
    Dim filename As String

    Path = "C:\Users\meghan lewis\Desktop\MASS DEMO"
    filename = .Range("C7").Value & .Range("D7").Value & _
               .Range("E7").Value & .Range("F7").Value & _
               .Range("G7").Value & .Range("H7").Value & .Range("I7").Value
    ActiveWorkbook.SaveAs filename:=Path & filename & ".PDF", FileFormat:=xlNormal
End Sub


Sub SaveAsPDF()
    Dim fName As String

    With Worksheets("WORK ORDER")
        fName = .Range("C7").Value & .Range("D7").Value & _
                .Range("E7").Value & .Range("F7").Value & _
                .Range("G7").Value & .Range("H7").Value & _
                .Range("I7").Value
    End With

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
    "C:\Users\meghan lewis\Desktop\MASS DEMO & fName", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

When I try to save it saves the pdf as mass demo rather than pulling from the cells. Please advise.

Upvotes: 0

Views: 375

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

  1. How does your code even compile? You have unqualified range addresses like .Range("C7").Value, .Range("D7").Value etc in Sub filename_cellvalue

  2. In Sub SaveAsPDF(), change "C:\Users\meghan lewis\Desktop\MASS DEMO & fName" to "C:\Users\meghan lewis\Desktop\MASS DEMO" & fName

fname within quotes will behave as a String and not a variable.

Upvotes: 2

Related Questions