Modifying Macro to add Column B to file name when exporting

GingaNinga

New Member
Joined
Sep 1, 2017
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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.
 
Upvote 0
Re: Modifying Marco to add Column B to file name when exporting

so where is the value you need to include ?
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top