New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Auto Invoice VBA and VLOOKUP Don't Work

Macros, VBA, Excel Automation, etc.

Auto Invoice VBA and VLOOKUP Don't Work

Postby justr8hr » Sun Feb 05, 2017 12:52 am

Hello Experts,

I've been losing sleep because of these problems. I created an invoice template (macro-enabled) that is designed to be automated: meaning, you can increase/decrease invoice number, auto save features and auto-fill other data once a name is selected.

1) VBA to auto-save then create new xls file doesn't work - - always error something. I have created a button, supposed to be to make this run and easier for the user, who doesn't know much about Excel. Please let me know what's wrong

Code: Select all
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "C:kinahcareInv" & Range("G4").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub


2) The invoice itself, gets info from another sheet called 'clients'. I created dummy columns where the VLOOKUP results would go then, link it to the invoice. This too did not work! Please let me know what's wrong, too.

This is one of the VLOOKUPs I did to extract the Code Nr of the client

Code: Select all
[code]
=VLOOKUP(I2,clients,1,0)

Thank you in advance for your help!!
  • 0

You do not have the required permissions to view the files attached to this post.
justr8hr
Rookie
 
Posts: 5
Joined: Oct 28, 2013
Location: Philippines
Reputation: 0
Excel Version: 2013

Re: Auto Invoice VBA and VLOOKUP Don't Work

Postby Sisyphus » Sun Feb 19, 2017 3:17 am

Your file name isn't a valid file name. Try this:-
Code: Select all
NewFN = "C:\kinahcareInv\" & Range("G4").Value & ".xlsx"

Your code doesn't check if the folder exists, and if it doesn't even the technically corrected file name won't work. If kinahcareInv is part of the file name, rather than a folder, omit the back-slash that follows it.
I didn't test the rest of your code.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 11 guests

cron