Using Excel VBA to create invoices

MikeyW1969

Board Regular
Joined
Apr 28, 2014
Messages
80
Hi all, I am trying to create invoices for some freelance work I am doing. Here is my plan:

I plan to create a document in Excel that will have a starting Invoice number. I will have a button to generate an invoice, auto incrementing the invoice number each time, and then saving the file as "Invoice[insert invoice number here].xslx". I would like to have this live in the cloud, either on Dropbox or Google Drive so that I can generate an invoice any time I need to.

it looks like this code should work for incrementing the invoice number(Of course, I will actualkly edit the fields to clear from the last invoice as well as update the field that will have the invoice number in it):
Code:
Sub NextInvoice()
    Range(“E5”).Value = Range(“E5”).Value + 1
    Range(“A20:E39”).ClearContents
End Sub
And then this code should save it where I need to:
Code:
Sub SaveInvWithNewName()
    Dim NewFN As Variant
    ‘ Copy Invoice to a new workbook
    ActiveSheet.Copy
    NewFN = “C:aaaInv” & Range(“E5”).Value & “.xlsx”
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub
So my questions are this:
1. How do these look for what I want to do? At the moment, they are just what I found on the internet, I can make them simpler if there is a better way, or one that is more recommended.
2. What's the best way to clear fields all over the place? For example; I want to clear fields A8-A18 and then A16-A30, as well as F16:30 and G16:30. Would I just do it like this(Putting this into the first code example above)?
Code:
Range(“A8:A18”).ClearContents
Range(“A16:A30”).ClearContents
Range(“F16:F30”).ClearContents
Range(“G16:G30”).ClearContents
...or is there a cleaner way to do this?
3. With the NewFN command, if I understand it right from the example, the “C:aaaInv” part is the path. Is that correct? If I wanted(for some weird reason) to save it to the root of the C drive, is that how I would do it, and then the 'Windows' folder would be "C:\Windows"? Or is that just where I put the file name? I would like the file name to be "Invoice{Invoice Number}", maybe with the date and client name. Currently A8 is the Client name and H5 is the date. F5 is the invoice number. And what would the path be if I wanted to save it to the cloud? Does Google Drive end up with just a drive name, or a path? I'm guessing the "Range(“E5”)" part of it is where I would get info such as my invoice number, is that correct? In other words, it would be "Invoice" & Range(“F5”) & "_" & Range(“A8”) & "_" & Range(“H5”).xslx , is that correct for it to look like this: Invoice2012_Client_Oct92018.xslx ? I know the date will be formatted differently, but I'm not sure how that looks at the moment.

Anyway, any help would be greatly appreciated. I think I'm pretty close here, just want to make sure that I'm on the right track.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
EDIT: I have moved in a different direction. I no longer need help on this, but I cannot figure out how to delete this post.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top