Loging invoices/quotes

[GYMMIC]

I am looking for a way to log every invoice/quote that is issued using excel. Currently we write the quote using a standard invoice template. we then take that information and manually input it into another work book that is kept as a log. I am looking for a way to automatically do this instead of having to manually enter the information, and would like for every entry to create a new line in the log worksheet.

I have limited coding experience, but I a willing to learn. Thanks!:think:


Any suggestions would be greatly appreciated. Thanks

[Ken Puls]

Hi GYMMIC, and welcome to VBAX!

Are you just wanting to keep number sequence? mdmackillop put together a KB entry for logging those: Autonumber invoices in a shared environment

If not, why don't you start by recording a macro. Do everything you normally would to manually copy it to the new book, then post the code here. We can modify it to make it more dynamic for you, and streamline the code. a

[GYMMIC]

OK..I saw the thread you suggested, that will enable me to generate a new transaction number everytime the file is opened. I know I will have to make a few changes because this file is not shared. Every User keeps their own quote log. I just had an Idea, but back to that later. (can each user that accesses this file be uniquely identified?)

Back to the log. I physically type in the name of the customer the time the request was received, and then the time that the request was sent back to the customer.
If the request is sent out to a suplier as a custom request, Our suplier will respond using a quote number they assign to our quote. We then input this number in our quote log so that we may keep record of it. Extra info I know. I appologize, I just want to make sure we are all on the same page. =)

This log is then used to run different reports, which I dont do, so i really dont care what is done with it. I just want to automate this log process.

On your macro suggestion, I understand what a macro is. But i do not know how to make one. =(

I appreciate your help.

Thanks

[Ken Puls]

OK..I saw the thread you suggested, that will enable me to generate a new transaction number everytime the file is opened. I know I will have to make a few changes because this file is not shared. Every User keeps their own quote log. I just had an Idea, but back to that later. (can each user that accesses this file be uniquely identified?)

Sure every user can be identified. There's a couple of ways to approach this, and it depends on how complex your environment is which approach should be taken. You can identify a user by their Windows login name, their Computer's name, or their Microsoft Office name. Each has it's own pros and cons, but I'd probably look into the rest of the code first.


On your macro suggestion, I understand what a macro is. But i do not know how to make one. =(

Okay, no problem. The first thing you'll want to do is work out the actions you want to take. Run through it a couple of times to make sure you know exactly what it is, and that you know the exact steps you want the macro to follow. Then, go to the Tools menu, and choose Macro|Record New Macro.

A little box will pop up. You can leave the default name as Macro1, but change the workbook to Personal Macro Workbook, then click okay. The box will dissappear. You should see a new (floating) toolbar pop up with a little blue square in it. Just ignore it for now.

Perform the actions that you want to record. Try to make sure that you do them all without extra steps, but don't worry if you do. We're going to modify the code later.

Once you have finished, click the little blue square on the new toolbar to stop recording. (or go to Tools|Macro|Stop Recording)

That's the first step in creating your macro. You've just recorded the basic framework you want to start with. :)

Now we're going to find the code and post it here so that we can tweak it to do what we really want.... (open up a specific workbook, always paste your data to the last row, retrieve a quote number from another workbook, etc...)

First, press Alt+F11, and you will see a new program open up. This is the Visual Basic Editor, where all your code is written. Press CTRL+R. This will open up the "project explorer" which looks like a windows navigation tree. If nothing happens, don't worry about it as it was probably already open.

In the project explorer, find VBA Project (Personal.xls) and double click it. (This is the Personal macro workbook). Browse through Personal.xls until you find Module1 and double click that. In the pane on the right, you should see a bunch of code listed there. It will start something like


Sub Macro1()
' Macro1 Macro
' Macro recorded 26/04/2005 by You
'...some recorded code here
'
End Sub

Copy all of that and paste it here. We'll clean it up first, then we'll tweak it to do the extra things we need. :thumb

FYI, if you want to get a bit more familiar with the VBE, you may want to check out VBAX's Excel VBA Training & Certification (http://www.vbaexpress.com/training.htm) course. Lesson 1 is free, and it's worth checking out. :yes

[GYMMIC]

Ken,
Here is a very simplified version of what I would normally do. This is just one transaction. copying and pasting just one entry. The two sheets are called invoice and quote_log.



Sub Macro1()
'' Macro1 Macro
' Macro recorded 4/27/2005 by Management Company
Range("D13:H13").Select
ActiveCell.FormulaR1C1 = "Hayward Lumber"
Sheets("Quote_log").Select
Range("E6").Select
ActiveCell.FormulaR1C1 = "Hayward Lumber "
Sheets("Invoice").Select
Range("D14:H14").Select
ActiveCell.FormulaR1C1 = "Bernie"
Sheets("Quote_log").Select
Range("F6").Select
ActiveCell.FormulaR1C1 = "Bernie"
Sheets("Invoice").Select
Range("M14").Select
ActiveCell.FormulaR1C1 = "316840"
Sheets("Quote_log").Select
Range("G6").Select
ActiveCell.FormulaR1C1 = "316840"
Sheets("Invoice").Select
Range("D16:H16").Select
ActiveCell.FormulaR1C1 = "Simpson"
Sheets("Quote_log").Select
Range("H6").Select
ActiveCell.FormulaR1C1 = "Simpson"
Sheets("Invoice").Select
End Sub

[Ken Puls]

Okay, can you clarify something for me?

You've entered indentical data in Columns D:H on the "Invoice" sheet. I assume that this will be done manually, and then you'll want to transfer the data to the Quote_Log sheet, is that right?

Another assumption is that you want to only transfer the value in the first column (D), yes, no?

FYI, here is a cleaned up version of your code, with all the "selects" and "activecells" taken out as they aren't necessary (the recored picks up a bunch of extra stuff we don't need):


