Excel- History

[mmc]

i have designed a spreadsheet for a company that sells product and
used cell reference to get the data from ordersheet to invoice shee
when the new order is ordered the last invoice data disappers i want t
know how to store data in new sheet.

Thn

 

[Max]

Assuming Sheet1 is your invoicesheet "template",
with formulas and so on.

And what you want to do is
to make/store a frozen copy of the invoicesheet
when everything is completed for that invoice

Try the sub MakeStaticCopy() below
--------------
Press Alt + F11 to go to VBE

Click Insert > Module

Copy > Paste
everything within the dotted lines below
[from "begin vba" till "end vba"]
into the empty white space on the right side in VBE

---------begin vba----------
Sub MakeStaticCopy()
' Makes a static* copy of Sheet1 on another sheet
' *Only values and formats are copied [no formulas]

Application.ScreenUpdating = False
Sheets("Sheet1").Select
Cells.Select
Selection.Copy

Sheets.Add
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
Range("A1").Select

Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
---------end vba----------

Press Alt + Q to exit and return to Excel

------------
In Sheet1
------------
Draw a button on the sheet from the Forms Toolbar
(If necessary, activate the Forms toolbar via View > Toolbars > Forms)

The Assign Macro dialog will pop-up automatically
[You can also right-click on an existing button > Assign Macro]

Select "MakeStaticCopy" > OK

Rename the button to taste, e.g.: "Make Invoice Copy"

Clicking on the button will run the macro, and produce a static copy
of the invoice in a new sheet to the left of Sheet1.