Can anyone help with VBA, please?
I am trying to get my main invoice data onto another worksheet using a button. I have added what I thought was the correct VBA code but it doesn't seem to run properly.
And as it won't put anything in the first cell, it won't add more record.
I have added a button but that just shows the error each time. I think the problem is that the invoice number is not numeric but a combination of text and cell numbers.
I have attached the xlsm file so you can see the invoice and the VBA part. Our invoice "number" is made up from company name, dates plus the initials of the people involved.
Thanks in advance
Chris
Try the attached.
I converted all the tables to Excel Built-in Tables and added some named ranges.
Code assigned to the button, via click event, is
Sub NewInvoice()
With InvLog.ListObjects(1)
If .ListRows(1).Range.Columns(1) = vbNullString Then
.ListRows(1).Range.Resize(, 4) = Array([invno], [InvTo], [InvTot], [InvDt])
Else
.ListRows.Add
.ListRows(.ListRows.Count).Range.Resize(, 4) = Array([invno], [InvTo], [InvTot], [InvDt])
End If
End With
End Sub
Good afternoon Charles
Thank you so much for your reply and script.
I played around with it - silly me - and in the end got myself in a real twist and had to start again.
The main reason was that I could not save your xlsm file - No, No and thrice NO, said my computer!
So I started again and have managed to be get close to what I want/need.
Would you be able to have a look at what I have done and suggest any alterations etc.
This is what started my "quest"
How to Create an Automated Invoice in Excel | Including Formulas and Customer Database
https://www.youtube.com/watch?v=T5dRVGbn7-Q&list=PLA3JEasWtYad0OeX78k0gFhxm5qnnaD57&index=1
but we do not need it all - just the save an invoice as an exel file, save the invoice as a PDF and email and save the invoice - from buttons on the invoice page.
I have attached my new workings (hope you can save it).
The vba I have managed to do for two of the three elements but they only work when I run them individually.
I haqve also attached a word doc with scripts that I think work separately.
If you can help, perhaps you might be able to email the script for me to add (emil address is on School or LA tab - I have used my own email for each school finance department as we are not ready to go live yet.
If it would help, perhaps we could do a Zoom chat. I would be happy to reimburse you for your efforts.
Thanks
Chris
