The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

Excel 2007 Invoice Help

(2 posts)
  • Started 4 years ago by egreenberg
  • Latest reply from moreeg
  • Topic Viewed 577 times

Posts: 1

I use a workbook to keep track of my expenses at work and i would like to do more with it. I started with just wanting to be better with excel but it turned into this whole project that I've become pretty proud of for a novice. So I've tricked out my purchase order and PO list pretty extensively for a novice but now i would like to create a VBA project for it.
Basically I have multiple worksheets, the first worksheet is the invoice, the second is a list of all the PO#'s used and available as well as information corresponding to the used PO#'s. The PO# list consists of (in order) PO number, Purchase date, Vendor, Payment, Total Amount. What I would like to do is have the purchase order in the first sheet, find the next available PO#, populate, and then take the info from the purchase order and save it to that PO number on the PO list worksheet.
Can anyone help? You will get a thousand internetz and you will be forever in Google's favor.

Posted 4 years ago
Posts: 842

Hi egreenberg

Sounds like an interesting project but I'm not sure if a 1000 internetz and Google's favours are a reward or a curse. Nevertheless, embarking on your first VBA project can be daunting and there are several people in this forum who are expert at it but you will need to provide very detailed information about the format of all the sheets involved. But I'll start off with some general advice on one (of very many) ways to proceed.

This is a bare bones macro that will get your first unused PO# assuming that the Purchase date cell is in column B and is empty;

Sub GetPONumber()
ActiveWorkbook.Worksheets("sheet2").Range("B65536").End(xlUp).Offset(0, -1).Copy
End Sub

It will copy the PO# from Sheet2 (substitute your PO sheet name) and paste it into Cell A5 in Sheet1 (substitute your target cell and Invoice worksheet name)

You can then either use similar code to copy the target cells from your Invoice sheet and place them in the PO sheet or you can use normal excel functions like vlookup or index to to get the values from the Invoice to the PO sheet using the PO# as the reference.

Give this some thought and come back with any questions at all that you may have. We are always happy to help a budding Excel fanatic.

Posted 4 years ago

Topic Closed

This topic has been closed to new replies.