Copying information in a cell to use as a PDF file name

[Mr_Natural]

Hi All,

I have a spreadsheet for sports stats, where I run two macros to show information for a particular team match-up.

Once the information for the two teams is displayed, I would like to utilize a macro(s) to be able to print selected areas of the worksheet to a PDF, copying the details of a nominated cell (say A1) to use as the name of the PDF. The details of this particular cell change depending upon which teams are displayed.

This is easy enough to do manually, however I am unable to replicate what I do manually via a macro. Is there any way of copying the details within the nominated cell and using this to name this file as part of the print to PDF process.

Any assistance would be greatly appreciated.

Thanks,

Mr N

[humdingaling]

here is code that will save your pdf into C:\Temp and name it based on what is in A1

Sub SaveAsPDf()

Dim strFileName As String
Const strPath As String = "\Temp\"

    strFileName =  Range("A1") 
    
    
    ChDrive "C:\"
    ChDir strPath
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
End Sub

of course change as required

ps you dont need to have that CHDRIVE/CHDIR in there if you dont want
you can just add it directly into the file name...like so

Sub SaveAsPDf()

    Dim strFileName As String
    strFileName = Range("A1")
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\temp\" & strFileName, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
End Sub

in fact you can shorten it even more to

Sub SaveAsPDf()

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\temp\" & Range("A1"), IgnorePrintAreas:=False, OpenAfterPublish:=False
        
End Sub

[Mr_Natural]

Thanks so much for taking the time to reply humdingaling.

I'll give your suggestion a try now.

Thanks again.

Mr N