Modifying Macro to add Column B to file name when exporting

[GingaNinga]

Hello - I found this code on the message board which works great for my purposes, expect that I am hoping to include the value in Column C to the file name when exporting.

Code is below:

Sub exportPages()

Set Sht = Worksheets("Sheet1")
' The variable Sht now holds the sheet that should be printed, so we can use that in the macro instead of repeating Worksheets("Sheet1")
ExportDir = "C:\temp"
' A string/text with the export directory, should end with a \
NrPages = Sht.HPageBreaks.Count + 1
' Using the Sht object, count the number of horizontal page breaks and add one to know the number of pages
For p = 1 To NrPages
    ' Loop though the pages, actually the loop is only there as a kind of "counter"
    If p = 1 Then
        RwStart = 1
        ' The first page starts at the top and has no page break before it, so the row with your name is 1
    Else
        RwStart = Sht.HPageBreaks(p - 1).Location.Row
        ' Page 2 starts after Pagebreak 1, this code finds the row of the page break (=the row directly after it)
    End If
    
    FoundName = Sht.Range("B" & RwStart).Value
    ExportName = "Export_" & FoundName & "_" & p & ".pdf"
    ' Create a variable with the name in the sheet
    
    Sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ExportDir & ExportName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=p, to:=p, OpenAfterPublish:=False
    ' Export the current page with the current name to the default location
Next
Set Sht = Nothing
' Clean up variables
End Sub

 

[mole999]

Re: Modifying Marco to add Column B to file name when exporting

if you replicate this >
" & FoundName & " for your new name part that would work

 

[GingaNinga]

Re: Modifying Marco to add Column B to file name when exporting

Thank you, I am not very familiar with macros, so I am not sure exactly where I would add this comment.

 

[mole999]

Re: Modifying Marco to add Column B to file name when exporting

so where is the value you need to include ?

 

[GingaNinga]

Re: Modifying Marco to add Column B to file name when exporting

Column C of the same line that the value from Column B is included

 

[mole999]

Re: Modifying Marco to add Column B to file name when exporting

FoundName = Sht.Range("B" & RwStart).Value
FoundName2 = Sht.Range("C" & RwStart).Value
ExportName = "Export_" & FoundName & " " & FoundName2 & "_" & p & ".pdf"

 

[GingaNinga]

Re: Modifying Marco to add Column B to file name when exporting

This is great - however, I discovered that I now have a different problem. The file name that it is saving for example "West Manager" & "Employee One" (Values contained in Columns B &C) - does not match with the manager and employee name from the exported file.

How can I adjust this so that the file name is compiled from the values in Column B + Column C from directly above the page break.

 

[mole999]

Re: Modifying Marco to add Column B to file name when exporting

static page break is fine, don't know how you determine from code where they will be