Click here to Skip to main content
15,613,716 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
I am having trouble find what the error is and how to correct it?
Sub InvoiceReport()
Dim myFile As String, lastRow As Long
myFile = "C:\Users\iaquino\Documents\Invoices\" & Sheets("Invoice").Range("A10") & "_" & Sheets("Invoice").Range("F4") & Format(Now(), "yyy-mm-dd") & ".pdf"
lastRow = Sheets("Invoice File").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
'Transfer data to Invoice File
Sheets("Invoice File").Cells(lastRow, 1) = Sheets("Invoice").Range("A10")
Sheets("Invoice File").Cells(lastRow, 2) = Sheets("Invoice").Range("F4")
Sheets("Invoice File").Cells(lastRow, 3) = Sheets("Invoice").Range("F28")
Sheets("Invoice File").Cells(lastRow, 4) = Now
Sheets("Invoice File").Hyperlinks.Add Anchor:=Sheets("Invoice File").Cells(lastRow, 5), Address:=myFile, TextToDisplay:=myFile
'Create invoice in PDF format
Sheets("Invoice").ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
Application.DisplayAlerts = False
'create invoice in XLSX format
ActiveWorkbook.SaveAs "C:\Users\iaquino\Documents\invoices\" & Sheets("Sheet1").Range("A10") & "_" & Sheets("Invoice").Range("F4") & "_" & Format(Now(), "yyy-mm-dd") & "xlsx", FileFormat:=51
Application.DisplayAlerts = True

End Sub
Updated 14-Sep-17 8:37am
Richard MacCutchan 9-Dec-15 15:28pm    
And? Is there a question, or some useful information in there somewhere?
MayurDighe 9-Dec-15 15:30pm    
Do you use "Debug" functionality to trace exactly where error occurs?

1 solution

There's few possible reasons of 1004 run-time error, but the most important is: you cannot open pdf in Excel application! Conclusion: you cannot read from pdf file!

On the first look:
1) source file hasn't been opened,
2) you did not set reference to a workbook (source file),
3) sheet does not exists or there are two or more opened files with the same sheet's name.

For further information, please see: Handle Run-Time Errors in VBA[^]
Share this answer
Member 12196950 9-Dec-15 16:40pm    
This is the first time I am using VBA but I was following a tutorial. I am suppose to be able to have a excel sheet with the info of the invoice from sheet 1 to sheet 2 where it created invoice as as a pdf so I can email or print.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900