Run-time error - "Document not saved"

[Jasesair]

I have the below code and am getting a bit of a foreign Run-Time Error. The error is highlighting the last line. Any assistance to resolve would be very much appreciated.

The Run-Time Error specifically says:

Run-time error '-2147024772 (8008007b)':
Document not saved.

Sub PDFpack()


     Dim inputRange As Range
     Dim c As Range
     Dim strFilename As String
    
     Set inputRange = Evaluate(Range("d3").Validation.Formula1)
              
        For Each c In inputRange
       
            Range("d3").Value = c.Value
      
       With Sheets("Season Summary")
        .PageSetup.Orientation = xlLandscape
    End With
    strFilename = Sheets("Season Summary").Range("Q3") & " as at " & Format(Date, "d-mm-yyyy")
    Set inputRange = Sheets("Season Summary").Range("A1:v39")
   
        Next c
    inputRange.ExportAsFixedFormat Filename:=strFilename, Type:=xlTypePDF, OpenAfterPublish:=True


End Sub

 

[Alex Blakenburg]

I don't know whether you tried to simplify the code for posting but it doesn't make a whole lot of sense.
You have the output lines hard coded "inside" the loop which means that the loop does nothing.
The file creation line is after the loop so it is only going to create the output based on the last value.

The error message is almost certainly related to an invalid FULL FILENAME ie Path (which should end in "\") & Filename.

We can't see your cells that are being used to generate the filename. What you can do is before your last line add the line:-

Debug.Print strFilename

Have a look in the immediate window and see if what prints there look like a valid full filename.

If you can't tell by looking at it, try copying the path part into the file explorer address box or an excel open file address box and see if it can find it.
Or post what produces here so we can have a look.

 

[Jasesair]

Hi Alex, I've fixed the error - there was a colon in a referenced cell. Thanks for the heads up. You're also right that the code isn't correct with lines clearly not in the right order. I'm looping through everything in a data validation list in Cell D3 and am wanting to PFD the range A1:V39 each time it's looped, and then combine each PDF into the one large PDF. If you can let me know where I've gone wrong, that'd be fantastic.

The new code is below, and it's only currently PDFing the last range that's looped.

Sub PDFpack()


     Dim inputRange As Range
     Dim c As Range
     Dim strFilename As String
    
     Set inputRange = Evaluate(Range("d3").Validation.Formula1)
              
        For Each c In inputRange
       
            Range("d3").Value = c.Value
      
       With Sheets("Season Summary")
        .PageSetup.Orientation = xlLandscape
    End With
    strFilename = "All Summaries" & " as at " & Format(Date, "d-mm-yyyy")
    Set inputRange = Sheets("Season Summary").Range("A1:v39")
   
        Next c
    inputRange.ExportAsFixedFormat Filename:=strFilename, Type:=xlTypePDF, OpenAfterPublish:=True


End Sub

 

[Alex Blakenburg]

What is the loop meant to be doing ?
It is not currently changing anything except what is in D3.

Are you perhaps calling a Worksheet.Change Event macro ?

 

[Jasesair]

What is the loop meant to be doing ?
It is not currently changing anything except what is in D3.

Are you perhaps calling a Worksheet.Change Event macro ?

Yes, that's correct - the loop is changing what's in D3. When D3 changes, plenty of other cell formulas are changing based on what's in D3. I would like it to PDF the range each time (the range is a full page), and then produce lots of pages in the one PDF.

There's nothing being called at all.

 

[Alex Blakenburg]

Try this.
Just keep an eye on that all the changes you are expecting to flow through from changing D3 make it all the way to the PDF file.

Sub LoopCreatePDFMultiPage()

    Dim inputRange As Range
    Dim c As Range
    Dim strFilename As String
    
    Dim outWB As Workbook
    Dim outSh As Worksheet
    Dim outNames() As String
    Dim srcWB As Workbook
    Dim srcSh As Worksheet
    Dim srcPrintRng As Range
    Dim i As Long

    Set srcWB = ThisWorkbook
    Set srcSh = ActiveSheet
    
    Set inputRange = Evaluate(srcSh.Range("d3").Validation.Formula1)
    Set srcPrintRng = Sheets("Season Summary").Range("A1:v39")
        
    ' Create Output Workbook
    Set outWB = Workbooks.Add
    
    ' Loop through change of settings
    ' Change Setting output updated sheet to output workbook
        For Each c In inputRange
       
            srcSh.Range("d3").Value = c.Value
            If Application.CalculationState <> xlDone Then Application.Calculate

            Do While Application.CalculationState <> xlDone
                DoEvents
            Loop
            
            ' Copy sheet to new sheet in new workbook
            Set outSh = outWB.Sheets.Add(After:=Sheets(Sheets.Count))
            
            srcPrintRng.Copy
            outSh.Range("A1").PasteSpecial Paste:=xlPasteValues
            outSh.Range("A1").PasteSpecial Paste:=xlPasteFormats
            
            With outSh           ' Change to new workbook and new sheet
                .PageSetup.Orientation = xlLandscape
            End With
   
        Next c
        ' ------------------------
        ' Print to PDF
        ' ------------------------
        outWB.Activate
        
        Application.DisplayAlerts = False
        outWB.Worksheets(1).Delete
        Application.DisplayAlerts = True
        
        ReDim outNames(1 To outWB.Worksheets.Count)
        For i = 1 To outWB.Worksheets.Count
            outWB.Worksheets(i).Select False
        Next i
        
        strFilename = "All Summaries" & " as at " & Format(Date, "d-mm-yyyy")
        ActiveSheet.ExportAsFixedFormat Filename:=strFilename, Type:=xlTypePDF, OpenAfterPublish:=True
        
        outWB.Worksheets(1).Select
        
        outWB.Close savechanges:=False

End Sub