Multiple workbooks Macro

[Phl1331]

I have changed my plans on my invoice file and have decided to create a new workbook for each month, name Customer Invoice - July, Customer Invoice - August, etc. Currently I have a macro that takes 4 specific fields of data from each invoice and puts it on the summary page. Now however, I want a complete YTD summary page that will take either all of the invoices from each book or all the data from the summary page of each workbook. Not sure where to start. Thanks for any help.

Currently my macro code is the following:

Please Login or Register  to view this content.

[boylejob]

Phil,

I believe I can help you again and I believe we can just add a little more code to what you have so a YTD file will be maintained as you create your month end summary.

I am still concerned about your total line. Will your totals from all your invoices ALWAYS fall on row 32. The example you posted showed an invoice with I believe 4 items on it, but what if there had been 10 items would the total still have been on row 32?

I will be working up your code for your YTD process.

[Phl1331]

I have built in about 12 lines for different items so there will never be more or less. a macro hides the empty ones when they hit print. so it will always be on line 32.

I forgot to give credit to you for this code above also. Thanks

[boylejob]

Phil,

Try this and see if it gives you what you are looking for. Of course you will need to update the file locations and the names to match your individual situation.

As you create the summary for a month it is also going to add the entries to the YTD record. I have only tested this briefly so you may need to play around with it.

Please Login or Register  to view this content.

Hope this helps!

[Phl1331]

Thanks, I haven't had a chance to try it yet because I've been side tracked...looking at the code though that looks like exactly what I want to do. I'll let you know though.

[boylejob]

Phil,

Great! I hope it does what you want.

I wanted to point out that this bit of code is set up on the basis that you will be doing your summary ONCE at the end of each month. Each time you run the program it will grab EVERYTHINGfor that month and move it to the month summary and the YTD summary. If the code were to be run at the middle of the month and the end of the month, then everything from the beginning to the middle of the month would end up being posted twice.

You probably already realize that, but I just wanted to make sure.

I look forward to hearing how it works for you!

[Phl1331]

Okay, what I get is everytime I run the macro "aaa" from the current month I am in, it writes the correct info in the "combined" sheet from that month and also in the YTD book, which is correct. Except if I save and close it and run a different month it simply overwrites what was already in the YTD book.

[Phl1331]

I have tried this over and over and over and it worked this last time. I think it has to do with the order i open save and close the books..not sure why though. I will keep testing in the morning. Thanks again for your help.

[boylejob]

Phil,

I am not sure why you are having the problem you are having. I have been unable to reprodue it on my end.

I would like for you to add this code right before the End Sub in the aaa sub. I am not sure if it will help your problem or not. It is a good idea to set the memory variable to NOTHING especially when you are running a number of subs. I'm not a real techie so I cannot tell you exacty what setting the variable to NOTHING is doing, but I know it is important.

Please Login or Register  to view this content.

Hope this helps. Let me know!