CODEWITHSUNDEEP
vbaexcelfilenamesexport-to-pdf
amea6995
amea6995

Reputation: 37

Unique File Name when exporting based on cell values

I'm trying to export multiple charts as individual pdf files using VBA but I was wondering if its possible that the file name for each of the pdf files be different and take it from a cell value in a range (Ex. A2-A6) so that the name of the file is based on a corresponding cell (A2) and the next one will be based on the cell below it (A3) and so on.

This is what I have so far. Essentially the names are unique but I'm only limited to having the number (i=1) change.

Sub Macro9()

Sheets("Charts").Select
i = 1

For Each myChart In ActiveSheet.ChartObjects
    myChart.Activate
    myPDF = "\\stchsfs\arboari$\Profile-Data\Desktop\Export Trial1\Graph Export_" & i & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=myPDF, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    i = i + 1
Next myChart

End Sub

Thanks!

Upvotes: 0

Views: 46

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

Try the code below, explanations inside the code/s comments:

Option Explicit

Sub Macro9()

Dim myChart As ChartObject
Dim myPDF As String
Dim i As Long

With Sheets("Charts")  '<-- use With instead of using Select 
    i = 1    
    For Each myChart In .ChartObjects
        myChart.Activate
        myPDF = "\\stchsfs\arboari$\Profile-Data\Desktop\Export Trial1\Graph Export_" & Sheets("Master Sheet").Range("A" & i + 1).Value2 & ".pdf" '<-- modifed this line
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPDF, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

        i = i + 1
    Next myChart
End With

End Sub

Upvotes: 2

Related Questions