Excel 2007, Save as PDF Macro, "Save as" name to reference cell

[igreenb]

I am trying to have a Macro save an Excel 2007 spreadsheet as a PDF (I have the Save as PDF or XPS installed.) How can I get the file name to save as 2 cells concatenated?

I am working with the following code so far. Any help greatly appreciated.

Sub Macro4()
'
' Macro4 Macro
'

'
    ChDir "C:\Documents and Settings\igreenberg\Desktop"
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\igreenberg\Desktop\p21.xls"
    Windows("PriceSheet2.xlsm").Activate
    Workbooks.Open Filename:="C:\Price Sheet Template (Distributors).xltx", _
        Editable:=True

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Y:\Quotes\2010 Price Sheets\Sample.pdf", Quality _
        :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Windows("p21.xls").Activate
    ActiveWorkbook.Close
    ActiveWorkbook.Close
End Sub

[Ionelul]

Hope this code works for you:

Sub Macro4()

Dim aWB as Workbook
dim aWS as Worksheet
dim sFileName as string

    ChDir "C:\Documents and Settings\igreenberg\Desktop"
    set aWB = Workbooks.Open Filename:= _
        "C:\Documents and Settings\igreenberg\Desktop\p21.xls"
    set aWS = aWB.Worksheets(1) // or whatever sheet you want to save or get the name from
    Windows("PriceSheet2.xlsm").Activate
    Workbooks.Open Filename:="C:\Price Sheet Template (Distributors).xltx", _
        Editable:=True
  
     sFileName =  "Y:\Quotes\2010 Price Sheets\" & AWS.Cells(RowIndex, ColumnIndex).Value & aWS.Cells(RowIndex2, ColumnIndex2).value & ".pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sFileName, Quality _
        :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Windows("p21.xls").Activate
    ActiveWorkbook.Close
    ActiveWorkbook.Close
End Sub

Ionut