Saving an invoice with an Invoice number and Customer Name

[mohammed_chand]

i have following code in my Module.
currently code saves invoices with invoice number. however i would like to add customer name with invoice number.
Invoice number is in D5 and customer name is in B11.
i would appreciate your help on this. how do i tweak my code to get customer name with invoice number.

Sub SaveInvoiceBothWaysAndClear()
Dim NewFN As Variant
' Create the PDF First
NewFN = "C:\Users\User\Desktop\invoice\" & Range("D5").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
' Next, Save the Excel File
ActiveSheet.Copy
NewFN = "C:\Users\User\Desktop\invoice\" & Range("D5").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Range("D5").Value = Range("D5").Value + 1
Range("A14:C23").ClearContents
Range("B11").ClearContents
Range("A11").ClearContents
ActiveWorkbook.Close SaveChanges:=True
End Sub

 

[Joe4]

Welcome to the Board!

Try changing this line in your code:

NewFN = "C:\Users\User\Desktop\invoice\" & Range("D5").Value & ".xlsx"

to this:

NewFN = "C:\Users\User\Desktop\invoice\" & Range("B11").Value & "_" & Range("D5").Value & ".xlsx"

If you want to "flip" the oder of Customer Name and Invoice Number, simply changing the "B11" and "D5" in the formula.

 

[mohammed_chand]

Is the code same for saving as PDF file?

 

[mohammed_chand]

Morning Joe

just tried the codes and it works.

Thank you for you help.

 

[mohammed_chand]

Hi

I am having issue saving PDF file. when changed the code from xlsx to pdf

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

this comes up highlighted.

can you please help?

regards

 

[mohammed_chand]

I think Error comes because of Data Validation. is there a way around this or will i have to delete the validation?

 

[Joe4]

I think Error comes because of Data Validation. is there a way around this or will i have to delete the validation?

I think we need a further description of this Data Validation. What is it doing, and when it is being incurred?

 

[mohammed_chand]

Afternoon
Cell B11 is for customer name which is drop down list of customers which are saved on sheet2.
hope this helps.

 

[Joe4]

Cell B11 is for customer name which is drop down list of customers which are saved on sheet2.

OK, but then what exactly is the issue then?
I don't think the Data Validation should have any affect on saving the file.
What exactly does the error message say?

Try putting this line of code just before the "Save" line of code, and tell me exactly what the message box returns:

MsgBox NewFN

I am thinking maybe you have an illegal character in the file save name.

 

[mohammed_chand]

Afternoon Joe
Thank you for your prompt reply.

i have just checked and found out that there is an "enter" new line in customer name.
example, bc contractors, london. i have it as
bc contractors
london.
so i think best option would be to use first 3 letters of customer name. do i have to amend any part of the code to use just first 3 letters of customer name?

regards