Sub Macro1()
Sheets("Invoice").Range("D13:H13") = "Hayward Lumber"
Sheets("Quote_log").Range("E6") = "Hayward Lumber "
Sheets("Invoice").Range("D14:H14") = "Bernie"
Sheets("Quote_log").Range("F6") = "Bernie"
Sheets("Invoice").Range("M14") = "316840"
Sheets("Quote_log").Range("G6") = "316840"
Sheets("Invoice").Range("D16:H16") = "Simpson"
Sheets("Quote_log").Range("H6") = "Simpson"
Sheets("Invoice").Select
End Sub

[GYMMIC]

You've entered indentical data in Columns D:H on the "Invoice" sheet. I assume that this will be done manually, and then you'll want to transfer the data to the Quote_Log sheet, is that right?


In the spreadsheet these cells are merged, in this particular area. I am enclosing the files that we are working on. So that we can both see whats going on. I also noticed that where the macro shows the ranges, it is really a merged area of the spread sheet. Also, the reason that they show up as ranges is because when I was recording the macro, I highlighted the information, cut it, then pasted it on the quote log. HTH. Please note that there is some sensitive information on these sheets.. Thanks

:bow: THANKS FOR YOUR HELP

[lucas]

Gymmic,
Not sure what your trying to do, try this. I unmerged your cells first of all. you can type in the first cell on the left and it will go across the other cells. Easier to copy to another sheet that way. I may not understand what your trying to do but I recorded this with the macro recorder. still needs tweaking if its along the lines of what your trying to accomplish.

[Ken Puls]

Hey Gymmic,

Although it's not necessary to make this macro work, I totally agree with Lucas. In my opinion, merged cells are the Devil's gift to Excel, and I avoid using them at all costs. They cause problems when trying to insert columns, or rows, among other things, and can cause tsome iritating errors with VBA. In the case you have, you shouldn't need them, since info all fills left to right anyway. If you want to center things across a range of cells, look up "center accross selection". It's a much better alternative.

Okay back to the macro. As mentioned, this will work with or without merged cells. What I've done is cut the macro down to two lines of code (it looks like 4 but I'll explain), and it will work quite quickly. It works under the assumption that you will fill in the quote detail, then execute the "Transfer" macro in some manner. Here's the code:


Sub Transfer()
Sheets("Quote_log").Range("E65536").End(xlUp).Offset(1, 0).Resize(1, 4) = _
WorksheetFunction.Transpose(Sheets("Invoice").Range("D13:D16"))
Sheets("Quote_Log").Range("E65536").End(xlUp).Offset(0, 2) = _
Sheets("Invoice").Range("M14")
End Sub

