Split file into multiple documents based on footer

[DME]

Hi all, my apologies as this is actually being used in a .Word doc, but hoping it's okay to post it here as perhaps this is something that someone may tackle in Excel.

I've created a large file using a mail merge. The large file is a combination of multiple 3pg sets of information, with each 3pg set specific to 1 employee and accompanied by a custom footer from the mail merge. My goal was to run a Macro to split that large file into each employees own 3 pages and use the footer to name each new PDF document that was generated. My code is below.

Something appears to be wrong with the ".SaveAs FileName :=fname.Text..." line of the code but I can't figure it out. Any suggestions or resources I can reference? Thanks so much in advance for your help!!

Sub NewMacroForTesting()
'
' NewMacroForTesting Macro
'
'
Dim i As Long, Source As Document, Target As Document, Letter As Range

Dim fname As Range

Set Source = ActiveDocument

With Source

    For i = 1 To .Sections.Count

        Set fname = .Sections(i).Footers(wdHeaderFooterPrimary).Range

        fname.End = fname.End - 1

        Set Letter = .Sections(i).Range

        Letter.End = Letter.End - 1

        Set Target = Documents.Add

        With Target

            .Range.FormattedText = Letter.FormattedText

            .Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = ""

            .SaveAs FileName:=fname.Text & " - Contributions", FileFormat:=wdFormatPDF

            .Close wdDoNotSaveChanges

        End With

    Next i

End With
End Sub

 

[Hui]

When you perform nail merge you have the option to do it as a single file or as separate files for each record

Why not redo it that way?

 

[DME]

@Hui good question- we had done that but were unable to name each pdf when creating them. As we created over 250 PDFs, we thought we’d try this. Any thoughts on an easy way to split out the sheets and name them in the merge/macro?

 

[Hui]

Do you still have the code that did the mail merge ?

 

[DME]

Hi @Hui, I do not. A colleague of mine used the Wizard to create the merge and now has the large document containing each set of individual documents. They’re being emailed to managers and we need the file name for each to be descriptive about what each file is. Any suggestions?

 

[Hui]

I look on a Word Forum
They may have such code to do what you want

 

[Dimmai Badsha]

U need to have merge field in your footer. That field will be the unique filename when autosaving the pdf's