Runtime Error 1004 with script to Save as PDF each worksheet.

  • Hi there, I got this particular code from a previous post here but, as the post was from 2014 I thought it may be best to start fresh.


    I have a workbook that contains around 40 sheets, and I need to PDF each of them seperately to different filepaths under different names.


    The code is as follows, where R1 contains the whole filepath + target name as these both change in each sheet.


    This Macro runs consistently for the first 5 sheets within the workbook before I am kicked out with the following error.


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.PNG","data-attachmentid":1220216}[/ATTACH]




    Anyone have any idea why this might be happening?


    Thanks in advance.

  • For one thing, what is the folder name? As for R1, it could contain the drive:\folder\basefilename part I guess? If the folder or basefilename part is an illegal filename, it would fail then too.


    Select is seldom needed. In this, change ActiveSheet to sh and remove the sh.Select line.

  • For one thing, what is the folder name? As for R1, it could contain the drive:\folder\basefilename part I guess? If the folder or basefilename part is an illegal filename, it would fail then too.


    Select is seldom needed. In this, change ActiveSheet to sh and remove the sh.Select line.


    I don't think the file path is an issue, as it is the same base file path across each of the other sheets.


    But I will try changing activesheet as suggested and see what happens!

  • Before the export, add this:

    Code
    Debug.print sh.name, fname & ".pdf"


    After the run, look in VBE's Immediate Window to see if valid drive:\folder\filename.ext was used. If no drive or folder is used, the current drive:\folder is used. This may not always be obvious where it gets put. Obviously, filenames with "" or "/" will be invalid as those designate folders. This is typically seen with some date string formatted values.

  • Before the export, add this:

    Code
    Debug.print sh.name, fname & ".pdf"


    After the run, look in VBE's Immediate Window to see if valid drive:\folder\filename.ext was used. If no drive or folder is used, the current drive:\folder is used. This may not always be obvious where it gets put. Obviously, filenames with "" or "/" will be invalid as those designate folders. This is typically seen with some date string formatted values.


    I've fixed it now, thank you for your help.


    The Debug script lead me to seeing that there were many hidden tabs that the macro could not PDF as the sheets were not set up with file paths. After deleting these the macro worked like a charm.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!