save sheets as PDF

[Arie Bos]

I have a workbook with 20 sheets with names starting with "LP", so LP01, LP02, LP03 ...LP20. There are also 4 other sheets with other names, not in the LP range.

I have the code below to save all sheets as PDF with names as follows: "B-II [18_05] LP01. John.pdf", "B-II [18_05] LP02. Peter.pdf", and so on until LP20.
Sht.Range("Z11") retrieves the sheet number (LP01, LP02) in cell I4, and Sht.Range("I4") retrieves the name (John, Peter).

The code starts OK, and creates the first pdf in the right location for LP01, John, but then hangs at "Sht.ExportAsFixedFormat 0, Fname:" with error 1004.

Why would the code stop after the first pdf is created successfully?

Sub Save_all_Reports()Dim Fname As String
Dim Sht As Worksheet
    For Each Sht In ActiveWorkbook.Sheets
        If UCase(Left(Sht.Name, 2)) = "LP" Then    
        Fname = "C:\Users\User\Dropbox\BCM\BFO\B-II\Monthly Reports\18_05\" & "B-II [18_05] " & Sht.Range("Z11") & ". " & Sht.Range("i4") & ".pdf"
        Sht.ExportAsFixedFormat 0, Fname
            End If
    Next Sht
End Sub

 

[Coding4Fun]

This worked for me, I changed the path to my desktop and set up 5 sheets all of which were exported and saved to my desktop with the appropriate names.

Sub Save_all_Reports()

Dim Fname As String

    For s = 1 To ActiveWorkbook.Sheets.Count
    
        If UCase(Left(Sheets(s).Name, 2)) = "LP" Then
        Fname = "C:\Users\User\Dropbox\BCM\BFO\B-II\Monthly Reports\18_05\" & "B-II [18_05] " & Sheets(s).Range("Z11") & ". " & Sheets(s).Range("I4") & ".pdf"
        Sheets(s).ExportAsFixedFormat 0, Fname
            End If
    Next s
    
End Sub

I am not positive if it is the problem or not but I suspect the issue you were running into was in your

[COLOR=#ff0000]For Each Sht in ActiveWorkbook.[B]Sheets[/B][/COLOR]

I dont think this allows you to loop through the sheets, but I could be wrong.

 

[Arie Bos]

Thanks Coding4fun,

tied your piece of code, but same error results, now even before the first pdf is created. May be something else is wrong with the file... Need to dig deeper...

 

[Arie Bos]

Got the path wrong,, but after correction it still hangs at Sheets(s).ExportAsFixedFormat 0, Fname.
I don't get this...

 

[Coding4Fun]

Try this, if this works then it will point to an issue within your file specifically or possibly other code that is also running.

Create a new workbook.
Add 5 Sheets
Name each sheet as LP01, LP02, LP03, LP04, LP05
Update Cell I4 of all 5 sheets to reflect a name - In my example I just used my name with the sheet number (Coding4Fun1, Coding4Fun2 Coding4Fun3 etc.)
Update Cell Z11 of all 5 sheets to reflect the sheets name - LP01, LP02, LP03 etc.

Go Into VBA Editor, add a module to the project and then paste the code provided and make sure the path is updated to point to the correct location. As another test I would change the path to point to your desktop, see if you can get them to save to your desktop and if that is successful then change the path back to your Dropbox location. If saving to your desktop works but Dropbox causes error then that would point to an issue with saving to Dropbox.

Now Save this workbook somewhere you can find it and make sure you save it as .XLSM and then go back in and run this code.

Sub Save_all_Reports()

Dim Fname As String

    For s = 1 To ActiveWorkbook.Sheets.Count
    
        If UCase(Left(Sheets(s).Name, 2)) = "LP" Then
        Fname = "C:\Users\User\Dropbox\BCM\BFO\B-II\Monthly Reports\18_05\" & "B-II [18_05] " & Sheets(s).Range("Z11") & ". " & Sheets(s).Range("I4") & ".pdf"
        Sheets(s).ExportAsFixedFormat 0, Fname
            End If
    Next s
    
End Sub

Let me know what the outcome of this is.

 

[Arie Bos]

Hi Coding4Fun,

yes this one worked indeed.

I think there is an error in this workbook. strange, I have a similar one for B-I project and that does work with the original code. Excel miracles....

Arie