Okay, so now on to what it does... In summary, it copies range D13:D16 from the Invoice page and rotates it to fit in the next available block of cells in Columns E:H. This will also slap those values into the next available row. It then copies the quote number in to that row, since the original block copied first does put in the city, which we don't care about.

The how:
Sheets("Quote_log").Range("E65536").End(xlUp)
-this is equivalent to going to the last cell in column E and pressing End and the up arrow to get to the last used cell in column E. For now, let's pretend that E5 has data, but E6 does not.
.Offset(1, 0)
-this moves the focus to the next row. So know we're dealing with cell E6
.Resize(1, 4)
-this changes the size of the range to work on from a range that is one row by one column to one row by four columns. So now we have range E6:H6
= _
-we're going to set the value of the range E6:H6 to something, which is where the = sign fits in. The next part is actually two characters: a space and a underscore. This is a line continuation, which is only used to make it easier to read your code. You must have a space before the underscore, though or VBA won't like it. So basically, this just means that the line I broke down above, and the row that we're coming to are interpreted as one line of code by VBA.
WorksheetFunction.Transpose(Sheets("Invoice").Range("D13:D16"))
-so this is what we're setting E6:H6 equal to. In this case, we want to fill it with the values of the range D13:D16, but we want to flip that range sideways. To do this, we use Excel's worksheet Transpose function, since VBA does not have a transpose funciton of its own. If you're not familiar with Transpose, it is a regular Excel formula that is documented in the help files.

I'll leave most of the audit of the next part to you, except to explain this part:
.Offset(0, 2)
-In the last Offset method, we went down one row (1,0). In this one, we stay in the same row, but move 2 columns to the right (0,2). The reason is that we've already pasted data to the next available row in the first step. This means that we need to change our focus when running our next line, as the line we want to focus on now DOES have data in it.

I hope this all makes some sense, but if not, feel free to ask!

Try copying the above code into your project and giving it a run. See if it's on the right track.

Cheers!

[GYMMIC]

Ken, this is working like a charm..
let me catch you up to speed of where i am at. I have a ssigned a special macor button to execute the transfer, on the tool bar. I need to make sure that the code you have given me also puts the date in the log. So I am going to re-run a macro, and record it. And try to make it do what i need it to do....

Now a question. I need to add the date to the code above. The date needed is in cell M13.


Sheets("Quote_log").Range("E65536").End(xlUp).Offset(1, 0).Resize(1, 4) = _
WorksheetFunction.Transpose(Sheets("Invoice").Range("D13:16"))

This part grabs the information from d13-d16, i also need the information from M13, would it be correct to do this:


Sheets("Quote_Log").Range("E65536").End(xlUp).Offset(0, -4) = _
Sheets("Invoice").Range("M13")

This ofcourse is assuming that making the y-intiger negative will make it move to the left.


Another question that pops into mind is , when i save the quote to something other than this template. this log is attached to the current sessions, right? Meaning if I save this under save as hayward lumber... when i open the template, for a new quote, will the information that was input to the log by the macro be there? I am assuming that the answer would be no. And hoping it is yes. But to sidestep this issue, how can I reword the code so that the information is saved on a file outside of the current workbook?:think:

[johnske]

...... But to sidestep this issue, how can I reword the code so that the information is saved on a file outside of the current workbook?:think:

Hi GYMMIC,

