Create new workbook, module and code using vba

Hi, I am new to the forum and working with vba code. I admit most of my work to date has been sourced from google searches and self leaning how to write code. I therefore consider myself a complete novice so appreciate any help.

Now, my project is to make a basic invoice sheet with vba code that copies the template layout to a new workbook and saves it as a new file name. Additionally, I have code for incrementing the invoice number by 1 and clearing the cells with the details so the template layout is ready for the next invoice. I also have code that copies some cells to another workbook, named accounts showing some invoice details such as invoice number, name and date.

Now my problem is when my new workbook is created I obviously then have a saved record of the invoice but without any of the code from the originating file which created it. I have managed to write into my code a way of generating a new module in the new workbook but cannot figure out how to add code to this new module from with the original workbook. I hope not to sound too confusing, but what I need is to generate a new workbook, module and code using vba to execute this. The reason I need to add this code to the newly created workbook is so that any edits to the information entered can be updated to the accounts list.

So this leads to my next question. Currently I am able to copy cells to a new workbook (accounts) and enter the information to the next clear row. But, how do I add a check to see if the invoice number is already present in my accounts workbook and if so replace the row instead of adding it to the next clear row. This is the code which I will use in my newly created workbook / module thus allowing me to edit the new workbook and update the accounts workbook.

So, the code I have so far is as follows:-

Please Login or Register  to view this content.

I appreciate any guidance / changing my code anyone on the forum can help me with,
Many thanks
Andy

Hi pgm

Welcome to the Forum!!!

First things first...please add Code Tags to all posted code (see Forum Rule 3 on how to do so).

Now, to your issue(s). I'd consider creating an AddIn to do as you describe for "sharing" code among many workbooks. Of course, the AddIn code would be modified to do this

how do I add a check to see if the invoice number is already present in my accounts workbook and if so replace the row instead of adding it to the next clear row

and this is a relatively straight forward process.

I've created and used AddIn's and am familiar with that process. You'll need to set a reference to the AddIn in the newly created workbooks. I'm also familiar with setting References with code...however, I've not set a reference to an AddIn with code so that would be new to me.

Were I you I'd do some research on AddIn's...if it's something you wish to pursue I'd be happy to look at it with you.

PS: If you really want to write code with code see this link
E:\VBA Code\Write Code in VBA\Progra...VBA Editor.mht

Thanks John.

Pgm,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

Hi Andy

Of course the Link was incorrect...it was from MY toolbox...try this link
http://www.cpearson.com/excel/vbe.aspx

Let me know what I can do to help.