Modifying Macro to add Column B to file name when exporting

GingaNinga

New Member
Joined
Sep 1, 2017
Messages
5
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:

Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Sep 1, 2017
Messages
5
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Re: Modifying Marco to add Column B to file name when exporting

so where is the value you need to include ?
 

GingaNinga

New Member
Joined
Sep 1, 2017
Messages
5

ADVERTISEMENT

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Sep 1, 2017
Messages
5
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,276
Messages
5,527,732
Members
409,786
Latest member
AbdulMoix

This Week's Hot Topics

Top