none
Excel Macro Error, been working fine for years RRS feed

  • Question

  • Hi,

    I have a monthly report I run which outputs single sheet "Invoices" from my workbook that is populated by a months worth of sales. The macro goes through a list of names, pulls the details for that person, populates the pivot table on the invoice, then saves as both a single excel sheet and a pdf. up until today it has worked fine. the code is, and the error occurs when it reaches the date line. It states "Compile error, can't find project or library":

    Sub Save_Sheets_To_New_Books() 'INVOICES

    Const strWbPath As String = "D:\Accounts\EA Letters\2020 09 September\"

    Dim strDate As String 'todays date

    'save the sheets to new books within the active folder and print them

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    strDate = Format(Date, "yyyy.mm.dd")     '(Date, "yyyy.mm.dd") THIS IS WHERE THE ERROR OCCURS the word DATE is highlighted

    ActiveSheet.Copy

    Call DeleteNamedRanges

    Call PrintAreaAndPasteSpecial

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".pdf" _

            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _

            :=False, OpenAfterPublish:=False         

    ActiveWorkbook.SaveAs FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".xlsx"

    Application.DisplayAlerts = True

    'ActiveSheet.PrintOut Copies:=1, Collate:=True 'remove the comment if you want to print it out as well

    ActiveWorkbook.Close False

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

    End Sub

    Please could any offer any assistance.

    Many Thanks


    Tuesday, September 8, 2020 10:56 AM