Run-time 1004 error

Question

I had this working for a month, then it just stopped on me. I get a "Run-time error 1004. Application-defined or object-defined error". The debugger stops on the ActiveSheet.export.

Thank you in advance for anyone that can help.

Sub PrintReportsToPDF()

Sheets("Daily Report").Select

With Sheets("Maint Report").PageSetup

        .FitToPagesWide = 1 

        .FitToPagesTall = False 

        .Orientation = xlLandscape 

    End With 

With Sheets("Daily Report").PageSetup

        .FitToPagesWide = 1 

        .FitToPagesTall = 1 

        .Orientation = xlPortrait 

    End With 

Sheets(Array("Daily Report", "Maint Report")).Select 

Sheets("Daily Report").Activate 

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True 

End Sub

Answer 1

Hi JustinGJR!

Thank you for the feedback and you are highly welcome.

To include the current date in the filename when saving the PDF, you can use the Format function in VBA to format the current date in the desired format.

Please try this: Sub PrintReportsToPDF()

With Sheets("Maint Report"). PageSetup . FitToPagesWide = 1 . FitToPagesTall = False . Orientation = xlLandscape End With

With Sheets("Daily Report"). PageSetup . FitToPagesWide = 1 . FitToPagesTall = 1 . Orientation = xlPortrait End With

Dim sheetsArray() As Variant sheetsArray = Array("Daily Report", "Maint Report")

Sheets(sheetsArray). Select

Sheets("Daily Report"). Activate

Dim fileName As String fileName = "Daily Report " & Format(Date, "m_d_yy") & ".pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Path\to\your\output" & fileName, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=True

End Sub

In this updated code, the fileName variable is defined as a string that includes the desired prefix "Daily Report" followed by the formatted current date using the Format function. The format string "m_d_yy" specifies the month, day, and year in a two-digit format.

Make sure to adjust the file path in the Filename parameter of the ExportAsFixedFormat method according to your desired output location.

Best Regards, Shakiru

Answer 2

Thank you!!

I marked your last reply as "answered" so it won't let me reply to it. I really appreciate the help.

Answer 3

It works.

Thank you Shakiru!

One last question.

How do I get the name of the file to be "Daily Report 5_30_23" or the current date?

Answer 4

Hi JustinGJR!

The error you're encountering, "Run-time error 1004," typically occurs when there is an issue with the parameters or the range of cells being used.

Please try this: Sub PrintReportsToPDF() Dim wsDailyReport As Worksheet Dim wsMaintReport As Worksheet

Set wsDailyReport = ThisWorkbook.Sheets("Daily Report") Set wsMaintReport = ThisWorkbook.Sheets("Maint Report")

With wsMaintReport.PageSetup . FitToPagesWide = 1 . FitToPagesTall = False . Orientation = xlLandscape End With

With wsDailyReport.PageSetup . FitToPagesWide = 1 . FitToPagesTall = 1 . Orientation = xlPortrait End With

ThisWorkbook.Sheets(Array("Daily Report", "Maint Report")). Select

wsDailyReport.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Path\To\Your\PDF\File.pdf", _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=True End Sub

Note the following: * Double-check that the sheet names "Daily Report" and "Maint Report" exist in your workbook exactly as specified in the code.

* Remove unnecessary Select and Activate statements

* Specify the file name and path

* Make sure to replace "C:\Path\To\Your\PDF\File.pdf" with the actual desired file path and name for the resulting PDF file.

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru