Name extracted pdf files after a relative cell

Pteroglossus

New Member
Joined
Nov 19, 2020
Messages
3
Hi everyone,

I have a series of sheets separated by horizontal page breaks which I'm exporting to individual pdf files.

I managed to adapt the code here made for vertical page breaks: Save each page break as seperate PDF to work on my file.

The only problem I'm facing is the naming of files. I'd like to name the files after a Surname that appears in I3 cell for the first page, Q3 for the second, Z3 for the next and so on.

Here is my attempt:

VBA Code:
Sub exportPages()

Set Sht = Worksheets("Relevé mensuel")
' 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 = "Z:\"
' A string/text with the export directory, should end with a \
NrPages = Sht.VPageBreaks.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
        Columnwithname = Columns(9)
        ' The first page starts on the left and has no page break before it, so the column with your name is I
    Else
        Columnwithname = Sht.VPageBreaks(p - 1).Location.Column
        ' Page 2 starts after Pagebreak 1, this code finds the column of the page break (=the column directly after it)
    End If
    
    FoundName = Sht.Range(Columnwithname & Rows(3)).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

I run into an Runtime error 13 "Type Mismatch" at "FoundName = Sht.Range(Columnwithname & Rows(3)).Value."

The "Columnwithname" definition seems to work as you can see below, I simply don't manage to point to the third row.

Capture.PNG


Would you have an idea?

Thanks,
Kevin
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,129,811
Messages
5,638,488
Members
417,027
Latest member
wlknspc7

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
Top