You could try adapting something like this (http://www.vbaexpress.com/kb/getarticle.php?kb_id=349) kb entry by Lucas to suit your needs

HTH,
John

[Ken Puls]

Hi Gymmic,

I haven't looked at the template, but you have the offset method reasoned out correctly. A negative number in the second column will move you left. :yes

Take a look at the entry John just linked for maitaining your info in another workbook. The code we've got so far just really needs to be wrapped in something which:

-opens the workbook that holds the info
-pastes the info to the end (that's the code we have)
-saves and closes the workbook.

We may be able to make it do it very easily, and avoid some of Lucas's code. I'll take a look at it a bit later to throw something together if you don't get there first.

[GYMMIC]

Thanks for your suggestions, I am having trouble understanding the logic on the suggestion from john's link.. I am new to this. So alot of what is going on is very confusing.

Working the code the way you did explained it in terms i was able to understand what was really going on. If you get the time to break that suggestion down like you did before would help me greatly. Thanks for your time and passion.

:clap: :friends:

[Ken Puls]

Sorry, Gymmic!



Wrote this up last night and tried to post it, but my ISP decided that I didn't need internet access last night (again). :steamed: My ADSL connection has just "gone out" on me for hours at a time this week, and last night was off during the time I use the net the most.

At any rate, I wrote up a reply for you, and saved it (cause I've been this route with them before), so here it is!


***
Hi Gymmic,

Okay, let's give this a shot. First thing, we need to copy your "Quote_Log" sheet into a new file. I'm assuming that you'll call it log.xls, and store it in the C:\Temp folder. If not though, you can adjust the code accordingly. I've also made some other changes, which I'll explain as well.


Sub Transfer()
Dim wbTarget As Workbook, wsSource As Worksheet, wsTarget As Worksheet
'Turn off screen flashing, and open the workbook to paste the records in
Application.ScreenUpdating = False
Set wsSource = ActiveWorkbook.Sheets("Invoice")
Set wbTarget = Workbooks.Open("C:\Temp\log.xls")
Set wsTarget = wbTarget.Sheets("Quote_log")
'Paste the records to Log workbook
wsTarget.Range("E65536").End(xlUp).Offset(1, 0).Resize(1, 4) = _
WorksheetFunction.Transpose(wsSource.Range("D13:D16"))
wsTarget.Range("E65536").End(xlUp).Offset(0, 2) = wsSource.Range("M14")
wsTarget.Range("E65536").End(xlUp).Offset(0, -4) = wsSource.Range("M13")
'Close the target workbook saving the changes
wbTarget.Close savechanges:=True
'Turn Screen updates back on
Application.ScreenUpdating = True
End Sub

Okay, now here's the breakdown:

First I turned off screen updating, so that you don't have to watch the other workbook open, then close.


Set wsSource = ActiveWorkbook.Sheets("Invoice")
Set wbTarget = Workbooks.Open("C:\Temp\log.xls")
Set wsTarget = wbTarget.Sheets("Quote_log")

This section does a couple of things. First, it assign certain items to variables, which can save us some typing. This is pretty handy, because we do want to fully qualify our code as much as possible, but we can type a little less.

The first line sets wsSource equal to the Activeworkbook (your template)'s "invoice" page. This means we can just type "wsSource" instead of Activeworkbook.Sheets("Invoice") throughout the code. We didn't use the Activeworkbook qualifier before, but we need to be careful now, because we are going to open a second file. That's actually why I set this variable before I opened the other workbook, because the value of the variable won't change after I set it, but the sheet that is active may.

Set wbTarget opens a the Log.xls file, and set it to the wbTarget variable. From now on, we can use "wbTarget" anywhere we would normally type "Workbooks("log.xls")"... like in the next line...

See how I subbed in the wbTarget in the Set statement? This basically means that wsTarget is referring to Workbooks("log.xls").Worksheets("Quote_log")

All right, on to the next section. I changed it to make use of the variables we set above. This accomplishes two things. First it makes the code shorter and a bit more readable. Second, it makes it a bit faster. Anywhere you are going to use the exact same objects over and over again (like Workbooks("log.xls").Worksheets("Quote_log")), assigning it to a variable saves VBA looking it up every time. If VBA was a person it would be saying "I know that sheet", rather than saying "I'll look in that book, and oh! There's that sheet" every time. Make sense?

It does one more handy thing, too. Say you change the name of the log sheet to "mylog". Just go back to the Set statement and update it... ONCE. No need to do it multiple times throughout the code.

Finally, we close the wbTarget (the log file) telling it to save changes. Then we turn screen updating back on.

That's pretty much it!

The log file can actually be used by more than one person, if you store it on the network, although you'd want to distinguish between the "loggers". While it would be open for a short time, depending on how many users there were, it could occasionally run into a sharing conflict. The lower the number of users, the less likely though, but it is somethign to consider.

Cheers,

[GYMMIC]

Ken... I can actually wark my thread Solved... You are awesome... Thanks a billion!!!!:hi: :clap: :clap: :friends: :thumb :beerchug: :beerchug: :cool: :bow: :bow:

[Ken Puls]

Hi Gymmic,

You're welcome!

:beerchug: