macro to reset worksheet to pre-use state

[enxocoebl]

hey ex(cel)perts,
i was wondering if there was a way to return a worksheet to its pre-use state without closing it. i have prepared an invoice sheet and it has dynamic ranges so the total, discount and stuff like that move one row down as one row of products purchased is added on top. for the next invoice i ll have to exit without saving and then reopen.. i was wondering if there was a macro to reset everthing on the sheet to its original unused state.

 

[Hermanito]

Three possible ways to tackle this come to mind:
1) Create an empty invoice file as template (.xlt) and create new invoices based on the template. Doubleclicking a .xlt file in Explorer will create a new .xls instance and leave the .xlt file unchanged. So for each new invoice you can simply start with doubleclicking the template in Explorer and saving your work when done. No macro's needed, standard functionality, and imho the easiest solution.
2) Create a worksheet that contains the empty version of your invoice and copy/paste it over a filled one when needed.
3) Create a macro that specifically clears filled data. This is the most complex and errorprone option.

My advice: read up on template files, I think you will find it very useful.

 

[enxocoebl]

i ll tell the difficulties i am facing.. firstly the invoice is in a different worksheet in the same workbook.( i tried making one in a different workbook also).
the worksheet is connected to other worksheets in a complicated way (the invoice has vlookup from the stores list (data coming in) and profit loss calc, inventory update, customer data (data going out)).
will try method 2 but if u know how to make that specific macro pls do let me know. i am trying to make this as easy as i can so my dad (who has no knowledge of hw to operate a computer) can use it with ease.
there is a data validation drop down list.(only one in the sheet).
thanx for the quick reply.

 

[Hermanito]

Could you explain in some more detail your current process?

In my understanding, once you created an invoice you would typically want to store it in some format for reference, printing, etcetera. I would expect you to want a separate file per created invoice, no?
Also, once the invoice is created, is it still necessary to keep the links to other worksheets active? This creates the risk that when you need to open an older invoice for whatever reason, the linked info has changed or is no longer available, which would change an existing invoice; and that is a big no-no in accounting, at least I thought so...

 

[enxocoebl]

ok. the invoice is created and printed and is discarded. i dont want to store it anywhere. hope this makes it easier.
when i am preparing the invoice all other related worksheets get the info they need and updates itself.example(stock in hand reduces by the number that was sold). no separate file for each invoice is needed.
ideally i would like to keep it open from morning 9 to evening 9..(i ll post another question abt this later). at this point all other worksheet connections are active and running normally. but if i cant reset the invoice file i ll have to close and open everytime theres a customer(very frequent). not only that .. the connections have to be established in every new file.
i am ready to provide u all info u may need to make this macro. thank u for ur help

 

[Hermanito]

I would feel very uneasy with such a process (what do you do if the printed version gets lost/destroyed/whatever?), but hey, if it works for you, that's good enough for me

What you would need now is the following:
- prepare one worksheet as an empty invoice, including all necessary links
- prepare another worksheet (same file) as the active invoice sheet, for now this sheet would be an exact copy of the empty invoice sheet
- hide the empty invoice worksheet to avoid unwanted changes
- add the macro I will create later today or tomorrow and link that to a button placed on another worksheet in your main file.
- Each time you press the button, the used invoice sheet will be overwritten with the contents of the empty invoice sheet

What I need to create the macro is the following:
The tabnames of both your (hidden) empty and actively used invoice sheets, if you don't plan to change those names. Otherwise I would need the codenames for those sheets. If needed I will explain how you can find those codenames.

 

[enxocoebl]

i would also like to add
the invoice file has a "total", "discount" and "discounted price" in cells g8,g9 and g10 respectively.
the first product is entered in row no 6.. and i ll use tab to add new rows. the rows below automatically move down.
column c has a drop down list. resetting that column would reset everything on the page. by resetting i mean not just deleting entry.. it should also make the total, discount and discounted price cells back up. this i need cos the first bill maybe 25 rows big the second bill may not be that big(printing unnecessarily).
hope i ve made it clear.

 

[enxocoebl]

the printed sheet is handed to the customer immediately.. and where i live its a mere formality. the tab names shall be "main" for active sheet and "main1" for the hidden sheet. i ll do as u say.
u r right.. i cant find the code names by myself..

 

[Hermanito]

You say that rows automatically move down, what does this mean exactly? Is this done with macros or nifty conditional formatting?

The dropdown list in column C: where is it exactly? Is it a data-validation list that points to another range or named range containing the values?

 

[enxocoebl]

NO MACROS are running in the invoice sheet at present. new rows are added when i press the "tab" key as the invoice sheet is in the form of a table.
yes the drop down list starts from column c6. the unused invoice sheet will have only row 6 for data entry.. the added total pops up in g8... if more than one product is purchased i ll press the tab key to add a new row.. the new row has all the formatting as the previous row including the data validation drop down list. the data the invoice sheet is looking up is in the worksheet called "stock" in the same workbook.