Automaticly saving formula's to values when saving

G

Gunti

Is there any way of doing this in Excel? I want certain (not all) formula's
to become value's when saving a file.
 
B

Barb Reinhardt

This could be done as a worksheet beforesave event, but you need to be
certain you don't want those formulas again, because once they're gone,
they're gone.
 
G

Gunti

Yes this is exactly what i want. Can you maybe get an example of what i
should do? I'll explain, I have a list with values. I'm using this list to
fill in another template. What i want is the template to be independant which
means the formula's that trace the values from the list need to be converted
to values. It doens't matter that they're gone after this action.

Greets,
Gunti
 
G

Gunti

Seeing as i have no experience in creating macro's. Can you make an example
where it saves Sheet1'!A3:A5 and Sheet2'!A3:A5 from a formula to value's when
you save?

Greets & Thanks,
Gunti
 
D

Daniel.C

Menus Tools / Macro / Visual Basic Editor
double click on "thisworkbook"
if you have more than one open workbook, choose the right one.
Paste the following macro.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'changes formulas to values for sheet Sheet1, range A1:C10
[Sheet1!A1:C10].Copy
[Sheet1!A1:C10].PasteSpecial xlValues
End Sub
Daniel
 
G

Gunti

Thanks, i see there's some effort put into this. I have however a better
idea, is it possible so i can create a 'Save values' button. When i click
this, the following formula's have to be copied and saved:
'Kopgegevens!'C3:C32
'calculatie BR'!F32:F43

Thanks for everything!


Daniel.C said:
Menus Tools / Macro / Visual Basic Editor
double click on "thisworkbook"
if you have more than one open workbook, choose the right one.
Paste the following macro.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'changes formulas to values for sheet Sheet1, range A1:C10
[Sheet1!A1:C10].Copy
[Sheet1!A1:C10].PasteSpecial xlValues
End Sub
Daniel
Seeing as i have no experience in creating macro's. Can you make an example
where it saves Sheet1'!A3:A5 and Sheet2'!A3:A5 from a formula to value's when
you save?

Greets & Thanks,
Gunti
Click to expand...
Click to expand...
 
D

Daniel.C

Sorry for my poor english...
Do you mean that the formulae of range 'Kopgegevens!'C3:C32 are to be
changed into values ?
Display the control toolbox toolbar, choose command button. Draw it on
the sheet, click on "view code" and paste the following code :
Private Sub CommandButton1_Click()
[Kopgegevens!C3:C32].Copy
[Kopgegevens!C3:C32].PasteSpecial xlValues
['calculatie BR'!F32:F43].Copy
['calculatie BR'!F32:F43].PasteSpecial xlValues
End Sub
(the first and last lines should already be automatically generated).
If you have any trouble, I can post the address of a sample file.
Also, if you use XL2007, there are changes in the menus.
Daniel

Thanks, i see there's some effort put into this. I have however a better
idea, is it possible so i can create a 'Save values' button. When i click
this, the following formula's have to be copied and saved:
'Kopgegevens!'C3:C32
'calculatie BR'!F32:F43

Thanks for everything!


Daniel.C said:
Menus Tools / Macro / Visual Basic Editor
double click on "thisworkbook"
if you have more than one open workbook, choose the right one.
Paste the following macro.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'changes formulas to values for sheet Sheet1, range A1:C10
[Sheet1!A1:C10].Copy
[Sheet1!A1:C10].PasteSpecial xlValues
End Sub
Daniel
Seeing as i have no experience in creating macro's. Can you make an example
where it saves Sheet1'!A3:A5 and Sheet2'!A3:A5 from a formula to value's
when you save?

Greets & Thanks,
Gunti

:

You can use the event procedure "Workbook_BeforeSave".
Regards.
Daniel

Is there any way of doing this in Excel? I want certain (not all)
formula's to become value's when saving a file.
Click to expand...
Click to expand...
Click to expand...
 
G

Gunti

This is exactly what i wanted. Perfect! I have one question (it's a
completely different one). I want the reference to a list to be static. I'll
show an example.

Cell A1 = '=Workbook2!A1

Now, when i save & close workbook2, it automaticly changes to d:\workbook2.xls

I want this to be static, i don't want it to change to d:\ etc. Do you have
any idea on how to do this?

Thanks in advance!


Daniel.C said:
Sorry for my poor english...
Do you mean that the formulae of range 'Kopgegevens!'C3:C32 are to be
changed into values ?
Display the control toolbox toolbar, choose command button. Draw it on
the sheet, click on "view code" and paste the following code :
Private Sub CommandButton1_Click()
[Kopgegevens!C3:C32].Copy
[Kopgegevens!C3:C32].PasteSpecial xlValues
['calculatie BR'!F32:F43].Copy
['calculatie BR'!F32:F43].PasteSpecial xlValues
End Sub
(the first and last lines should already be automatically generated).
If you have any trouble, I can post the address of a sample file.
Also, if you use XL2007, there are changes in the menus.
Daniel

Thanks, i see there's some effort put into this. I have however a better
idea, is it possible so i can create a 'Save values' button. When i click
this, the following formula's have to be copied and saved:
'Kopgegevens!'C3:C32
'calculatie BR'!F32:F43

Thanks for everything!


Daniel.C said:
Menus Tools / Macro / Visual Basic Editor
double click on "thisworkbook"
if you have more than one open workbook, choose the right one.
Paste the following macro.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'changes formulas to values for sheet Sheet1, range A1:C10
[Sheet1!A1:C10].Copy
[Sheet1!A1:C10].PasteSpecial xlValues
End Sub
Daniel

Seeing as i have no experience in creating macro's. Can you make an example
where it saves Sheet1'!A3:A5 and Sheet2'!A3:A5 from a formula to value's
when you save?

Greets & Thanks,
Gunti

:

You can use the event procedure "Workbook_BeforeSave".
Regards.
Daniel

Is there any way of doing this in Excel? I want certain (not all)
formula's to become value's when saving a file.
Click to expand...
Click to expand...
Click to expand...
 
Top